Increment Sequence number - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Increment Sequence number

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-04-06 : 13:58:50

I have a table where there is a sequence number field is there. So when a user inserts a new record he can choose a sequence number. If the use picked an existing sequence number then I have to increment the following records sequence number by one.
What is the best approach to do this?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 14:04:59

whats the need for allowing user to select seq number? it would be much easier if you would have made it an identity column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-04-06 : 14:14:02

quote:
Originally posted by visakh16

whats the need for allowing user to select seq number? it would be much easier if you would have made it an identity column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Priority of Items can change that is done with this sequence number. Also these records can be grouped and the sequence number will apply by groups as well.

Sachin.Nand

2937 Posts

Posted - 2010-04-06 : 15:01:03

Please post some sample data of what exactly you want.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 00:07:06

Will you be doing batch inserts also? Also is there a chance of users concurrently inserting to table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-07 : 11:13:50

This link will help you to post your question with appropriate data scripts for us to be able to help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

But, until then I can take a guess with some pseudo code:

IF EXISTS (SELECT * FROM MyTable WHERE Seq = <NewSeq> AND Key = <Key>)
BEGIN
	UPDATE MyTable
	SET Seq = Seq + 1
	WHERE Key = <Key>
	AND Seq >= <Seq>
END

INSERT MyTable (Key, Seq, <other columns>)
VALUES (<key>, <Seq>, <other columns>

Kristen
Test

22859 Posts

Posted - 2010-04-07 : 12:10:33

We do this with a "Sequence" column - completely separate from any other ID info, and just used to order the records.

We use a floating point number so that we can insert in-between existing numbers; and we have a "renumber" routine which redistributes all the sequence numbers evenly again.

The alternative is to do:


UPDATE U
SET MySequence = MySequence + 1
FROM MyTable AS U
WHERE MySequence >= @NewInsertNumber

INSERT INTO MyTable(MySequence, ...)
VALUES(@NewInsertNumber, ...)

but this will be frequently updating most/many rows in the table, which is inefficient and will generate lots of logged transaction data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 12:37:42

quote:
Originally posted by Kristen

We do this with a "Sequence" column - completely separate from any other ID info, and just used to order the records.

We use a floating point number so that we can insert in-between existing numbers; and we have a "renumber" routine which redistributes all the sequence numbers evenly again.

The alternative is to do:

UPDATE U
SET MySequence = MySequence + 1
FROM MyTable AS U
WHERE MySequence >= @NewInsertNumber

INSERT INTO MyTable(MySequence, ...)
VALUES(@NewInsertNumber, ...)

but this will be frequently updating most/many rows in the table, which is inefficient and will generate lots of logged transaction data.

what if you've concurrent batch inserts?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-07 : 12:48:21

"what if you've concurrent batch inserts?"

yeah, good point, "all bets are off" ... which is another reason why we don't do it that way

But sticking that UPDATE in a TRANSACTION would do (i.e. make it ATOMic), wouldn't it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 12:50:52

yeah..thats fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-04-07 : 17:59:35

quote:
Originally posted by Lamprey

This link will help you to post your question with appropriate data scripts for us to be able to help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

But, until then I can take a guess with some pseudo code:
IF EXISTS (SELECT * FROM MyTable WHERE Seq = <NewSeq> AND Key = <Key>)
BEGIN
UPDATE MyTable
SET Seq = Seq + 1
WHERE Key = <Key>
AND Seq >= <Seq>
END

INSERT MyTable (Key, Seq, <other columns>)
VALUES (<key>, <Seq>, <other columns>

This is the table structure
CREATE TABLE [dbo].[Departments](
[DepartmentId] [int] NOT NULL,
[HeadingId] [int] NOT NULL,
[DepartmentName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Departmentlink] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DepartmentSequenceOrder] [int] NULL,
CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
(
[DepartmentId] ASC,
[HeadingId] ASC,
[DepartmentName] ASC
) ON [PRIMARY]
) ON [PRIMARY]

while inserting it should increment the sequence number if the insert hapens in between records.
Also when one can update the sequence this will reorder the sequence by changing other sequence numbers .
When deleting a record I need to reorder.

Always the sequence should start from 1 and increment there should be no missing number. If someone deletes 2 then 3 becomes 2 and 4 becomes 3 like that.

Kristen
Test

22859 Posts

Posted - 2010-04-08 : 03:05:44

Why do you need the numbers to be contiguous, rather than just an ascending sequence?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 05:14:37

it would become really costly to do the reordering on each insert when table data grows over time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources