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,
SQLTwins by Nakul Vachhrajani 