October 20, 2012 at 7:00 pm
I have seen a strang thing about the identity column of seed, and cannot find why, please help.
My situation is like this:
in our table we use the surragate key imported from another database, but now they change and tell us we should use our own surrogate key. So we keep last year records as before, and for new records, we want to start from 5000000 and increment is 1. So we define the table has a identity column, and see is 5000000, increment is 1.
But in my developer environment, I see after I loaded the records, it started from 5000001, but on my production one, it started from 5000000, I don't understand why they start from diffrent seed.
Here is the process we do this:
1. do the intial load of last year records, and set identity insert on, it goes from 1-4500000.
Then set identity insert off.
2.run a stored procedure that load new year records.
I see on dev server, it starts from 5000001, but on prod, it starts from 5000000.
The only diffrence is on dev, I run 1 and 2 in one batch file. On prod, I run 1 yesterday, and today I run 2.
What makes the different?
How can I identify the problem?
Thanks,
October 20, 2012 at 8:08 pm
Sorry, I don't understand what you said.
And I cannot fix the schema, this is a consultant sql developer developed database, and he just left.
He was still remoting work on this. I don't hear he said there is a problem with this.
For this thread, I just want to know why the identity seed starts differently.
October 21, 2012 at 11:07 am
CELKO (10/20/2012)
Rows are not records. one of the many they differ is that rows must have a key. A key is a subset of attributes which uniquely identifies the row.
So a row is a record with a key? Why do you object to it being called a record then, since it is one? Or do you think tht facts about the ket are nort recored in the row, so "record" is inappropriate because nothing is being recorded?
IDENTITY is a table property that gives a count of the physical insertion attempts (not even successes!) to one table in one instance of a Microsoft database product. This is one of many reasons competent SQL programmers never use it to mimic a 1950's magnetic tape record number.
Oddly enough, it's questionable whether the identity property is a property of the table or of a column, and it's probably the latter (in relational terms, it's an attribute - but you used the term table, which sadly does not mean relation). Also, it doesn't give the count you describe, since it can be set to an arbitrary value at any time. Of course you can use the fact that a table is allowed to have only one identity column to claim that IDENTITY is a table property, but that's just playing silly games, not anything to do with databases (whether SQL or RELATIONAL). I have yet to see how IDENTITY can be used to mimic a 1950s magnetic tape record number (is there even such a thing? I was responsable way back when for defining CTL's magnetic tape recording standard, which certainly had no record number; neither did the ECMA standard; neither did the ICT/Ferranti-Packard standard; neither did any othe magnetic tape standard that I ever came across (Elliot, ANSI,...); admittedly those are late 50s/early 60s standards, so something earlier may exist that had record numbers, but that woud surprise me).
In short, your real problem is an awful schema design. Can you fix it?
Is that a claim that any design using identity (or any other auto-generated surrogate key) is inherently awful for just that reason? I suppose then that you think I'm a criminal because I believe that there are cases where accepting the need for either an extra join (to a table that defines the surrogate) or an extra column (in a table that has both the natural key and the surrogate as well as properties of the natural key) is an acceptable trade-off for the space saving by using the surrogate instead of the natural key as foreign key in referring tables?
I'm all in favour of pushing standards, and good design. But that's not what you are doing here.
Tom
October 21, 2012 at 11:26 am
sqlfriends (10/20/2012)
I see on dev server, it starts from 5000001, but on prod, it starts from 5000000.The only diffrence is on dev, I run 1 and 2 in one batch file. On prod, I run 1 yesterday, and today I run 2.
Are you sure things are happening in the same order?
The rules for what value you get first are something like:
If you set the the identity to 5000000 when the file is empty and has never contained any records, then insert the new records, then insert the old records using identity insert, the new records will start at 5000000. The same applies if you empty the file using truncate table before setting the identity value.
If you insert the old records using identity insert first (or any record), then set the identity to 5000000, then insert the new records, the new records will start at 5000001. Even if you delete all the new records before you start inserting the new ones, unless you do the delete using truncate table, the new records will start at 5000001.
But the text in BoL has changed between 2008 and 2012, so I'm not very sure. Nor do I trust the text to be accurate (BoL usually is, but by no means always).
Tom
October 21, 2012 at 11:28 am
Firstly, there isn't a problem here. There is never a guarantee that identity values will not have gaps. Any time there's a insert that rolls back, the identity increments and doesn't go back. Identity values are meaningless numbers, you shouldn't care at all what the values are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2012 at 5:37 pm
Thank you Tom and Gail.
I just did some experiment, and I think one point I didn't make it clear. I said earlier one server is dev, the other is production.
Actually, it is not production, it is a copy of database I restored from production to my local. I wanted to test that before I run the second load on prod, so I restored the db from prod to my local to do the second load.
Somehow I think just because this is a restored database, it makes the difference.
when I do the second load to the table on my local, it starts from 5000000 instead of 5000001. it will start with the seed instead of seed+1 even it has records in it.
Then I did another experiment to create a simple test database with a couple of records in the table. Then restore it, then load with new data with seed, it proves above theory.
Also thanks Gail, it is good to know it does not matter the key start with. I thought it is better to make dev enviroment and prod the same starting number, maybe easier to troubleshoot for later.
Thanks,
October 22, 2012 at 7:36 am
...
Is that a claim that any design using identity (or any other auto-generated surrogate key) is inherently awful ...
Yes it is his claim, and you can find it in one of JC books (probably "SQL Programming Style") if you really want to spend your penny (or euro-cent) on it.
JC is well known for his arithmophobia for auto-incrementing keys and hateness of surrogate keys in general. Yet he failed to suggest anything useful as Natural Key even for the most common database entities (say for example "Customers").
October 22, 2012 at 2:17 pm
The seed value -- rather than seed + 1 -- is the identity value that will be assigned to the first row inserted into the table.
So, if you want the values to start at 5000001, you must seed that number, NOT 5000000.
You can remember this by remembering that the default seed value is 1, not 0; and you know that idents on new table rows start with 1 (the seed value) rather with 2 (the seed value + 1).
Btw, rows only require a key in theory, not in practice. Thankfully, practical business people build useful, real-life applications rather than solely ranting about theory.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply