#0270 – SQL Server – IDENTITY columns – Myths – The value for SEED and INCREMENT must be 1


When I interview students and ask them about IDENTITY values, they are often under the impression that they have a fixed starting value (i.e. SEED) and increment (i.e. IDENTITY values go from 1, 2, 3, 4, 5… and so on).


For those who read my Introduction to this series on IDENTITY values, I had mentioned that it is a common implementation to have the IDENTITY value increment by 1, but it is not the only implementation possible. Let’s look at this by means of some examples:


SEED & INCREMENT values – DEFAULT behavior


It might come as a surprise to many that the customary definition of IDENTITY (1,1) is used for consistency purposes only. Simply defining a column as IDENTITY would cause the database engine to assume a default value of 1 for the seed and identity.


The script below shows this by not specifying anything for the seed & increment values when defining the IDENTITY column.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
--NOTE! No values have been explicitly defined for the SEED & INCREMENT
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY,
                            IdentityValue VARCHAR(10)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),
       ('Two'),
       ('Three');
GO

--3. Check the values inserted into the table
SELECT IdentityId, IdentityValue
FROM dbo.tIdentity;

----3b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
*/

As can be seen from the results shown above, the database engine used default values of 1 for both seed & increment.


SEED & INCREMENT values – Custom values


Now, let us attempt a similar experiment but with a seed value of 10 and an increment value of 5.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY (10, 5),
                            IdentityValue VARCHAR(10)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Ten'),
       ('Fifteen'),
       ('Twenty');
GO

--3. Check the values inserted into the table
SELECT IdentityId, IdentityValue
FROM dbo.tIdentity;

----3b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityId  IdentityValue
----------- -------------
10          Ten
15          Fifteen
20          Twenty
*/

As can be seen from the results shown above, the identity columns worked perfectly fine with seed and increment values other than 1.


The only limitation that this approach might end up with is the limited range of allowed values. For example, if the seed is kept at 1000, the column can accommodate an equivalent number of lesser records before the max. value for the given data-type is reached.


Conclusion


As shown by the experiments above, it is not necessary for any application to use the default values of IDENTITY columns – they can be set to values acceptable by the business and domain rules applicable to the application.


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

6 thoughts on “#0270 – SQL Server – IDENTITY columns – Myths – The value for SEED and INCREMENT must be 1

  1. balakrishna141

    Great Article,

    Every beginner should read this article and get the basic idea about IDENTITY:

    Thanks.

    Like

    Reply
  2. chizzers

    Why would you have seed and increment available if you couldn’t set them? This isn’t a myth, it is just a lack of knowledge/understanding. One look at books online will give you the answer.

    Like

    Reply
  3. balakrishna141

    Hi Chris,

    As for this article and me. If u have’t set the seed and increment values then it will take by default (1,1). Or if you want to set your own values then you can…(5,5).

    Thanks.

    Like

    Reply
  4. Nakul Vachhrajani

    @Chris: The myth that I have seen many developers carry is that identity values always have a seed/increment value of 1 – it’s the most common implementation, but not necessarily true. One may encounter systems where the seed & identity values are other than 1 which was the point of the post.

    Like

    Reply
  5. Pingback: #0390 – SQL Server – SSIS – OLE DB Destination – Table Fast Load – Keep Identity option | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s