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 |
|
dve
Starting Member
19 Posts |
 Posted - 2005-08-26 : 16:31:22
|
| Hi,I need SQL server to generate unique sequential numbers for me so I can compute a custom record id: For instance: - 1 would become #20050826-0001- 2 would become #20050826-0002- etc..Right now my code (C#)1) opens a connection2) starts a transaction3) updates a counter table (UPDATE counters SET counter = counter + 1 WHERE counterId = 'someId')4) selects the new counter (SELECT counter FROM counters WHERE counterId = 'someId'5) reads the result6) commits the transaction7) closes the connection8) uses the number to insert a new record (this is done in a transaction different from the ones above)I update the counters table in step 3 to get a lock there so the next person will have to wait until the transaction completes to get the next number...voila; there I have a unique sequential number but this method is a performance bottleneckIs there a more efficient/ faster way to do this???? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-08-26 : 16:38:18
|
| Well I would use stored procedures, and I would have the table like thisDoes it have to be a sequential # for each account?Why not just make it a composite key with Account and datetime, or IDENTITY?Other than that, You'd be better off in a sproc at the very least.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
dve
Starting Member
19 Posts |
Posted - 2005-08-26 : 16:45:39
|
| Hi,The problem is that i may be inserting many records in one transaction and I can't have the sequential number generation happening in the same transaction because that would lock out other users...this is my situation pseude-code wise ;-) SqlTransaction transaction = connection.BeginTransaction();for(int i=0;i<10000;i++){ //get next number int number = GetNextNumber(); //compute custom id string customId = ComputeCustomId(number); //insert record InsertRecord(customId, ..., ..., ..., connection, transaction);}transaction.Commit();Many users may be doing this at the same time! So the GetNextNumber() method should be unaware of the transaction nescessary to transactionally correct insert 10000 records... Bear in mind that I'm on a web-farm here so only sql server can provide me with unique numbers...If you know how to do this with a sproc! Please let me know!!!Thanks for you help! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-26 : 18:36:55
|
| You still haven't explained why an IDENTITY column would not work for you.Using IDENTITY, you would just insert the new row, and then get the value of @@IDENTITY, if you need it. SQL Server takes care of generating the new number, it is guaranteed to be unique, and you don’t have to worry about concurrency issues.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Generating sequential numbers fast...