#SQLSERVERLearning : Identity Column Insight.

All my blogs with #SQLSERVERLearning are intended to cover all stuff identified as the difference between Oracle and SQL Server.
Hopefully, it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access #SQLSERVER Learning..

Identity column helps to have a column with auto increment counter based on SEED/StartWith and Increment value on each request.
Identity column was part of SQL Server since 2008 release and a genuine need was for oracle developer to have same.
Finally in oracle 12c release, Identity column and sequence as default were part of new Feature.

Ideally comparison of Identity column behaviour in oracle and SQL server can be debatable, as both have different implementation logic.
But for understanding point of views, lets walk through Identity column implementations and its behaviour on different actions.

Lets start with field work by understanding how identity works in SQL Server.

CREATE TABLE TAB_IDENTITY
(
EMP_ID INT IDENTITY(1,1), --Identity column that will start with 1 and always increment by 1.
EMP_NAME VARCHAR(1000),
DATE_JOIN DATE DEFAULT GETDATE()
);
check table propery having identity column.
SP_HELP 'TAB_IDENTITY';

sql_identity

Emp_id is identity column having Not Null constraint implicitly added by SQL Server.
By Specifying not NULL and Identity, we would need to always adhere to column value from identity itself, we can’t insert custom values.
Below insert with insert EMP_ID data as per identity properties.
Insert Into Tab_Identity(emp_name,Date_Join) Values('ENAME1', GETDATE()-4);

–> Custom Data in Identity column.
when we try to explicitly provide a value for emp_id, it would give an exception.

Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(2,'ENAME2', GETDATE()-3);
Error message ::
Cannot insert explicit value for identity column in table 'tab_identity' when IDENTITY_INSERT is set to OFF.

Now lets try to Set IDENTITY_INSERT ON;
set IDENTITY_INSERT identitydb.dbo.Tab_Identity ON;

It will allows insertion of custom identity values to be inserted.

Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(2,'ENAME2', GETDATE()-3);
Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(10,'ENAME2', GETDATE()-3);

By adding custom values greater then Current Seed, i.e by updating Emp_id from 2 to 10 we also updated identity SEED values.
SQL Server provide DBCC CHECKIDENT to check identity column property within a table.

DBCC CHECKIDENT ('TAB_IDENTITY');
Checking identity information: current identity value '10', current column value '10'.

–>Truncate impact on identity
let see what happen when we try to truncate table having identity column.

TRUNCATE TABLE TAB_IDENTITY;
DBCC CHECKIDENT ('TAB_IDENTITY');
Checking identity information: current identity value 'NULL', current column value 'NULL'.

Truncate cause reset of identity column and it will altogether start with initial seed.

–>Drop impact on identity
Reason to include was its behavious in oracle when objects still exists in Recycle bin.

DROP TABLE TAB_IDENTITY;
DBCC CHECKIDENT ('TAB_IDENTITY');
output::
Cannot find a table or object with the name "TAB_IDENTITY". Check the system catalog.

As table is drop, corresponding identity columns is also drop.

–>Number of identity column in a table
Any table in oracle or be in SQL server can have only one identity column.

If in case we want any other column to get increment based on some SEED and Increment factor, we can use Sequence as Default.
CREATE SEQUENCE SEQ_IDENTITY
START WITH 1
INCREMENT BY 1;

CREATE TABLE TAB_IDENTITY_SEQ
(
EMP_ID INT IDENTITY(1,1),
ROW_NO INT DEFAULT NEXT VALUE FOR SEQ_IDENTITY,
);

CONSTRAINT_TYPE CONSTRAINT_KEYS
--------------------------- -------------------------------
DEFAULT ON COLUMN ROW_NO (NEXT VALUE FOR [SEQ_IDENTITY])

With Oracle 12c, we can also have a custom sequence next val as default for column values.

–>Insert for a table having only Identity columns
Suppose a Scenario in which we have a table with only 1 column which is identity column.
With traditional insert syntax, we can’t insert values within such tables.

CREATE TABLE T_IDENTITY
(
EMP_ID INT IDENTITY(1,1)
);

INSERT T_IDENTITY DEFAULT VALUES; -- using default values we can cause identity value to get populated.
(1 ROW(S) AFFECTED)

DBCC CHECKIDENT ('T_IDENTITY');
CHECKING IDENTITY INFORMATION: CURRENT IDENTITY VALUE '1', CURRENT COLUMN VALUE '1'.

–>Change SEED value for a Identity Column
DBCC CHECKIDENT provide an option to change Seed value for identity column.
With identity column we can only change current SEED value but Increment factor once set can’t be change.

Lets take above table for current example.
DBCC CHECKIDENT ('T_IDENTITY', RESEED , -10000);
INSERT T_IDENTITY DEFAULT VALUES;
SELECT * FROM T_IDENTITY

EMP_ID
-----------
1
-9999

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , , . Bookmark the permalink.

One Response to #SQLSERVERLearning : Identity Column Insight.

  1. Pingback: #SQLSERVERLearning : Identity Column Insight. — Oracle Insight and Performance concepts – SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s