Blog Post

15 years of experience with Identity columns

,

  • 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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating