SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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])
INTO SampleTableT1(Code, SampleColumn)

INSERT INTO IdentityGeneratorTable([Dummy Column])
INTO SampleTableT2(Code, SampleColumn)

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

sample sql column identity generator

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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.