Identity Crisis

Question

Identity Crisis

dun

Say Hey Kid

Points: 708
More actions
October 28, 2009 at 9:31 pm

#216120

Comments posted to this topic are about the item Identity Crisis

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

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 1

Gud question. I had forgotten abt incremental value as I never use it. I always increment identity column with default value.

BudaCli Hall of Fame Points: 3404 More actions · Answer 2

Nice one, realised my mistake after running the qry on management studio.

What you don't know won't hurt you but what you know will make you plan to know better

Carlo Romagnano SSC-Insane Points: 22659 More actions · Answer 3

San-847017 (10/28/2009)
I always increment identity column with default value.

I used a seed = 1 and an increment of 5 and in another table seed = 2 and an increment of 5. So table A always has last digit = 1 or 6 and table B 2 and 7.

When I reference in table C the two table above, I know thanks to the last digit which table is linked.

daniel.surridge Old Hand Points: 392 More actions · Answer 4

In your explanation 'According to BOL, "If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used."' you omit to say that this is for TRUNCATE.

kramaswamy SSCoach Points: 18181 More actions · Answer 5

bah. automatically assumed it was seeded with increments of 1. i've never used a non-1 increment personally :/

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

Shoot! I had the identity increment right, but forgot about truncate table resetting the seed value.

Mark

Michael Poppers SSCrazy Points: 2151 More actions · Answer 7

kramaswamy (10/29/2009)
bah. automatically assumed it was seeded with increments of 1. i've never used a non-1 increment personally :/

Ditto :-).

Michael Poppers SSCrazy Points: 2151 More actions · Answer 8

Mark Harr (10/29/2009)
Shoot! I had the identity increment right, but forgot about truncate table resetting the seed value.

There was a recent question which brought that point out (maybe Mark missed it :-)). I appreciate this question's showing an aspect of IDENTITY() with which I wasn't familiar.

SanjayAttray SSChampion Points: 13157 More actions · Answer 9

Damn. I missed the increment/seed part. Generally we used (1,1) for identity column and its (1,3) in the question.

SQL DBA.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 10

Steve Jones - SSC Editor

SSC Guru

Points: 736063

October 29, 2009 at 10:26 am

#1072619

I have added the truncate item to the explanation.

ck9663 Right there with Babe Points: 763 More actions · Answer 11

SanjayAttray (10/29/2009)
Damn. I missed the increment/seed part. Generally we used (1,1) for identity column and its (1,3) in the question.

That hit me, too...:angry:

VM-723206 SSCrazy Points: 2964 More actions · Answer 12

Damn, I knew how this would work, and noticed the increment 3 too. My mind kinda' swapped the DELETE and TRUNCATE. Chose 4.

BudaCli Hall of Fame Points: 3404 More actions · Answer 13

VM-723206 (10/30/2009)
Damn, I knew how this would work, and noticed the increment 3 too. My mind kinda' swapped the DELETE and TRUNCATE. Chose 4.

Welcome to the party. Not as easy as 1 2 3 ...

What you don't know won't hurt you but what you know will make you plan to know better

rizwan.inam SSC Journeyman Points: 86 More actions · Answer 14

What is the Difference between Truncate Table and Delete Table