August 11, 2004 at 5:03 am
hi forum members,
this is my first post and also i m new to sql. i have been reading all about identity column but feel that it is not worth using. my situation is that i have lots of tables which must have UNIQUE row ids and moreover the data is being created by multiple users.
how can i make sure that no duplicate row ids get generated. i have already faced a problem :
User 1
reads the max(rowid) say 1524
incriment it gets 1525 and
insert a new row
user 2
reads the max(rowid) (1524) - before the user 1 commited
incriment 1525
insert a new row and gets an error of duplicate rowid
Please Help...
regards,
gagan
August 11, 2004 at 9:20 am
We use a bigint type, with the identity set to generate unique IDs for each row, and we have no problems with that - I'm not sure off your reasoning against it. There's far less overhead than having to send out another query to work out the next row id.
If you're actually talking about the uniqueidentifier type, then yes I can understand that you may find that overkill, unless you're considering the likes of replication or merging tables.
August 12, 2004 at 12:24 am
Gagan:
(I posted this before, but something happened to it when I pressed the Post button).
I don't know why you are working so hard with this. Using your example:
"User 1
reads the max(rowid) say 1524
incriment it gets 1525 and
insert a new row
user 2
reads the max(rowid) (1524) - before the user 1 commited
incriment 1525
insert a new row and gets an error of duplicate rowid"
Why are you incrementing the ID? Why not specify the Identity property (= "Yes"), and let SQL Server do the work.
I.E. Assume that the current max ID is 1524 (Note that SQL Server will know what this number is automatically).
User 1: Insert a new row (SQL Server generates and saves ID = 1525)
User 2: Insert a new row (and SQL Server generates and saves ID = 1526)
[Note that User 2 could beat User 1 and save the new ID = 1525, and User 1 would end up with an ID = 1526].
Your Identity column could be an Int type (or a BigInt type as suggested by Martin). I have used this on applications with 200+ tables (and 500+ views), with no trouble.
If you need to use a UniqueIdentifier on your tables, you can do that as well, as long as you remember to set the "Is GuidRow" property to "Yes" and the Default Value = "(newid())". (I use this, as well as an Identity column, in a User table. The Identity column is the Primary Key, and the GUID is used as a seed for password encryption via MD5).
Allan
August 12, 2004 at 6:05 am
What are you trying to gain from manually incrementing the unique index? Are you constrained by a business rules?
We also use identity column for hundreds of tables in our databases and haven't had any problems with duplicate records. SQL server handles this well and never assigns the same number twice. Even if the transaction conducts a rollback; sql server will not re-assign the number, unless you intervene and reseed the table.
August 12, 2004 at 9:12 am
The identity value gets assigned BY THE DB AT INSERT TIME; not by you prior to the insert. When you insert data into a table, do not list the identity column when you do an insert, and a value will be generated for you. It works perfectly in a high-use database.
CREATE TABLE dbo.foo
(id int identity,
name varchar(50)
)
INSERT INTO dbo.foo
(name)
August 12, 2004 at 10:42 pm
i have been reading about identity columns and have almost decided not to use them!
Problems: 1) @@identity gets lost in transporting tables 2) missing identities in case of rollbacks 3) not sure but have read about getting multiple identities 4) spurious threat from DBCC CHECKIDENT ; i m using these rowids in other tables to make links and in no circumstances change the rowids once generated.
i dont want to use guid also mainly due to size.. cannot afford to have them.. it is huge database getting data on minutes basis.
thanks!!
August 15, 2004 at 10:44 pm
thanks a lot Adi,
your tips were very helpful..
sorry for not to be clear for point 3), i restate: i m not aware of reasons but i have read that there are chances that an identity column of a table may get duplicate values. i need this column data to be unique.
thanks and regards,
gagan
August 16, 2004 at 6:44 am
3) Yes, it is possible to achieve duplicate values in an identity column.
But not if the identity column is a primary key or has an unique index defined on it.
/rockmoose
You must unlearn what You have learnt
August 17, 2004 at 11:38 pm
...just to expand on rockmoose's comment. IDENTITY columns in and of themselves are not meant to ensure uniquness, but by not reseeding the value and using a unique constraint (or primary key) you can ensure uniqueness.
If you want my opinion, the question to be asked is not whether to use the IDENTITY function to generate a surrogate key, but rather is the use of surrogate keys appropriate at all and in this case specifically? And if you decide to use them, make sure you understand the potential problems they can cause. In any case, the blanket use of surrogate keys is almost always a mistake.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 18, 2004 at 6:29 pm
Im probably the only one that will say this, but I wouldn't rule out uniqueidentifiers in all cases. Especially if you need the ID to do a parent/child insert using them lets you do a lot more on the client (or on the server) with less work. They are a little bigger and not as friendly, but if you're using a surrogate key you shouldnt be looking at it much anyway!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply