RSS

Using recursive query to generate incremental integer field in SQL server

24 Jun

There are number of ways to generate incremental integer id field in SQL server using TSQL for temp tables, DDL operations etc. Recently for research purpose I needed to generate incremental integer column for a table. I used a recursive query to do it for research purpose although there are many other trivial ways to do it too.(eg.ROW_NUMBER() function, IDENTITY column temp table method etc.)

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 21 /*Terminating condition*/
)
SELECT  *
FROM    cte

Recursion in SQL server is frequently achieved by using common table expressions(there are other approaches too SQL CLR, Recursife UDF calls etc). How ever maximum recursion level in SQL server is by default 100 so if you try to get more than that you will be popped up with error saying

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

In order to overcome that use following kind of query hint which also specify an aditional option(which is actually the query hint in this case)

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 12222 /*Terminating condition*/
)
SELECT  *
FROM    cte OPTION( MAXRECURSION 32767)

Actually if you specify 0 as MAXRECURSION you can virtually expand the limit to much higher level as in following query.

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 12222 /*Terminating condition*/
)
SELECT  *
FROM    cte OPTION( MAXRECURSION 0) -- specifying maxrecursion to 0

Advertisements
 
Leave a comment

Posted by on June 24, 2012 in MS SQL Server, SQL, TSQL

 

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: