IDENTITY function

As we all know we can create a table with an identity column, add rows and automatically get every row numbered sequentially. This requires that we first create a table with a column that has an identity property set. If you want to have a new table with a column that gives every row an increasing (or decreasing) number you don’t have to make the extra move to first create the table with an identity column and then insert the data. Instead you can use the IDENTITY function. I recently found myself in a situation where i had to migrate some data and needed to assign every row a unique number. One alternative would have been to create a cursor, loop through all the records and have a variable that would increase it’s value by 1 for every record and then insert the row. Not a very appealing solution. Instead i decided to select the data with an added identity column into a new table (which was the desired result by the way).

Here’s an example: SELECT IDENTITY(INT, 1,1) AS ID, COL1, COL2….INTO NewTable FROM SomeTable This statement will create a new table, called NewTable, with a column called ID with datatype INT and the rest of the columns that are listed in the select statement. Note that the new column, ID, is created as an integer and does not have the IDENTITY property set so it does not behave like a IDENTITY after table creation. One restriction to this is that you can only use the IDENTITY function in a SELECT INTO statement. Why It would have been nice if you could use it with a plain regular insert…