June 17, 2002 at 6:42 am
OK anyone know what happens if you breach the upper linit of an identity column. It's a big number I know, but lo and behold I'm working for a client who is likley to go throuhg the ceiling in a couple of years
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 17, 2002 at 6:48 am
You will get an error like this.
Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
Except where you see tinyint you will see the datatype for the identity column. You should look at archiving the data maybe by year or something that will make the set unique and join by a view. Or find a better key from the data than an indentity value.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 17, 2002 at 7:24 am
With SQL2K you can use a Bigint - that might buy you a little time. Uniqueidentiers should give you unlimited usage.
Andy
June 17, 2002 at 7:40 am
Thanks for that. Now the intersting thing is that you could specify the identity as a bigint according to BOL, however I've got a very strong suspicion that when you try yo access it SQLServer won't use the bigint. I'd be interested to know.
You'll be pleased to know that my client is right now reviewing correct archiving policies.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 17, 2002 at 7:46 am
GUID's is novel, however I bet there's a performance issue. Surely it must be slower to go and get a GUID than increment a number, could be an issue in a high transaction app like this one.
No matter how long you're at this , there's always something that comes out of left field.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 17, 2002 at 8:53 am
hi crosspath, is your primary key a identity column. if so, i think that would be a a bad choice for a pk. if not , then you can always you can always reset the identity column with DBCC CHECKIDENT .
HTH
June 17, 2002 at 9:14 am
quote:
Now the intersting thing is that you could specify the identity as a bigint according to BOL, however I've got a very strong suspicion that when you try yo access it SQLServer won't use the bigint. I'd be interested to know.
I am confused by this statement. bigint as identity field should be fine and have heard no issues with it not being full used or dying at a point?
quote:
Surely it must be slower to go and get a GUID than increment a number, could be an issue in a high transaction app like this one.
Not supposed to be an issue and is used often by Microsoft.
Hard to tell you what will bennifit you best without knowing a lot about your table and transaction needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 17, 2002 at 10:51 am
Certainly there has to be a bit more overhead with a GUID, but not much, and you can leverage the fact that you can create them client side which is a great way to reduce network traffic/round trips to the server.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply