- An identity column is an auto incrementing column
- An identity column is typically used as a primary key
- A primary key that’s an identity column is usually a surrogate key
- A surrogate key is one that is not related to the contents of the row in any way
- An identity column must be NOT NULL
- You can tell if a column is an identity column by looking at the is_identity column of sys.columns or using the COLUMNPROPERTY function (TableObjectId, ColumnName, ‘IsIdentity’)
- An identity column has three parts. Data type, Seed, and Increment
- The data type of an identity column is typically an INT but can be most numeric data types. ie tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)
- The seed defaults to 1 and is usually 1. It can however been any value that fits in the data type.
- IDENT_SEED returns the original seed value of a table
- To change the seed of a table use DBCC CHECKIDENT
- The increment is how much the identity column increases each time a new row is added
- If the increment is negative then the identity values do in fact go down
- Negative increments can be handy if your identity column has reached the max value of the data type. Reseed to 0 (assuming that is where you started) and set your increment to -1.
- IDENT_INCR returns the increment of a table
- Identity columns can have gaps in the sequence
- @@IDENTITY returns the last identity value generated within the current session but ignores scope
- Don’t use @@IDENTITY unless you know you should
- SCOPE_IDENTITY returns the last identity value generated within the current session and scope
- IDENT_CURRENT returns the last identity value generated for a specific table or view
- Use SCOPE_IDENTITY when you want the identity value for the row you just inserted
- Use IDENT_CURRENT to get the most recent identity value from a table before you do an insert
- If you don’t have permissions on the table then IDENT_CURRENT will return NULL — Thanks Anthony
- Don’t use @@IDENTITY <- Worth repeating twice
- In general you don’t list the identity column (the insert will fail even if there is a null value being inserted into the column)
- If you want to insert a specific value into the identity column you have to use the IDENTITY_INSERT setting.
- SET IDENTITY_INSERT TableName ON
- To insert a value into the identity column you must list the columns in the insert statement.
- Make sure you turn IDENTITY_INSERT back off when you are done. Only one table in a session can have IDENTITY_INSERT turned on at a time
- SET IDENTITY_INSERT TableName OFF
- When doing a SELECT INTO you can use the IDENTITY function –Thanks SQLAndy & Jeff
SELECT IDENTITY(Int, 1,1) AS Col_Name INTO TableName FROM sys.databases
- If you truncate a table it resets the seed. –Thanks Kevin
- If a table has less than 1000 rows it will be reseeded to 1000 after a system restart — Thanks Anthony
- In a memory-optimized table, the only allowed value for both seed and increment is 1 — Thanks Ypercube
Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL