generating IDs with IDENTITY() in sql server 2005
-
Hi wonderful experts, this problem has become a headache to me and so I consult you. I need to generate IDs that are unique for records. I want the IDs to include the year in which the record is added to a table. So for instance, the first record will have an actual value of say
10001
but because i want the year to be part of the ID and where for instance the record is added in the year 2011, I will want the ID to be
201110001
The subsequent records to follow will have IDs as
201110002
201110003
201110004
201110005
.........
.........
.........where the numbers have been stuck to the year to generate the IDs. Then during the next year which will be 2012, the first record in that year will start the count from 10001, and when stuck to the year, subsequent IDs will be
201210001
201210002
201210003
201210004
.........
.........
.........I want all these records to be in the same table. I have been thinking of ways around this but I don't get a way of achieving this. Please help if there are ways to do this. I'm using Microsoft SQL Server 2005. Thanks.
-
Hi wonderful experts, this problem has become a headache to me and so I consult you. I need to generate IDs that are unique for records. I want the IDs to include the year in which the record is added to a table. So for instance, the first record will have an actual value of say
10001
but because i want the year to be part of the ID and where for instance the record is added in the year 2011, I will want the ID to be
201110001
The subsequent records to follow will have IDs as
201110002
201110003
201110004
201110005
.........
.........
.........where the numbers have been stuck to the year to generate the IDs. Then during the next year which will be 2012, the first record in that year will start the count from 10001, and when stuck to the year, subsequent IDs will be
201210001
201210002
201210003
201210004
.........
.........
.........I want all these records to be in the same table. I have been thinking of ways around this but I don't get a way of achieving this. Please help if there are ways to do this. I'm using Microsoft SQL Server 2005. Thanks.
Personally I would create a table to define the sequence, start the value at 10000, increment it as necessary, and reset it when the year rolls over. What do you plan to do if your sequence exceeds 99999?
-
Hi wonderful experts, this problem has become a headache to me and so I consult you. I need to generate IDs that are unique for records. I want the IDs to include the year in which the record is added to a table. So for instance, the first record will have an actual value of say
10001
but because i want the year to be part of the ID and where for instance the record is added in the year 2011, I will want the ID to be
201110001
The subsequent records to follow will have IDs as
201110002
201110003
201110004
201110005
.........
.........
.........where the numbers have been stuck to the year to generate the IDs. Then during the next year which will be 2012, the first record in that year will start the count from 10001, and when stuck to the year, subsequent IDs will be
201210001
201210002
201210003
201210004
.........
.........
.........I want all these records to be in the same table. I have been thinking of ways around this but I don't get a way of achieving this. Please help if there are ways to do this. I'm using Microsoft SQL Server 2005. Thanks.
Dan_K wrote:
I want the IDs to include the year in which the record is added to a table
Mistake number 1, commited by all newbie database designers, yup every one of them unless somone intervenes. A primary key should not hold any intelligent information, if you want the year the record was created then add a datetime field for the job DO NOT USE THE PRIMARY KEY. Piebald will tell you to use GUIDs, I like IDENTITY myself but that is just preference, neither has any intelligence in the information, it is ONLY there to identify the record and should not be used for any other reason, this includes sorting. You do not care if there are gaps or missing records or the sequence changes, it is only to identify the record.
Never underestimate the power of human stupidity RAH
-
Dan_K wrote:
I want the IDs to include the year in which the record is added to a table
Mistake number 1, commited by all newbie database designers, yup every one of them unless somone intervenes. A primary key should not hold any intelligent information, if you want the year the record was created then add a datetime field for the job DO NOT USE THE PRIMARY KEY. Piebald will tell you to use GUIDs, I like IDENTITY myself but that is just preference, neither has any intelligence in the information, it is ONLY there to identify the record and should not be used for any other reason, this includes sorting. You do not care if there are gaps or missing records or the sequence changes, it is only to identify the record.
Never underestimate the power of human stupidity RAH
well, to be fair he never actually says that he is using this as a primary key. It may be something like a business transaction id, where for audit purposes there must never be a gap in the sequence. Think payments between bank accounts: if there is a gap in the sequence numbers, what happened to that missing payment? It will ring an alarm bell with the auditors and they will want an explanation.
-
well, to be fair he never actually says that he is using this as a primary key. It may be something like a business transaction id, where for audit purposes there must never be a gap in the sequence. Think payments between bank accounts: if there is a gap in the sequence numbers, what happened to that missing payment? It will ring an alarm bell with the auditors and they will want an explanation.
Oooh I hate it when someone brings logic to a good rant :laugh: You are quite right in that a sequence may be a business requirement, I just focussed on the IDENTITY and went into rant mode, I'll bet you a beer that is NOT what he had in mind though.
Never underestimate the power of human stupidity RAH
-
Oooh I hate it when someone brings logic to a good rant :laugh: You are quite right in that a sequence may be a business requirement, I just focussed on the IDENTITY and went into rant mode, I'll bet you a beer that is NOT what he had in mind though.
Never underestimate the power of human stupidity RAH
In fact the OP doesn't say that there should be no gaps, just that the id should be unique, so I think you are probably right. Even if he did want no gaps, IDENTITY would be the wrong solution since it doesn't guarantee no missing values in the sequence.
-
well, to be fair he never actually says that he is using this as a primary key. It may be something like a business transaction id, where for audit purposes there must never be a gap in the sequence. Think payments between bank accounts: if there is a gap in the sequence numbers, what happened to that missing payment? It will ring an alarm bell with the auditors and they will want an explanation.
That's kinda what I figured. I recall back when I was using OpenVMS, support calls got an ID number of the form YYMMDDxxx where xxx began at 001 each day and incremented throughout the day (I hope they didn't get more than 999 calls in a day). It made for a more memorable ID. And, no, it wouldn't have to be used as the primary key (but maybe it was).
-
That's kinda what I figured. I recall back when I was using OpenVMS, support calls got an ID number of the form YYMMDDxxx where xxx began at 001 each day and incremented throughout the day (I hope they didn't get more than 999 calls in a day). It made for a more memorable ID. And, no, it wouldn't have to be used as the primary key (but maybe it was).
I'll bet money it was. It's so tempting, there's this little voice inside your head whispering "Just do it, you know you want to..."
-
I'll bet money it was. It's so tempting, there's this little voice inside your head whispering "Just do it, you know you want to..."
David Skelly wrote:
Just do it, you know you
Shaddup shaddup - I recently listened to that lying bastard and did not use a separate primary key for a minute dimension table (2 records) bastard came back and bit me AGAIN, I hate that little voice.
Never underestimate the power of human stupidity RAH
-
David Skelly wrote:
Just do it, you know you
Shaddup shaddup - I recently listened to that lying bastard and did not use a separate primary key for a minute dimension table (2 records) bastard came back and bit me AGAIN, I hate that little voice.
Never underestimate the power of human stupidity RAH
Holmes I think I will want the beer for myself, though you were right. I won't take much just five bottles. I initially had in mind of making that column a primary key but I think I need to be careful with that. I need to find another approach. Thanks all, and I'm really enjoying your discussions too.
-
Holmes I think I will want the beer for myself, though you were right. I won't take much just five bottles. I initially had in mind of making that column a primary key but I think I need to be careful with that. I need to find another approach. Thanks all, and I'm really enjoying your discussions too.
Thats easily fixed, create a PK column using IDENTITY() and another column calculating the structure you want as your key field, the key field is for user consumption only and must NEVER be used to identify a record internally, only from the user. The key field should have a unique constraint. If you want to make the key field calculation transparent you can put it in a trigger X| (I loathe triggers) but I would put it into the insert procedure (I ALWAYS use stored procedures, my preference)
Never underestimate the power of human stupidity RAH
-
Hi wonderful experts, this problem has become a headache to me and so I consult you. I need to generate IDs that are unique for records. I want the IDs to include the year in which the record is added to a table. So for instance, the first record will have an actual value of say
10001
but because i want the year to be part of the ID and where for instance the record is added in the year 2011, I will want the ID to be
201110001
The subsequent records to follow will have IDs as
201110002
201110003
201110004
201110005
.........
.........
.........where the numbers have been stuck to the year to generate the IDs. Then during the next year which will be 2012, the first record in that year will start the count from 10001, and when stuck to the year, subsequent IDs will be
201210001
201210002
201210003
201210004
.........
.........
.........I want all these records to be in the same table. I have been thinking of ways around this but I don't get a way of achieving this. Please help if there are ways to do this. I'm using Microsoft SQL Server 2005. Thanks.
In the Identity versus GUID arena, if you are or will be using replication, avoid the use of Identity fields - it can causes huge headaches. Tim
-
In the Identity versus GUID arena, if you are or will be using replication, avoid the use of Identity fields - it can causes huge headaches. Tim
Hear hear! But it's not really a matter of Identity versus GUID, it's a problem with the keys being auto-generated by the engine rather than the application. You can use either an integer or a GUID and set it yourself; there's nothing magical about GUIDs here.