SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.
  • 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
  • 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


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...