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

Click here to get free chapters (PDF) in the mailbox

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 (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – To Err is Human; to Forgive, Divine – Errata of SQL Server Interview Book
Next Post
SQLAuthority News – I am Speaking at SQL Saturday 116 – Bangalore, India on January 7, 2012 – First SQL Saturday in India

Related Posts

No results found.

2 Comments. Leave new

  • Yes identity can be ever decreasing, set Identity Increment to -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?


Leave a Reply Cancel reply

Exit mobile version