QOTD Truncating Identity

Question

Post reply

QOTD Truncating Identity

Wayne West

SSC-Insane

Points: 22586
More actions
March 9, 2016 at 8:39 pm

#313921

Comments posted to this topic are about the item QOTD Truncating Identity
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 9, 2016 at 11:43 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

I would have gotten it wrong as I thought it would be 1 after reading BOL. Misunderstood what it said.:-D

timwell SSCertifiable Points: 5315 More actions · Answer 2

The explanation does not explain why the DBCC CHECKIDENT(table) returns null after TRUNCATE TABLE

I would have expected it to show the seed value (whatever it was. It was not stated.)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

The fact that the seed value wasn't stated led me to the answer. If "1" had been one of the options, it would have been more difficult, since that's the default seed value. But if no rows have been inserted since the reseed then there is no column value and no identity value and hence NULL is returned. I agree that the explanation might have stated that explicitly.

John

daniel.plocinik Hall of Fame Points: 3288 More actions · Answer 4

Interesting question. Perplexing answer. Even with the information from the supporting link I cannot come up with that answer. I guess 6 cups of coffee is not enough to get my brain thinking this morning.

Wayne West SSC-Insane Points: 22586 More actions · Answer 5

I should have included the original Identity(1,1) statement, mea culpa. I think John Mitchell's analysis is correct, I found it quite odd in my limited testing and thought it would make for an interesting discussion in QOTD. I've also been sick for a couple of weeks with tremendous back pain added on to that, so I'll blame it on the drugs. 🙂

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

bgodick SSC-Addicted Points: 459 More actions · Answer 6

bgodick

SSC-Addicted

Points: 459

March 10, 2016 at 10:35 am

#1864306

Nice question. Thanks!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

daniel.plocinik Hall of Fame Points: 3288 More actions · Answer 8

That's what I needed - drugs... :w00t:

Actually, if you think like Microsoft there were really only two choices and I chose the wrong one. One day I'll become a true SQL teckie and then this answer will be obvious. :hehe:

George Vobr SSChampion Points: 10010 More actions · Answer 9

Thank you for this interesting question. After TRUNCATE TABLE is the table empty. DBCC CHECKIDENT on an empty table returns null.

USE TestDB;

GO

CREATE TABLE Audit_Logins (ID INT IDENTITY, Name varchar(10));

SELECT * FROM Audit_Logins;

DBCC CHECKIDENT ([Audit_Logins]);

GO

/*

Results

ID Name

----------- ----------

(0 row(s) affected)

Checking identity information: current identity value 'NULL', current column value 'NULL'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

INSERT INTO Audit_Logins VALUES ('Login1');

INSERT INTO Audit_Logins VALUES ('Login2');

INSERT INTO Audit_Logins VALUES ('Login3');

INSERT INTO Audit_Logins VALUES (NULL);

SELECT * FROM Audit_Logins;

DBCC CHECKIDENT ([Audit_Logins]);

GO

/*

Results

(1 row(s) affected)

ID Name

----------- ----------

1 Login1

2 Login2

3 Login3

4 NULL

(4 row(s) affected)

Checking identity information: current identity value '4', current column value '4'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

TRUNCATE TABLE Audit_Logins;

DBCC CHECKIDENT ([Audit_Logins]);

GO

/*

Results

Checking identity information: current identity value 'NULL', current column value 'NULL'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

DROP TABLE Audit_Logins;

GO

HappyGeek SSCoach Points: 18729 More actions · Answer 10

HappyGeek

SSCoach

Points: 18729

March 11, 2016 at 12:12 am

#1864490

Very good question, thanks

...

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Nice question to make me stop and think. Thanks, Wayne.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

webrunner SSC-Dedicated Points: 31885 More actions · Answer 12

Wayne West (3/9/2016)
Comments posted to this topic are about the item <A HREF="/questions/IDENTITY/138589/">QOTD Truncating Identity</A>

Thanks, great question. I incorrectly chose 0 but learned something.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

akljfhnlaflkj

SSC Guru

Points: 76202

March 14, 2016 at 7:10 am

#1864898

Missed this one.