Sample SQL Column Identity Generator using OUTPUT CLAUSE in T-SQL and SPARSE NULL Data Type
Here is the t-sql script to create table for our sample sql demonstration tables.
The first table is the SQL column identity generator table in MS SQL Server 2008.
What is interesting about this t-sql identity generator table is that it has one identity column with bigint values.
I used big int since many tables will be requesting unique identity values from this table so I keep the column type as big as it can.
And comes SPARSE NULL part of the table. I had to create a column which is totally useless. So I created varchar(1) column which will keep nothing.
This means all values in this column will be NULL. So I defined that the column is a SPARSE NULL column which points that most of the column values will be NULL for that column.
The two other tables have columns named SampleColumn that their values are unique and comes from the sql identity generator table named IdentityGeneratorTable column Id.
You can use more than two tables of course. I only used two tables for this example.
CREATE TABLE IdentityGeneratorTable
(
Id bigint identity(1,1) not null,
[Dummy Column] varchar(1) sparse null
)
CREATE TABLE SampleTableT1 (
IdentityColumn1 int identity(1,1) not null,
Code varchar(10),
SampleColumn bigint not null
)
CREATE TABLE SampleTableT2 (
IdentityColumn2 int identity(1,1) not null,
Code varchar(10),
SampleColumn bigint not null
)
To insert rows into sample sql database tables, I use T-SQL OUTPUT CLAUSE enhancement which was introduced with MS SQL Server 2005 for the T-SQL developers.
INSERT INTO IdentityGeneratorTable([Dummy Column])
OUTPUT 'Code n', INSERTED.Id
INTO SampleTableT1(Code, SampleColumn)
VALUES (NULL)
INSERT INTO IdentityGeneratorTable([Dummy Column])
OUTPUT 'Code m', INSERTED.Id
INTO SampleTableT2(Code, SampleColumn)
VALUES (NULL)
If you execute the above sql insert statements a few times with different Code column values, you can view the inserted column identity values by running sql Select statements on those tables.
SELECT * FROM SampleTableT1
SELECT * FROM SampleTableT2
--SELECT * FROM IdentityGeneratorTable
For sql tutorial and examples on Sparse Null please refer to document Microsoft SQL Server 2008 Sparse Columns and SPARSE NULL
And for T-SQL Output Clause samples and tutorials, please refer to tsql articles T-SQL OUTPUT Clause in order to INSERT Data in Two Tables in One Command and MS SQL Server 2005 T-SQL OUTPUT Clause Sample Code with Insert, Update, Delete Statements.