Introduction
This article will introduce the users, especially the SQL Server developers to a limitation of SQL Server that it does not allow a user to apply IDENTITY to more than one Columns in a Table. After the introduction, we will look at several ways that we can address the limitation. In the last solution, i.e., Solution #4, we will look at how to address the limitation in a way in which it almost seems as if we have created not one but two columns with IDENTITY in a single table.
Background
SQL Server does not allow to create identity on two columns in a single table. What if we have a scenario in which we have to require identity on two columns in single table? Well, there are multiple ways to do that. Starting with the Issue or the Error, let's look at them one by one...
The Issue/Error: Creating Table with IDENTITY on Two Columns
If a user tries to create a Table in SQL Server with IDENTITY on two Columns, SQL Server does not allow it and returns an error. Suppose we want to create a Table with the name tblTempTable which will have three Columns. The Columns are:
ID with datatype INT. This is the Primary Key and has IDENTITY with SEED value 1 and INCREMENT of 1.ExtraID with datatype INT. The is the second Column on which we want to have IDENTITY with SEED value 2 and INCREMENT of 2.TempName with datatype VARCHAR(100). This Column will store the concerned data.
The code to create table is:
CREATE TABLE tblTempTable (
ID INT PRIMARY KEY IDENTITY(1, 1),
ExtraID INT IDENTITY(2, 2),
TempName VARCHAR(100)
);
GO
Now, when we execute the code to create table, SQL Server returns the error:
Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'tblTempTable'.
Only one identity column per table is allowed.
Code can also be found in .SQL file by the name "01 - The Error.sql".
Now how are we going to address this issue. Obviously, we cannot create IDENTITY on two columns in a single Table. So there must be a workaround.
Solution#1. Select the Identity Value before Operation
There are multiple ways in which a user can select the next Identity Value. Simply either a user can SELECT the value before the INSERT statement or user may define a User-Defined function for this purpose.
First, we create the Table but the legitimate code will require us to have only IDENTITY on just one Column. So, the code for creating the Table is as follows:
CREATE TABLE tblTempTable (
ID INT PRIMARY KEY IDENTITY(1, 1),
ExtraID INT NOT NULL,
TempName VARCHAR(100)
);
GO
We also create three variables to create a new Identity for Column ExtraID. The code is as follows:
DECLARE @ExtraID AS INT;
DECLARE @SeedValue AS INT = 0;
DECLARE @IncrementValue AS INT = 2;
Now before executing the INSERT statement, we SELECT the new value for ExtraID Column in a variable and pass the variable in INSERT statement. The code is as follows:
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Aqeel');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Rizwan');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Shariq');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Raheel');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Abu Bakar');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Waseem');
Code can also be found in .SQL file by the name "02 - Select before Operation.sql".
How good is a software engineer if he/she cannot write a reusable piece of code. So in the next step, we create a User-Defined Function to address the issue in a better way.
Solution#2. Select the Identity Value using User-Defined Function
User-Defined Function provides a way for software engineers to create a reusable piece of code. In this case, the user will create a Scalar Function that will return the new value for ExtraID Column. The code to create the function is as follows:
CREATE FUNCTION dbo.NewTempTableExtraID
(
@SeedValue INT,
@IncrementValue INT
)
RETURNS INT
AS
BEGIN
DECLARE @NewExtraID AS INT;
SELECT @NewExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue)) + @IncrementValue FROM tblTempTable;
RETURN @NewExtraID;
END;
GO
Note that this function accepts two parameters, i.e., @SeedValue and @IncrementValve. Both parameters are self-explanatory. This function will select and return the next value for ExtraID Column. The User-Defined function implements the same logic from the previous section.
Now instead of selecting the new value for ExtraID column before the INSERT statement, the user will call the Function in the INSERT statement and this will insert new value in Table.
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Aqeel');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Rizwan');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Shariq');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Raheel');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Abu Bakar');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Waseem');
This is how we optimize and reuse the code and avoid the factor of human error.
Code can also be found in .SQL file by the name "03 - User Defined Function.sql".
Solution#3. Create a Sequence Object
SQL Server 2012 introduced a new user-defined schema-bound object by the name SEQUENCE. User can create this object and can configure multiple attributes including START value, INCREMENT value, MINVALUE, and MAXVALUE (for more details, see point 1 in references).
Now what we do is we create a SEQUENCE object. The code to create a SEQUENCE object is as follows:
CREATE SEQUENCE dbo.seqExtraID AS INT START WITH 2 INCREMENT BY 2;
Now that we have a SEQUENCE object by the name seqExtraID, we retrieve the new value for ExtraID Column and pass it to the INSERT statement and insert it into the Table.
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Aqeel');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Rizwan');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Shariq');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Raheel');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Abu Bakar');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Waseem');
You may note that this is the same as how we addressed the issue in Solution #1. The only difference is that instead of selecting the value using the SELECT statement, we are retrieving the value from the SEQUENCE object.
Code can also be found in .SQL file by the name "04 - Using the Sequence Object.sql".
Here comes the final solution that will give us the same look and feel that we have two columns with IDENTITY in a single Table.
Solution #4: DEFAULT SEQUENCE in Table
We are familiar with the DEFAULT object. DEFAULT object specifies a value to be inserted into the column to which the object is bound when no value is explicitly supplied during an insert (for more details, see point 2 in references).
So first, we create a SEQUENCE. The code is as follows:
CREATE SEQUENCE dbo.seqExtraID AS INT START WITH 2 INCREMENT BY 2;
Now when we have the SEQUENCE by the name dbo.seqExtraID, we create the table with default value for Column ExtraID using the SEQUENCE object dbo.seqExtraID we just created! The code to create the table is as follows:
CREATE TABLE tblTempTable (
ID INT PRIMARY KEY IDENTITY(1, 1),
ExtraID INT NOT NULL DEFAULT _
(NEXT VALUE FOR dbo.seqExtraID),
TempName VARCHAR(100)
);
Note how we mentioned the default value for ExtraID Column using the DETAULT keyword. Once the table is created, we can insert the data in the Table using the simple INSERT statement. The INSERT statements are as follows:
INSERT INTO tblTempTable (TempName) VALUES ('Aqeel');
INSERT INTO tblTempTable (TempName) VALUES ('Rizwan');
INSERT INTO tblTempTable (TempName) VALUES ('Shariq');
INSERT INTO tblTempTable (TempName) VALUES ('Raheel');
INSERT INTO tblTempTable (TempName) VALUES ('Abu Bakar');
INSERT INTO tblTempTable (TempName) VALUES ('Waseem');
SELECT * FROM tblTempTable;
When the above statements are executed, SQL Server will provide the results for the SELECT Statement. The results look like this:
You may note that neither is there a need to mention the ExtraID Column now is it needed to provide its value. The INSERT statement executes just like we have not just one Column but two Columns with IDENTITY in a single Table.
Code can also be found in .SQL file by the name "05 - Using the Sequence Object in Table.sql".
Points of Interest
Illusion that a Table has not one but two columns with IDENTITY.
References
- https://msdn.microsoft.com/en-us/library/ff878058.aspx
- https://msdn.microsoft.com/en-us/library/ms173565.aspx
