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 |
|
bsilvertab
Starting Member
2 Posts |
 Posted - 2009-04-10 : 14:47:23
|
| I am doing a project for school which requires me to increment a row called sequence (int) everytime the CustomerID field changes. Here is what I am talking about....CustomerID char(10) StaffID char(6) Sequence intX8X606LHH JACTAM 1X8X606LHH KORSTA 2X8X606LHH ROOKEN 3X8X606LHH WAGGAR 4X8X607DG LEVDIA 1X8X607DG ROOKEN 2X8X626EAB GUZKEL 1X8X626EAB SWIGLO 2X8X631ET MORKAR 1X8X631ET TOWCON 2X8X311MLS SCHDEN 1X8X311MLS VANKAR 2X8X323BMW KORSTA X8X323BMW ROOKEN X80307CRC GUZKEL X80307CRC SWIGLO This goes on for 350 rows. I think that the Identity command should be used to 'reseed' the sequence row, but I don't know how to make that happen everytime the customerID field changes. Any insight or help would be much appriciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-10 : 15:15:07
|
| You can not use identity for this. The table design is actually what we have in production right now for a particular system and it is causing deadlocks. I would highly recommend not using this approach (sequencer per customer) and instead just use an identity column (sequencer for the table). To answer your question, you'll need to find the max value for that customer and increment it by 1. The deadlocking issue occurs due to the need to lock the row and other things wanting to query it. I'm not sure if your school project would require the locking mechanism in there, but it is required to guarantee the integrity of the data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
bsilvertab
Starting Member
2 Posts |
Posted - 2009-04-10 : 15:31:31
|
| tkizer- I wouldn't do my table this way either, but that is what the assignment is requiring, so I am not worried about deadlocks. Do you have any examples of how to find a max value for the customer and increment it by 1? How do you find a max value of a char? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Sequence / Identity Project