Analyzing Identity Fields
A simple way to determine if a table contains an identity field is to use the SSMS Object Explorer Design Interface. Navigate to the table, then right-click it and choose Design from the pop-up window. When your design tab opens, select the first field in the table to view its list of properties in the lower pane of the tab (In this case the field is ProductID). Look to see if the Identity Specification property in the lower pane is set to either yes or no.
In this example the ProductID field begins with a value of 1 (Identity Seed) and adds 1 to the value of the ProductID for each new record added to the table (Identity Increment).
Creating Identity Fields
Since most identity fields use a seed value of 1 and an increment value of 1, the example below will use different values to better illustrate the behavior of the identity property.
Let’s begin by creating the [TestTable], with a seed value of 100 and an increment value of 50. This means the first record’s ID will be 100 and each record inserted into the table after this will have 50 added to the previous value.
CREATE TABLE TestTable
(TestID INT IDENTITY(100,50),
INSERT INTO TestTable (FName, Middle, LName)
(‘Sarah’, ‘F’, ‘Smith’),
(‘John’, ‘Q’, ‘Public’),
(‘Mary’, ‘B’, ‘Goode’)
(A note for SQL Server 2005 users, this example inserts values using row constructors, a feature introduced in SQL Server 2008. If you are running SQL Server 2005, you must change your code slightly to replicate this example. Repeat the full INSERT statement and VALUES keyword for each row being inserted.)
The results for the first three records inserted are shown below. Notice that the values for the TestID field are 100, 150, and 200.
SELECT * FROM TestTable
This shows that the Identity seed value does not have to start at one, nor does it have to increment by one. Any seed or increment value can be used.
Question: Does this include having a seed value of zero and a negative increment value?
The identity property enforces non-nullability. Using the SSMS Object Explorer, navigate to the Design Interface tab for TestTable and notice that SQL Server has created the TestID field as non-nullable, even though we didn’t specify it as being NOT NULL when creating the table using T-SQL code. This is a SQL Server constraint to actively ensure this important field is always populated with a value.
Overriding Identity Fields
Sometimes you will need to alter or insert specific values in a field with an identity property. In the following example, the ProductID field of the CurrentProducts table is the identity field. To make an explicit insert of ProductID values, it is necessary to temporarily implement the SET IDENTITY_INSERT statement, by first setting its property to ON for the CurrentProducts table and then setting it back to OFF immediately after the new values have been inserted.
Remember, after the manual inserts are completed, it is very important to change the SET IDENTITY_INSERT property back to OFF. Once the SET IDENTITY_INSERT property is set to OFF for a table, SQL Server will automatically begin picking the next values.
SQL Server will only allow one table per session to utilize the SET IDENTITY_INSERT with its property set to ON at a time. In other words, if the code above was missing the highlighted SET IDENTITY_INSERT CurrentProducts OFF statement, no other table in the JProCo database could manually alter any other identity field until this table has the property set to OFF. Fortunately, SQL Server will issue an error message identifying the table that currently has the SET IDENTITY_INSERT property set to ON, making it easier to make the necessary correction.
This lesson is a condensed excerpt from Chapter 3:
SQL Queries Joes2Pros: SQL Query Techniques for Microsoft SQL Server 2008 (SQL Exam Prep Series 70-433 Volume 2 of 5)