January 23, 2013 at 6:58 am
Good question, ended up getting me 🙂
January 23, 2013 at 7:13 am
Enjoyed the question thanks. It has made me realise that the reseed parameter has to be treated with some care.
Dave Morris :alien:
"Measure twice, saw once"
January 23, 2013 at 7:34 am
Nice question. I was concerned a bit by the specification of SQL Server 2012... had to double-check to make sure they didn't change this behavior in that version.
January 23, 2013 at 8:45 am
tripped myself by ticking the wrong box, which i saw after pressing "submit
Same here. Rushed a bit. Good question.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
January 23, 2013 at 10:54 am
Dave Morris (1/23/2013)
Enjoyed the question thanks. It has made me realise that the reseed parameter has to be treated with some care.
And more care then I gave the answer -1. OOOOps!
Not all gray hairs are Dinosaurs!
January 23, 2013 at 11:14 am
thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 23, 2013 at 11:43 am
Thanks for this question. I remembered that there was inconsistent behavior surrounding the RESEED but forgot the specifics until I read up.
January 23, 2013 at 1:56 pm
Nice question, thanks!
January 23, 2013 at 2:41 pm
Bob Cullen-434885 (1/23/2013)
OK. I got it wrong because I tried to apply what I thought was sensible logic to the choice of the second value. Can anyone explain why SQL uses such bizarrely inconsistent behaviour?
I'm just wondering what's the inconsistent behaviour you're referring to? The identity column gets reseeded with a new value of 100 and the next row inserted then gets assigned (new value + 1) , so 100+1=101. What's bizarre or inconsistent about that?
Cheers
Roddy
January 23, 2013 at 3:12 pm
Roddy.CAMERON (1/23/2013)
I'm just wondering what's the inconsistent behaviour you're referring to?
Delete the table, then run the same code again -but this time without the first insert statement.
My guess is that this is the inconsistent behaviour Bob meant.
January 23, 2013 at 4:20 pm
Hi Hugo
Yes, I see what you mean. I guess it doesn't seem inconsistent to me as I fully understand the different actions and so I was puzzled as to what was inconsistent or bizarre. I guess the issue comes down to knowing that reseeding an identity depends on whether the first row has been inserted into the table or not. I accept if someone doesn't realise what the full functionality of the command is doing and simply tries and determine it based on their own logical thinking (and I have done this many times myself), certain behaviour can appear inconsistent.
If no rows have been inserted then running DBCC CHECKIDENT is indeed actually changing the seed value to be used by the first row inserted, so it is "reseeding" the table. However if there are rows already in the table you are not reseeding it at all, you are actually just updating the value of the "current identity".
I think it maybe really relates more to a terminology thing and the fact that the command actually performs 2 different actions, rather than being inconsistent in my opinion. If you take the term "RESEED" in the CHECKIDENT command to be absolutely explicit in it's meaning then yes, it is inconsistent as it performs differently depending on circumstances. However if you accept the one command is actually being used to perform 2 effectively different tasks depending on the circumstances, and using the single "reseed" term in the dbcc command simply allows SQLServer to perform different tasks depending on whether there are rows in the table or not, rather than making you use a separate "UPDATE_CURRENT_IDENTITY" clause perhaps if there are already rows inserted, then it's not inconsistent.
Like a lot of functionality though, the user actually needs to be aware of the exact workings, otherwise I accept it can appear bizarre/inconsistent, even though in fact it is not.
Cheers
Roddy
January 24, 2013 at 3:05 am
main thing was : DBCC CHECKIDENT ('dbo.MyTable', RESEED, 100);
January 24, 2013 at 4:11 am
Roddy.CAMERON (1/23/2013)
Hi HugoI think it maybe really relates more to a terminology thing and the fact that the command actually performs 2 different actions, rather than being inconsistent in my opinion. If you take the term "RESEED" in the CHECKIDENT command to be absolutely explicit in it's meaning then yes, it is inconsistent as it performs differently depending on circumstances. However if you accept the one command is actually being used to perform 2 effectively different tasks depending on the circumstances, and using the single "reseed" term in the dbcc command simply allows SQLServer to perform different tasks depending on whether there are rows in the table or not, rather than making you use a separate "UPDATE_CURRENT_IDENTITY" clause perhaps if there are already rows inserted, then it's not inconsistent.
Like a lot of functionality though, the user actually needs to be aware of the exact workings, otherwise I accept it can appear bizarre/inconsistent, even though in fact it is not.
Cheers
Roddy
Hi Roddy,
Very good explaination. You are calling inconsistency as dong 2 different tasks in one command based on circumstances. Can you list me what are all such functions available in sql server which behaves differently based on circumstances, So that I can be aware of such functionalities before using it.
--
Dineshbabu
Desire to learn new things..
January 24, 2013 at 7:21 am
good question.
I find the "inconsistency" discussion slightly odd. All operators/functions/operations/procedures have results/effects depending on the values of their parameters - but because dbcc checkident's effects depend the value of its first parameter, it's inconsistent? Presumably the operator + is also inconsistent, because the meaning of @z+7 depends on both the type and the value of @z; and "SET" is terribly inconsistent too: SET @x = @y depends rather heavily on the value of @y; different values of @y can produce different results for @x, and - even worse - some values for @y will result in an error message with no effect at all on the value of @x.
dbcc checkident(<some table>,'RESEED', <someinteger>) has an effect that depends on the value of <sometable> (that is the table value, not the string value). So what? Should we expect that the table value has no effect? Should it be allowed to return an error if the first parameter is a table that doesn't have an identity column? Isn't that inconsistent - it sometimes returns an error and sometimes doesn't? If that isn't inconsistent, what is the basis for the claim that deciding whether to use teh increment on teh basis of where a current identity value is available is inconsistent - after all, the existence of a current identity value is no less a property of the table than the existence of an identity column - neither is static, they they can both be changed easily.
Tom
January 24, 2013 at 7:35 pm
Hi Dineshbabu
It's not really possible to list all function or commands that can perform different actions depending on the circumstances. As Tom very rightly states in his post, many commands and functions perform differently depending on how they are being used and the particular circumstances.
I suggest your best approach is to read the documentation for any command or function that you are planning on using very carefully, to understand it fully before using it.
Cheers
Roddy
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply