Home > Query, SQL > BISQL # 36 :Queries to Understand more on Identity aspect in SQL {Identity Part–III}

BISQL # 36 :Queries to Understand more on Identity aspect in SQL {Identity Part–III}


Hi Friends,

Continuing from my last post on basics of identity :BISQL # 34 :What is Identity IN SQL {Identity Part–I} « SQL Server Mentalist

Also post on identity  function BISQL # 35 : Deep dive into Identity Functions in SQL {Identity Part–II} « SQL Server Mentalist


Today we are going to discuss about Some query related to Identity aspect in SQL

Although the Identity column is very useful in maintain the data in the table, sometimes we need to set this constraint off like when we importing the data into the database.

Following are some constraint related configuration query for Identity

1.Set Identity Off

To set the Identity column property off, the following command is used.

Set Identity_insert <Table name> Off

2.Set Identity On

To set the Identity column property On, the following command is used.

Set Identity_insert <Table name> On

3.Reseed Identity

We can also reset the values of the identity column to some different value with the help of the following command

dbcc checkident (Tablename, reseed, 10)

From above we can conclude some points for Identity

Identity column in the Sql server is a very useful property and it can be used to retrieve the data very quickly specially in the table where no primary key is defined.

  1. Only one Identity column is possible for a table.
  2. In case of truncate command on a table the identity column value is reset to 0.

For more interesting find on related topic on daily basis you can visit my link resource website :Link Resource # 11 August 1 – August 2 « Dactylonomy of Web Resource

Hope this helps !!

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 37 :Checking, Seeding and Reseeding of Identity {Identity Part–IV}

· BISQL # 38 : Introduction To Function – {SQL Server Function Part – I }

· BISQL # 39 : Details of Row set functions – {SQL Server Function Part – II}

· BISQL # 40 : SQL Server Code Name “Denali” CTP3 Product Guide

· BISQL # 41 : Difference Between Char And Varchar


Hope you will like this post on Queries to understand on Identity aspect Part III.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog : Link Resource Website

           For More information related to BI World visit my Mentalist Blog

                       Link Resource Blog >> Daily Interesting links

                       SQL Server Mentalist >> SQL Learning Blog

                       Business Intelligence Mentalist >> BI World

                                    Connect With me on

           | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                   Copyright © 2011 – 2012 Vishal Pawar

Categories: Query, SQL

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 )

Connecting to %s

%d bloggers like this: