Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chorofonfilo
Starting Member
40 Posts |
 Posted - 2008-04-03 : 18:37:15
|
| Hi, thank you very much for your time and your help on this in advance. If i have a multi user application in which a new record is gonna be registered on a table, and the id of this record is gonna be generated counting the existing records before the insertion and adding 1 afterwards, to generate a numeric type id for it, and finally i would be retrieving this new id, to add it to another table thats gonna be ready to receive future data related to this newly record, would be a problem to do it ussing something like this?.Create procedure performinsertion@nam varchar(50),@idat datetime,@ldat datetime,@ddat datetime,@medid int,@desc varchar(100)as Declare @id intset @id=0Select @id=count(*) from table1set @id=@id+1Insert into table1(id,name,indate,lodate,ddate)values(@id,@nam,@idat,@ldat,@ddat)Insert into table2(medid,id,desc)values(@medid,@id,@desc)I am not ussing an identity value, and thats how i would generate the consecutive ids, but if the insertion is performed at the same time by multiple users, Could this turn into a problem because the users would get the same number of records to insert the new id or each request would be separately?, and also could this be a problem on the retrieving action on a multi user scenario regardless of ussing triggers or not?,which would be the best way to perform all this?.I am sorry for the long post, would be cool if you guys help me to clarify this.Thank you very much again.Perseverance worths it...:) |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-03 : 18:42:49
|
| Identity column is the way to go.An identity column would allow many concurrent inserts to work without any id collisions. That is the way to go. One problem with count(*)+1 is it will be very slow as the table grows. Another problem is that when rows are deleted you get primary key violations. Another problem is that concurrent users will likely get the same @id resulting in a primary key violation.Be One with the OptimizerTG |
 |
|
|
chorofonfilo
Starting Member
40 Posts |
Posted - 2008-04-03 : 19:15:01
|
quote:
Originally posted by TG
Identity column is the way to go.An identity column would allow many concurrent inserts to work without any id collisions. That is the way to go. One problem with count(*)+1 is it will be very slow as the table grows. Another problem is that when rows are deleted you get primary key violations. Another problem is that concurrent users will likely get the same @id resulting in a primary key violation.Be One with the OptimizerTG
Hi Tg, i understand perfectly what you say, but what if i need to generate a varchar id like (00001) based on counting the ones added before, is the retrieving method of the newly created id well done in this case? and finally does an identity column really grants you different ids, in a case of a same time insertion by mutiple users?.Thanks for helping me figuring this out.Perseverance worths it...:) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-03 : 19:52:07
|
| >>and finally does an identity column really grants you different ids, in a case of a same time insertion by mutiple users?.Absolutely. There is no way 2 rows can end up with the same value based on an identity. At least because of concurrent inserts, I mean it's possible if someone RESEEDs the identity value but that would be the only way it could happen.The varchar version "00001" is what I consider to be a presentation issue. If you have some business reason to present that format then you can do that in the presentation layer or convert it when you select it but IMO it should be based on an underlying integer.Be One with the OptimizerTG |
|
|
|
chorofonfilo
Starting Member
40 Posts |
Posted - 2008-04-04 : 09:33:07
|
quote:
Originally posted by TG
>>and finally does an identity column really grants you different ids, in a case of a same time insertion by mutiple users?.Absolutely. There is no way 2 rows can end up with the same value based on an identity. At least because of concurrent inserts, I mean it's possible if someone RESEEDs the identity value but that would be the only way it could happen.The varchar version "00001" is what I consider to be a presentation issue. If you have some business reason to present that format then you can do that in the presentation layer or convert it when you select it but IMO it should be based on an underlying integer.Be One with the OptimizerTG
Thank you TG, your suggestion is really helpfull, i am just kinda afraid of ussing identity columns because they have an strange behaviour on failed transactions, but i guess i can use the Select @variable=@@identity form to retrieve the value and add it to the next table as the generated newly id to make the multiple insertion.Would u suggest me a workaround if the transaction fails, and a gap on the identity value appears?.Thank you very much for sharing your knowledge.Perseverance worths it...:) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-04 : 09:57:45
|
| scope_identity() would be the preferred method of retrieving the last identity value inserted by session that called scope_identity(). That only works for the final row added so on a multi-row insert only the last identity value is returned.An identity column is designed to be a surrogate key. It should have no meaning to business logic. it is only a means to uniquely identify rows and use for referencial integrity. Gaps in the values are normal and should have no impact functionality. Is there a real need for the values to be sequencial?Be One with the OptimizerTG |
|
|
|
chorofonfilo
Starting Member
40 Posts |
Posted - 2008-04-04 : 19:28:17
|
| Thanks a lot TG, i have clarified this with your help, and now i know how to make the multi insert possible, i don't know if there are other safe ways to make it work, so i will do some research on it, and i will post the links here for those who might be interested, if there are more ways to perform this, would also be great to read about them here and help us to enlighten our minds a little more :).This is a good link to understand the difference between @@identity and scope_identity btw: http://msdn2.microsoft.com/en-us/library/aa259185(SQL.80).aspx Have a great weekend all you guys and gracias TG again.Perseverance worths it...:) |
|
|
|
|
|
|
|
|
Retrieving a new id and puting it in other table.