• 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