SQL SERVER – Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.

Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.

SQL Quiz

What is an IDENTITY Value? What are the different ways to get IDENTITY values and properties of IDENTITY Values? If you are going to answer that identity value is ever increasing, is it possible then to make identity value ever decreasing?

Link to participate in SQL Quiz

Notes of Vinod Kumar

The usage of IDENTITY columns is a very common practice. Usages are sometimes simple like a link. But my extension here would be after the definition of IDENTITY, how would I know what the increments and the value of the same is?

Notes of Pinal Dave

Identity is very popular and I often see it used in various places. Quite often I see it being used as a Primary Key and frequently considered as a good candidate for the clustered index. The real challenge is to understand various properties which are related to Identity. There are a lot more to identity than just increasing number. The interval can be changed as well. There are various global and system functions associated with it. But one thing’s for sure; identity does not have an identity crisis!

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#35
@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column
Discussion – Effect of Missing Identity on System – Real World Scenario
Negative Identity Seed Value and Negative Increment Interval
Enable Identity Insert – Import Expert Wizard


There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.

Link to participate in SQL Quiz

Note: The SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.

Reference: Pinal Dave (http://blog.sqlauthority.com)

4 thoughts on “SQL SERVER – Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31

  1. Pinal,

    create table sample
    id int identity(1,1),
    name varchar(100)

    select IDENT_CURRENT(‘sample’)
    Here Identity as 1

    begin tran
    insert into sample(name)
    select ‘udhaya’ union
    select ‘ganesh’

    select IDENT_CURRENT(‘sample’)
    But here Identity as 2 . I have rollback, why not affected in identity column?


  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051 | SQL Server Journey with SQL Authority

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