How do I create a custom identity?
-
Hello,
I have been tasked with creating a new table with a unique Identity key. Instead of the the standard integer automatically incrementing by one on each insert, my client wants the primary key to have five digits AA000 through ZZ999.
So, the first insert into the table would have a unique primary key of AA000, the next would be AA001... then when it gets to AA999 it would then flip to AB000.
I have no idea where to even start. If anyone can point me in the right direction, with some possible code samples or something I would be very appreciative.
Thanks,
Brian
-
Depending on how the new records are inserted
use a control record to store last key generated
CREATE TABLE ControlRec(key1 tinyint,key2 tinyint,key3 smallint)
INSERT INTO ControlRec(key1,key2,key3) VALUES (65,65,-1)
DECLARE @key1 tinyint,@key2 tinyint,@key3 smallint,@key char(5)
BEGIN TRANSACTION
UPDATE ControlRec SET @key1=key1,@key2=key2,@key3=key3=key3+1
IF @key3>999
BEGIN
SET @key3=0
SET @key2=@key2+1
IF @key2>90
BEGIN
SET @key2=65
SET @key1=@key1+1
END
UPDATE ControlRec SET key1=@key1,key2=@key2,key3=@key3
END
COMMIT TRANSACTION
SET @key=CHAR(@key1)+CHAR(@key2)+REPLACE(STR(@key3,3,0),' ','0')
you can use the above in the procedure that is creating new record or put it in a trigger (but will need a loop due to multiple inserts!!)
or if you want to use the existing table to generate the next number then you could do the following (example is a function)
CREATE FUNCTION dbo.ufn_nextkey ()
RETURNS char(5)
AS
BEGIN
DECLARE @key char(5),@key1 tinyint,@key2 tinyint,@key3 smallint
SELECT @key=MAX() FROM WITH (UPDLOCK)
IF @key IS NULL
SET @key = 'AA000'
ELSE
BEGIN
SET @key1 = ASCII(SUBSTRING(@key,1,1))
SET @key2 = ASCII(SUBSTRING(@key,2,1))
SET @key3 = CAST(SUBSTRING(@key,3,3) as smallint) + 1
IF @key3>999
BEGIN
SET @key3=0
SET @key2=@key2+1
IF @key2>90
BEGIN
SET @key2=65
SET @key1=@key1+1
END
END
SET @key = CHAR(@key1) + CHAR(@key2) + REPLACE(STR(@key3,3,0),' ','0')
END
RETURN @key
END
Far away is close at hand in the images of elsewhere.
Anon. -
If your client is stupid it does not mean you must follow his stupidity.
Create standard IDENTITY column and write a function to convert integers into required code. Create computed column next to identity and assign values from the function to it.
In all SELECTs return this code, just don't show your identity column.
Don't forget to create index on this computed column.
But in all FK references use INT column. It's much more effective for indexing and overall performance. 4 bytes of INT handle 2 billion values, 4 char numbers only 10 thousand. Feel the difference.
_____________
Code for TallyGenerator -
Brian,
David is on the right track with a function but, here's some simpler code that uses classic "base" conversion techniques... it may be just a bit faster because it's 100% set-based. It could be used in a function but read on...
Here's the code... play with it... test it... change the value of @ID remembering that the max should be 675999 ((26^2)-1 +999) which I DIDN'T trap for...
DECLARE @ID INT
SET @ID = 675999
SELECT
CHAR((@ID/26000)%26+65) --1st Letter
+CHAR((@ID/1000)%26+65) --2nd Letter
+REPLACE(STR(@ID%1000,3),' ','0') --The 3 digit numeric part
Do as Serquiy suggested... make a regular IDENTITY column starting at 0 with an increment of 1. Make your custom ID column a CHAR(5).
You won't be able to use the above formula as a default (if you figure out a way, DO post it please) because you can't use an IDENTITY column in a default and none of the identity related functions will produce correct results for multi-line inserts.
To make this automatic, you will need to put a trigger on the table so that whenever the table realizes fresh inserts or updates, the formula is applied to all rows contained in the INSERTED trigger table. I hate triggers because they can really drag down performance if written poorly but I can't think of another way to automate this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Very nice Jeff
Wish I'd thought of that
Could use your code for a COMPUTED COLUMN but would not be able to index it
Best bet is to use trigger (be careful of multiple inserts though
)but will have to be 'INSTEAD OF' unless the key is NONCLUSTERED
If insert only done by sp then your code can be used in the proc and if the ID is not wanted in the table then use a control table to store the ID
Far away is close at hand in the images of elsewhere.
Anon. -
A deterministic function could be used for this. Just send the identity as the input parameter and you're golden. No impact on inserts or updates. However the argument could be made that the cost of constantly calculating that number would be greater on the long run than to just update it once it's inserted.
Also what would happen if an identity is "missing". They will most likely argue that there's missing data when there's none
. -
why not use Jeff's function as a calculated column?[edit--on rereading, that is exactly what remi and david said..i just provided the example sorry]
DROP TABLE X
CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
XCALCULATED AS CHAR((XID/26000)%26+65) --1st Letter
+CHAR((XID/1000)%26+65) --2nd Letter
+REPLACE(STR(XID%1000,3),' ','0'), --The 3 digit numeric part
SOMEOTHERCOL VARCHAR(30)
)
INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SELECT * FROM X
XID XCALCULATED SOMEOTHERCOL 1 AA001 WHATEVER 675999 ZZ999 MORESTUFF Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the kudo, David. It's a real pleasure to hear coming from you.
Lowell's idea is a good one... You very well could use the calculation as a computed column.... be careful though... you cannot use views to update a table with calculated or derived columns in SS-2000 (not that you should but one never knows).
Since the ID is based on the actual numeric ID provided, it doesn't matter if ID's are missing or not...
Remi is correct, could be a deterministic function but if you put it in a trigger (does NOT have to be INSTEAD OF), you can kind of forget about it. No maintenance, no worries. Even if a user updates the custom ID directly, it'll snap right back because of the trigger.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the kudo, David. It's a real pleasure to hear coming from you. We sycophants have got to stick together
You very well could use the calculation as a computed column Yes as I mentioned but also stated that you cannot index the column and therefore would involve a table scan or an index scan of the ID (if it is the primary)
Using a trigger seems to be the best way and having both the ID and alternate key (with index) on the record, the only reason I mentioned 'INSTEAD OF' trigger was that if the alternate key had to be the primary key then the key would have to be generated prior to insert.
Far away is close at hand in the images of elsewhere.
Anon. -
Make the function deterministic and set index on it.
DROP TABLE X
CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
XCALCULATED AS CHAR((XID/26000)%26+65) --1st Letter
+CHAR((XID/1000)%26+65) --2nd Letter
+convert(char(3), replicate('0', 3-len(convert(varchar(3), XID%1000))) + convert(varchar(3), XID%1000)), --The 3 digit numeric part
SOMEOTHERCOL VARCHAR(30)
)
CREATE NONCLUSTERED INDEX IX_X ON dbo.X
(XCALCULATED) WITH FILLFACTOR = 70
But, as I mentioned, don't use this computed column as a reference for FK. It's bad for performance.
_____________
Code for TallyGenerator -
>the only reason I mentioned 'INSTEAD OF' trigger ...
Got it... thanks for the idea, David.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just curious, Serqiy... why do you prefer the following...
+convert(char(3), replicate('0', 3-len(convert(varchar(3), XID%1000))) + convert(varchar(3), XID%1000)), --The 3 digit numeric part
... over ...
+REPLACE(STR(XID%1000,3),' ','0'), --The 3 digit numeric part
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Because of "Create Index ..." following.
_____________
Code for TallyGenerator -
thanks everyone for the great responses!
-brian
-
Serqiy,
This too, is deterministic and a bit easier on the eyes...
RIGHT('000'+CAST(XID%1000 AS VARCHAR),3)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply