As I was writing the series on myths around IDENTITY columns, I started to wonder whether copying a table into another using the SELECT…INTO statement copies over the IDENTITY property also.

The simplest way to answer this question was to run a simple test, which is shown below:

1. Create a table with an IDENTITY column defined, and insert some test data
   
2. Use SELECT…INTO to select data from the source table and pump it into another table
   
   
   
   • For clarity, I will be using different column names between the source and the destination columns
   
   
3. Use the catalog views – sys.columns and sys.identity_columns to confirm whether the new table was created with the IDENTITY column in place or not

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(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO

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

--2. Use SELECT..INTO to bulk insert data to a new table
SELECT SourceTable.IdentityId AS DestinationId,
       SourceTable.IdentityValue AS DestinationValue
INTO dbo.DestinationTable
FROM dbo.tIdentity AS SourceTable

--3. Check the properties of the new column - is it an IDENTITY column?
SELECT sc.name AS ColumnName,
       OBJECT_NAME(sc.object_id) AS TableName,
       sic.seed_value AS IdentitySeed,
       sic.increment_value AS IdentityIncrement,
       sic.is_nullable AS IsNullable,
       sic.last_value AS LastIdentityValueUsed
FROM sys.columns AS sc
INNER JOIN sys.identity_columns AS sic ON sc.object_id = sic.object_id
                                      AND sc.column_id = sic.column_id
WHERE ( sc.object_id = OBJECT_ID(N'dbo.DestinationTable',N'U') OR
        sc.object_id = OBJECT_ID(N'dbo.tIdentity',N'U')
      )
  AND sc.is_identity = 1;

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

IF OBJECT_ID('dbo.DestinationTable','U') IS NOT NULL
    DROP TABLE dbo.DestinationTable;
GO

/**********************************************
               RESULTS
**********************************************/
/*
ColumnName     TableName         IdentitySeed IdentityIncrement IsNullable LastIdentityValueUsed
-------------- ----------------- ------------ ----------------- ---------- ---------------------
IdentityId     tIdentity              1              1               0             3
DestinationId  DestinationTable       1              1               0             3
*/

As can be seen from the experiment, the IDENTITY property propagates from one table to another via the SELECT…INTO clause.

Until we meet next time,

Be courteous. Drive responsibly.

In the first part of this series on IDENTITY columns, I had mentioned that IDENTITY columns must be NOT NULL and must not have a DEFAULT constraint defined on them. Often, this is interpreted in haste as IDENTITY columns cannot have any constraints defined on them.

This is a myth. Today’s post will attempt to clear out this confusion.

Defining a DEFAULT constraint on an IDENTITY column – Msg 1754

The script below is quite simple – it attempts to create a table with an IDENTITY column and then goes on to use the ALTER TABLE statement to add a default constraint to 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, this time WITHOUT a Clustered Key
CREATE TABLE dbo.tIdentity (RecordId INT IDENTITY (1,1),
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add a default constraint
ALTER TABLE dbo.tIdentity
    ADD CONSTRAINT df_DefaultRecordId DEFAULT(0) FOR RecordId;
GO

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

Executing this script returns an error as shown below:

Msg 1754, Level 16, State 0, Line 3
Defaults cannot be created on columns with an IDENTITY attribute. Table ‘tIdentity’, column ‘RecordId’.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

This example settles one part of the puzzle – IDENTITY columns cannot have DEFAULT constraints on them.

Defining other constraints (CHECK/PRIMARY KEY) on an IDENTITY column

The script below creates a table with an IDENTITY column and defines a primary key constraint on the IDENTITY column. Later, the script uses the ALTER TABLE statement to add a CHECK constraint on the IDENTITY value.

Because the DDLs execute successfully, we will insert some test data to test that the constraints are indeed in effect.

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, with a primary key constraint on the IDENTITY column
CREATE TABLE dbo.tIdentity
    (
      RecordId INT IDENTITY(1, 1),
      IdentityValue VARCHAR(20),
      CONSTRAINT pk_tIdentityRecordId PRIMARY KEY CLUSTERED ( RecordId )
    ) ;
GO

--2. Alter the table to add a CHECK constraint
--   Now, we have two constraints on the IDENTITY column:
--   a. A Primary Key constraint
--   b. A Check constraint
ALTER TABLE dbo.tIdentity
ADD CONSTRAINT chk_MaxRecordId CHECK ( RecordId <= 5 ) ;
GO

--2. Add some test data
--   Since we are adding 5 records, the statement will succeeed
INSERT  INTO dbo.tIdentity ( IdentityValue )
VALUES  ( 'One' ),
        ( 'Two' ),
        ( 'Three' ),
        ( 'Four' ),
        ( 'Five' ) ;
GO

--2b.One may also want to check the value of IDENT_CURRENT
PRINT 'CurrentIdentityValue (Before Constraint violation): '
    + CAST(IDENT_CURRENT('dbo.tIdentity') AS VARCHAR(10)) ;
GO

--3. Add some more test data
--   This will fail, because the identity value will now exceed the values 
--   allowed by the CHECK constraint
INSERT  INTO dbo.tIdentity ( IdentityValue )
VALUES  ( 'Six' ) ;
GO

--3b.One may also want to check the value of IDENT_CURRENT
PRINT 'CurrentIdentityValue (After Constraint violation): '
    + CAST(IDENT_CURRENT('dbo.tIdentity') AS VARCHAR(10)) ;
GO

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

The resulting messages are shown below:

CurrentIdentityValue (Before Constraint violation): 5
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint “chk_MaxRecordId”. The conflict occurred in database “tempdb”, table “dbo.tIdentity”, column ‘RecordId’.
The statement has been terminated.
CurrentIdentityValue (After Constraint violation): 6

As can be seen from the resulting messages shown above, the CHECK constraint on the IDENTITY column was in effect resulting in the constraint violation error.

Observation!

Observe that the current value of the IDENTITY column (obtained via IDENT_CURRENT) has incremented even though the value was not inserted. This is because of the failed insert, which can leave holes in the IDENTITY sequence.

Conclusion

As we can see from the examples above, IDENTITY columns cannot have a DEFAULT constraint defined on them – other constraints can still be defined on an IDENTITY column.

Until we meet next time,

Be courteous. Drive responsibly.

As seen in the first part of this post, we can add IDENTITY columns to existing tables without any issue. IDENTITY columns cannot be NULL. Hence, when we add a new column as an IDENTITY column, every record existing in the table has to be assigned an IDENTITY value. So, the question arises that which record should be the first one to be assigned with the Identity seed, which record will be the second record and so on. In short:

How existing records are assigned the IDENTITY value?

We saw in the first part of the post that because a table was ordered via a clustered primary key, the IDENTITY specification was directly applied in that order. However, when the table is a heap (i.e. does not have a clustered index defined on it and is therefore unordered), the IDENTITY values are assigned based on the order in which records were inserted into the table.

The script and it’s results below prove this point.

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, this time WITHOUT a Clustered Key
CREATE TABLE dbo.tIdentity (RecordId INT,
                            IdentityValue VARCHAR(20),
                            CONSTRAINT pk_tIdentityRecordId PRIMARY KEY NONCLUSTERED (RecordId)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (5, 'Five'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four');
GO

--2b. As a test, inserting some interleaving data separately
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (2, 'Two'),
       (6, 'Six');
GO

--3. Check the values inserted into the table
--   Observe the order of the records that come up in the results
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ADD IdentityId INT IDENTITY(1,1);
GO    

--4. Check the values inserted into the table
--   Observe the order of the records that come up in the results
--   v/s the value of the IdentityId
SELECT RecordId, IdentityId, IdentityValue
FROM dbo.tIdentity;
GO

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

/**********************************************
               RESULTS
**********************************************/
/*
RecordId    IdentityValue
----------- --------------
3           Three
5           Five
1           One
0           Zero
4           Four
2           Two
6           Six

RecordId    IdentityId  IdentityValue
----------- ----------- --------------
3           4           Three
5           6           Five
1           2           One
0           1           Zero
4           5           Four
2           3           Two
6           7           Six
*/

Now, let us define a CLUSTERED INDEX on this table such that the records are re-arranged. Once the clustered index is defined, we will insert a couple of records and then look at the assignment of the IDENTITY column. We can see that IDENTITY values continue to be assigned in the order in which records are inserted, however, the records are now ordered based on the clustered index specification.

USE tempdb;
GO

-- 5. Define a clustered index for the Primary Key
ALTER TABLE dbo.tIdentity
    DROP CONSTRAINT pk_tIdentityRecordId;
GO

ALTER TABLE dbo.tIdentity
    ADD CONSTRAINT pk_tIdentityRecordId PRIMARY KEY CLUSTERED (RecordId);
GO

--6. As a test, inserting some interleaving data separately
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (-1, 'Minus One'),
       (7, 'Seven'),
       (8, 'Eight');
GO

--7. Check the values inserted into the table
--   Observe the order of the records that come up in the results
--   v/s the value of the IdentityId
SELECT RecordId, IdentityId, IdentityValue
FROM dbo.tIdentity;
GO

----7b.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
**********************************************/
/*
RecordId    IdentityId  IdentityValue
----------- ----------- --------------
-1          8           Minus One
0           1           Zero
1           2           One
2           3           Two
3           4           Three
4           5           Four
5           6           Five
6           7           Six
7           9           Seven
8           10          Eight
*/

Conclusion (Part 02)

The behavior of IDENTITY columns and of SQL Server seen in this post is quite interesting – IDENTITY values are always generated in the order in which the records are inserted. When adding IDENTITY column to an existing table, this is true only if the table is not ordered, i.e. does not have a clustered index. Once the IDENTITY values have been assigned to all existing records in the table, all future values will be generated in the order of insertion.

Until we meet next time,

Be courteous. Drive responsibly.

Generally, IDENTITY columns are used when developing new tables so that when records are inserted into a table, they are assigned the appropriate values.

Hence if ever the need arises to insert an IDENTITY column into an existing question, the big question is – can it be done?

Is it really possible to add an IDENTITY column to an existing table?

The answer is quite simple – Yes!

The only restrictions that ALTER TABLE enforces upon us are that a new column must be added and that it should be of a data-type compatible with IDENTITY Columns. To demonstrate these points, I will perform two experiments shown below.

Modifying an existing column to an IDENTITY column

Other than recreating the entire table, an existing column cannot be modified to an IDENTITY column. Copy-Paste the query below into SSMS and simply parse it by using Ctrl+ F5 key combination or the blue check mark on the SSMS toolbar. The following error will be encountered:

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 (RecordId INT,
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (2, 'Two'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four'),
       (5, 'Five'),
       (6, 'Six');
GO

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

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ALTER COLUMN RecordId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED;
GO    

--4. Check the values inserted into the table
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

----4b.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
**********************************************/
/*
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'IDENTITY'.
*/

Tip: If your design ever needs to be modified to convert an existing column into an IDENTITY column, this can be achieved using the following high-level steps:

1. Create an identical table under a different name, but with an IDENTITY column (e.g. tmp_tIdentity)
   
2. Set IDENTITY_INSERT to ON for this new table
   
3. Pump data from the old table to the new table (i.e. from tIdentity to tmp_tIdentity)
   
4. Set IDENTITY_INSERT to OFF for this new table
   
5. Drop the old table
   
6. Rename the newly created table (i.e. rename from tmp_tIdentity to tIdentity)

Adding a new Identity column to an existing table

While existing columns cannot be converted to IDENTITY columns, new IDENTITY column can always be added to an existing table (provided there are no other IDENTITY columns on it).

The below script quite simply creates a table and then add an IDENTITY column to it. As can be seen from the results below, although the records were inserted randomly, the identity values are assigned based on the way in which the records are ordered. This is an important consideration, as we will see in part 02 of this post. In this case, we have a clustered key on the RecordId column and therefore, the values in the IDENTITY column follow the same order as the RecordId.

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 (RecordId INT PRIMARY KEY CLUSTERED,
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (5, 'Five'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four'),
       (2, 'Two'),
       (6, 'Six');
GO

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

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ADD IdentityId INT IDENTITY(1,1);
GO    

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

----4b.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
**********************************************/
/*
--Before adding the IDENTITY value
RecordId    IdentityValue
----------- --------------
0           Zero
1           One
2           Two
3           Three
4           Four
5           Five
6           Six

--After adding the IDENTITY value
RecordId    IdentityId  IdentityValue
----------- ----------- --------------
0           1           Zero
1           2           One
2           3           Two
3           4           Three
4           5           Four
5           6           Five
6           7           Six
*/

Conclusion (Part 01)

The above experiments show that Microsoft SQL Server allows us to define IDENTITY columns on existing tables, under certain restrictions. This can be a very useful feature in case an original, legacy design needs to be changed for a universal adoption of IDENTITY columns in your product.

Until we meet next time,

Be courteous. Drive responsibly.

When one thinks of an incremental number, a series of positive numbers comes to mind. Since IDENTITY columns are an auto-incrementing series of numbers, it is human to associate IDENTITY columns with positive numbers. However when we talk about numbers and the number line, we can go to infinity from both sides of 0 (-Infinity….-5, -4, -3, -2, -1, 0, +1, +2, +3, +4, +5,….+Infinity).

Hence, the question that comes up is whether IDENTITY values (including the seed & increment values) can be negative. The only way to test it out is to use an example:

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 (-1, -1),
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Minus One'),
       ('Minus Two'),
       ('Minus Three'),
       ('Minus Four'),
       ('Minus Five');
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 IdentityValue;
--GO

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

/**********************************************
               RESULTS
**********************************************/
/*
IdentityId  IdentityValue
----------- --------------
-1          Minus One
-2          Minus Two
-3          Minus Three
-4          Minus Four
-5          Minus Five
*/

As can be seen from the result of the script above, it is possible for the SEED, INCREMENT and the IDENTITY column value itself to be negative. Keeping in mind the rules of the number line (from elementary school), please note that as one moves away from 0 (in either direction), the number keeps increasing in value and is therefore incrementing.

Conclusion

As shown by the quick experiment above, it is quite possible to have negative values for IDENTITY columns. As of writing this post, I do not have a practical implementation of this in mind. However, if you have ever encountered a situation where auto-incrementing negative values might be useful, do drop a line in the blog comments below.

Until we meet next time,

Be courteous. Drive responsibly.