Sequencing and Maintaining Sequence

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sequencing and Maintaining Sequence

Author

Topic

2400hrs
Starting Member

4 Posts

Posted - 2006-07-21 : 16:27:05

I have a table with a list of services we offer. Services are part of a category. The service ID should be uniquely numbered per category, so I dont believe the identity column would work:


categoryid  serviceid
   1           1           
   1           2
   1           3
   2           1
   2           2

I need a way to generate this new service ID when a record is inserted. This is multi user and many people could be adding a new service to a category at once.

[CODE]
CREATE TABLE Service (
categoryid integer,
serviceid integer
)
[/CODE]

Is this an ideal way, and is it safe against the same sequence being given twice?

[CODE]
CREATE PROCEDURE dbo.spNewService
@category integer
AS
BEGIN

INSERT Service
SELECT @category, SELECT coalesce(MAX(serviceid),0)+1
WHERE categoryid = @category

END
[/CODE]

I do wonder if while its SELECTing the MAX(serviceid) another transaction has just selected and about to insert.

The next stage after that works. The service ID is mainly used to provide an ordering, all new services go to the end; the user might want to reorder them, again this needs to be done in a way which prevents sequence numbers being duplicated by two processes doing 2 things on the table at once. I came up with something to swap two adjacent records, but not sure how efficient or safe it is?

[CODE]
CREATE PROCEDURE dbo.spSwapAdjacentRecords
@service integer
AS
BEGIN

UPDATE Service
SET serviceID =
CASE
WHEN serviceid = @service THEN @service -1
WHEN serviceid = @service -1 THEN @service
END
WHERE (serviceid = @service OR serviceid = @service-1)

END
[/CODE]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-21 : 16:43:23

If you create a unique constraint you will be safe from duplicates.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-22 : 02:20:02

See if this helps
http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx

Madhivanan

Failing to plan is Planning to fail

2400hrs
Starting Member

4 Posts

Posted - 2006-07-23 : 09:57:14

On the 1st solution in that link, I think I could use by holding the next sequence number in the category table. However it says this is prone to deadlock, I do not want to lock up the category table when generating the next sequence for a service within that category.

The 2nd solution isn't feasible, I'd need a dummy identity table for each category, and there could be 100s of categories, some with just 1 or 2 services (and there could be certain categories with 1000s of services)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-07-24 : 20:38:20

If the service ID needs to be unique per category then why can't you just make a new column as a normal identity column ('origialServiceId') and copy that into ServiceID on your initial insert? Sure it will be unique across categories as well but it will still fulfill your original rules.
If you need to swap two over then just get the primary keys for the two rows and update the sequence with each other's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources