In this demonstration we will be showing how to use IDENTITY columns in a table. First of all, an IDENTITY field is very similar to an auto number field, where the number will automatically be added as each record is inserted. By default, the value will start at 1 and then count by 1.
This can be changed by changing the SEED and INCREMENT value when you create the IDENTITY field. For Example, if we want to start at the number 1000 and count by 2, we create the field like this IDENTITY(1000,2). However, as you can see in our code we will be going with the default settings.
In lines 1-4 of the sample code you will notice we are creating a table with two columns name TestID and TestName with the TestID field being created as an IDENTITY field. After we create the table, we will need to populate the table by using the INSERT statement.
On line 6 we are using the shortest way of adding a column by NOT including the INTO statement or the name of the columns. You may also notice that we are only supplying the value for the second column as the first column will be auto-populated with the next number available for the IDENTITY field.
On line 7 and 8 we are also inserting a row but in this case we did include the INTO statement as well as the column name for the TestName field. There is no functional difference between the two INSERT statements. (With one exception we will see later.)
Finally on line 9-12 is a demonstration of how to add more than one column at a time. (This is only available in SQL Server 2008 or later.) Now when we run the INSERT statements from line 6-12, we should see the following messages. (There are three seperate Insert Statements.)
Now to see the actual rows that are in this table we would need to run the statement on Line 17 which would return the following results. Notice that the first column had numbers automatically added. This is because it was an IDENTITY field.
What if later we want to insert an explicit value into the IDENTITY field. First we need to turn the IDENTITY_INSERT statement ON. Which can be done by running the code on line 14. Then on lines 15 and 16 we add the explicit value of -99. It is important to note, that you do need to supply the column names when explicit inserting values.
Now finally, if we want to go back to the auto-numbering of the IDENTITY field again we would need to turn SET IDENTITY_INSERT OFF as seen on line 17. Then when we would insert another row, the numbering starts counting again from where the last auto-number finished.
When working with larger tables it may be necessary to find out what the current IDENTITY value is in the table. You can do this by using the DBCC CHECKIDENT statement. Notice that the message shows the current identity value of 6.
You can also use the DBCC CHECKIDENT statement to change the IDENTITY value. Notice after we reseed to the value of 20 the next row being inserted will be 21.