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 |
|
buzzi
Starting Member
48 Posts |
 Posted - 2009-03-30 : 18:07:36
|
| Hello All,I have concurrency issue with one of our table and am trying to resolve the issue,Can somebody help me clarify thisThe table has a varchar field that is custom generated based on a functionthe first 5 characters currently holds 2 types, which are standard based on the input, and the rest of the value is incrementing by 1 for each insertType1:9999AType2:8888BSAMPLE DATAcol1 col21 9999A1012 9999A1023 9999A1034 8888B1015 8888B102the select statement i use to get the next increment value is like below:if we assume the input type is "A"; then the 2 paramters specified in the select will be as:@type = '9999A'@typeref = '9999A%'and the output should be basenumber = 104and then i have other formating statements which will give the final value of '9999A104' as per the sample dataselect @basenumber= SUBSTRING(col2,len(@type) + 1,(len(col2) - len(@type))) + 1 FROM table where col2 like @typerefI currently see 2 problems which i rectified1.the column was missing a unique constraint2.added the index on col2earlier it was doing a clusted index scan, now its doing a index seek, hope this helps a bit. But the problem is i am not storing the old & new values anywhere, and am running into the concurrency issue.Thanks a lot for the help |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-31 : 09:24:45
|
| You can use an identity column in the same table as the base for the incrementing number and have a second computed column which uses the identity column and a function to generate a custom code. this removes possibility of duplication so it can have a unique index.http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Autosequence Generation