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
Denali – SEQUENCE is not IDENTITY
Prize
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)
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’
rollback
select IDENT_CURRENT(‘sample’)
But here Identity as 2 . I have rollback, why not affected in identity column?