A better way of generating numbers than identity
-
What's the best way of generating the number in a primary key column besides identity column ?
-
you can create a UDF for generating a incremented number for a table and attach it to the column in the CREATE TABLE statement
-
Lots of debate on this one. Some people think that the data itself should generate the primary key.
I like to use identities, but they are problematic when moving data from dev to production.
Using a single table for multiple id fields can be a contention point in a busy system. If you have multiple tables that are being slammed with inserts, I'd use separate tables that have a single row in them to store the "next" PK value. This is nice in that you can always "adjust" it if things get out of synch or you need to manually add a specific value.
One thing you should do is wrap the select from the pk table, the insert and the update to teh Pk table in a transaciton so there are no collisions.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Do you care what the value of the data is? If not, one option (slightly
more expensive) is the uniqueidentifier data type and the newid() function (as the default). What version of SQL Server are you running?My personal preference for primarykeys is NOT the Identity value. That's a great value/data type for locating a speciic row, joining tables, etc., but the the primarykey should be more than that.
-
I kinda like identity for pkeys. Simple, easy to manage. Uniqueidentifiers are growing on me though. Im using GUID's in a object model Im working on now, I can generate ALL the keys in the object (on the client), then persist them without the old insert get the identity value..insert the child record dance. No round trips.
We have an app that uses a table for key generation. In base36! Not worth the pain as far as Im concerned. Keys are still meaningless.
Andy
-
quote:
What's the best way of generating the number in a primary key column besides identity column ?
You will be rolling your own no matter what so here's an idea: (I actually cobbled this up a while back so it works)
Create a "seed" table with 2 columns (SeedID int, Seedvalue TimeStamp)
Update Seedtable
set SeedID=1
where SeedID=1
This updates the TimeStamp/Seedvalue column. You can either select it back from the table as BIGINT or use @@DBTS to get the current data base value.
This has the side benefit of creating database-wide unique keys across all tables. use with caution.
-
Don, that's a pretty cool solution. Another one, similar in the general idea, is to insert a row into a table and then do an immediate rollback, like this:
create table foo (a int identity(1,1), b tinyint)
begin tran
insert into foo (b) values (1)
rollback tran
select @@identity
This could be wrapped in a proc or udf (without the create table statement of course).
On another note, SQL:200n (the upcoming standard) will probably include both a sequence and an identity (not quite like SQL Server's, but almost).
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
No matter what solution you use keep in mind how many trasactions you need to process in a given time. I have seen people do this and tie themselves up with locking issues due to high number of transactions. The method I use on simple identitys is like so.
---create seed table
create table seedx (idx int not null)
go
--insert initial value
insert seedx(idx) values (1)
go
--create a variable to set from current seed value and increment seed in one step.
declare @id int
update seedx set @id = idx, idx = idx + 1
print @id
I believe this is how the internal process acutally does it as well, and seems to have least problem with accidental duplication or need to set your own locks to prevent processes access the same value at the sametime.
-
If you have three systems, for example, a devel server, test server and Production server, then make the ids start at 1 and increment by 3 on the devel server and start at 2 and increment by 3 on the test server and start at 3 and increment by 3 on your prod server. So your ids would look like this:
Devel Server Test Server Prod Server
1 2 3
4 5 6
7 8 9
10 11 12
All you have to do is increment by the number of servers that you have this way you would never have a conflict in ids between your servers.
Edward M. Sokolove
Edward M. Sokolove -
I'll share some code we have been using to maintain IDs in our Sybase databases for years. Sybase didn't have the concept of IDENTITY columns back when this was created.
The table looks like this:
CREATE TABLE t_next_id (
table_nm varchar(30) NOT NULL,
next_id int NOT NULL,
table_database_nm varchar(30) NULL,
table_primary_key_nm varchar(155) NULL)
LOCK ALLPAGES
go
CREATE UNIQUE CLUSTERED INDEX XPKt_next_id ON t_next_id(table_nm)
go
We use this Next ID table to support a lot of diffferent tables in different databases. The last two columns are strictly informational and serve no other purpose. Here is the stored procedure called to get the next available ID:
CREATE PROCEDURE p_next_id_2
@table_nm varchar(40), -- table that the Next ID request is for
@cnt int = 1, -- number of IDs wanted, the first of multiple IDs is set on @next_id
@next_id int = 0 output, -- Next ID
@suppress_next_id_display int = 0 -- Suppress output of ID with a value != 0
AS
BEGIN
DECLARE @rows_affected INT
SELECT @rows_affected = 0
-- the row is always storing the next ID to use
SELECT @next_id = next_id
FROM t_next_id
WHERE table_nm = @table_nm
IF (@@rowcount = 1)
begin
WHILE (@rows_affected != 1)
begin
-- update the row for the given table to the next id + @cnt where
-- the row matches the table and next_id captured at the start of the proc
UPDATE t_next_id
SET next_id = (next_id + @cnt)
WHERE table_nm = @table_nm
AND next_id = @next_id
SELECT @rows_affected = @@rowcount
-- if the row wasn't updated then someone else got the ID so increment
-- the @next_id var and try again
IF (@rows_affected != 1)
begin
SELECT @next_id = next_id
FROM t_next_id
WHERE table_nm = @table_nm
end
end
-- if the output is not being suppressed create the result set
IF ( @suppress_next_id_display = 0 )
begin
SELECT @next_id
end
end
ELSE
begin
RAISERROR 99999 "Table '%1!' not found in t_next_id table.", @table_nm
end
END
go
Hope this is helpful,
Jeff
-
I've done something very similar to what Jeff just posted. Only main difference was we wrapped it in a transaction.
BTW: Be VERY careful of using identity columns if you ever want to use replication. Once you replicate the database you'll have to start using managed identities and they are a real pain to manage! If replication is in the future I would highly suggest something similar to what Jeff posted or use a uniqueidentifier(one will get put on the table anyway so why not actually use it!)
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
quote:
If you have three systems, for example, a devel server, test server and Production server, then make the ids start at 1 and increment by 3 on the devel server and start at 2 and increment by 3 on the test server and start at 3 and increment by 3 on your prod server....
sokolove has an interesting idea, but we add servers so quickly here that we would be adjusting the data frequently... 🙁
Jon
-
That is a problem if you add servers frequently this is not the way to go, but if you have set servers (Devel, Test, Production) then this would work. Although if you increment by 1000 it could still work. Unless you would grow past 1000 servers. So every time you add a server start with the next incremental number and increment by 1000. server1 1 1001 2001 3001 4001, server2 2 1002 2002 3002 4002, server3 3 1003 2003 3003 4003 server 4 4 1004 2004 3004 4004, etc..and if you think you would not grow past a hundred servers then reduce your increment to 100 instead of 1000. Never tried it with that many servers and you could increase your autonumber to bigint to allow for large numbers especially if you start at 1000 and increment by 1000 your numbers will get large.
Edward M. Sokolove
Edited by - sokolove on 12/16/2002 09:36:41 AM
Edward M. Sokolove -
If you add servers and databases a lot I would suggest a compound primary key. Use whatever method you decide to generate a unique ID but also add one or two other columns with default values. The two columns I was thinking of would be servername and database name. These can be populated with @@servername and db_name().
e.g.,
CREATE TABLE t_test (
row_id numeric identity,
server_nm varchar(30) default @@servername not null,
db_nm varchar(30) default db_name() not null,
col_1 varchar(30) not null,
col_2 varchar(60) null,
constraint xpk_t_test primary key (row_id, server_nm, db_nm))
go
-
I've been a big fan of Identity columns for years. At least since v 6.5. [ I think I used it in version 6.0, but that was years ago. ]
But, GUID's are starting to grow on me. Between replication issues with the IDENTITY columns (PAIN IN THE REAR), and the round trips (noted by Andy above), if you need a new ID column, GUID's are really starting to win me over. And, with memory and disk space at ever dropping prices, the 'savings' from a small integer ID is almost non-existent compared with the design savings from a totally unique ID (GUID).
I have also used procedure based unique ID's, but only when the ID used an algorithm to verify that it was really an ID - similar to what Visa/MasterCard use to keep the average crook from just making up those credit card numbers.
If you need such an algorithm, then by all means, you need a procedure, or UDF mentioned above (love to see that bit of CREATE TABLE). If not, let the system create a basic, non-intelligent ID: GUID or IDENTITY.
Andrew
What's the business problem you're trying to solve?
more expensive) is the uniqueidentifier data type and the newid() function (as the default). What version of SQL Server are you running?Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply