CREATE SEQUENCE - 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

SQL Server 2008 Forums

Transact-SQL (2008)

CREATE SEQUENCE

Author

Topic

LHendren
Starting Member

17 Posts

Posted - 2012-03-12 : 11:04:00

I have two tables that need an initial value of one; increment by one; and cycle when the value reaches 9 in one table and 999 in another table.

When I use SSMS 2008, I get the error message "Unknown object type 'SEQUENCE' " when trying to CREATE SEQUENCE.

CREATE TABLE DocumentNo (SeqID int identity(1,1)primary key,
SeqVal varchar(1) ) works fine, but I need to cycle.

Thank you in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-12 : 11:14:20

CREATE SEQUENCE is only supported in SQL Server 2012, earlier versions will have to use identity, or custom code if you need to cycle the values.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-12 : 11:20:43

Perhaps you can use a computed column, like this?

CREATE TABLE DocumentNo (SeqID int identity(1,1)primary key,
SeqVal as 1+(SeqID-1)%9 )

Edit: Never mind. You really do need a SEQUENCE because your requirement seems to be the ability to manage both tables with a single sequence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 11:30:42

quote:
Originally posted by LHendren

I have two tables that need an initial value of one; increment by one; and cycle when the value reaches 9 in one table and 999 in another table.

When I use SSMS 2008, I get the error message "Unknown object type 'SEQUENCE' " when trying to CREATE SEQUENCE.

CREATE TABLE DocumentNo (SeqID int identity(1,1)primary key,
SeqVal varchar(1) ) works fine, but I need to cycle.

Thank you in advance

i think you need to create a seperate third table for generating sequence id and then use the value for inserts to other table. For cycling part how do you want inserts to happen?

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-12 : 11:39:38

Here's an example of cycling values using identity:

CREATE TABLE sequence1(i TINYINT NOT NULL IDENTITY(1,1))
GO
CREATE TRIGGER cycle_sequence1 ON sequence1 FOR INSERT AS
SET NOCOUNT ON
IF IDENT_CURRENT('sequence1')>=9 BEGIN
	DBCC CHECKIDENT('sequence1',RESEED,0)
END
GO

-- generate sample data
INSERT sequence1 DEFAULT VALUES
GO 20
SELECT * FROM sequence1

The (obvious) problem with this is that it will generate duplicate values.

LHendren
Starting Member

17 Posts

Posted - 2012-03-13 : 09:47:01

Thanks for all the replies - even though they were what I expected. I am re-writing dBase code to VB code and thought CREATE SEQUENCE would be a great way to generate the numbers and cycle them instead of mirroring this dBase code:

if mTotal1>0

use Generate
go bottom
store document to mPADocNo
store substr(mPADocNo,7,13) to mdoc
store substr(mPADocNo,9,13) to mSeg
store val(mdoc)+1 to vdoc
store substr(mPADocNo,1,6)+str(vdoc,7) to mPADocNo

if val(sequence) = "9"
store sequence to mSequence
store substr(mSequence,1,1) to mSequence
store val(mSequence)-8 to mSequence
store str(mSequence,1,1) to mSequence
else
store sequence to mSequence
store substr(mSequence,1,1) to mSequence
store val(mSequence)+1 to mSequence
store str(mSequence,1,1) to mSequence
endif

if line_item = 999
store line_item-998 to nPAReptSuff
store str(nPAReptSuff) to mPAReptSuff
else
store line_item to nPAReptSuff
store nPAReptSuff+1 to nPAReptSuff
store str(nPAReptSuff) to mPAReptSuff

endif

use WWTest
go bottom
replace Document with mPADocNo

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-13 : 10:33:38

The code I posted will simulate that dBase code, here's a version to handle 999:

CREATE TABLE sequence2(i SMALLINT NOT NULL IDENTITY(1,1))
GO
CREATE TRIGGER cycle_sequence2 ON sequence2 FOR INSERT AS
SET NOCOUNT ON
IF IDENT_CURRENT('sequence2')>=999 BEGIN
	DBCC CHECKIDENT('sequence2',RESEED,0)
END
GO

-- generate sample data
INSERT sequence2 DEFAULT VALUES
GO 2000
SELECT * FROM sequence2

LHendren
Starting Member

17 Posts

Posted - 2012-03-13 : 10:43:31

Thank you. This does save me some time.

CREATE TRIGGER....1st time I have used it. Powerful tool here as well as my future code.

Thanks again!

quote:

Originally posted by robvolk

The code I posted will simulate that dBase code, here's a version to handle 999:

CREATE TABLE sequence2(i SMALLINT NOT NULL IDENTITY(1,1))
GO
CREATE TRIGGER cycle_sequence2 ON sequence2 FOR INSERT AS
SET NOCOUNT ON
IF IDENT_CURRENT('sequence2')>=999 BEGIN
	DBCC CHECKIDENT('sequence2',RESEED,0)
END
GO

-- generate sample data
INSERT sequence2 DEFAULT VALUES
GO 2000
SELECT * FROM sequence2

LHendren
Starting Member

17 Posts

Posted - 2012-03-16 : 10:15:28

Once again, thank you for all the help. In order to hopefully help others in the future, here is what I ended up with:

CREATE TABLE Generate_DocumentNo (MiscDocumentNo SMALLINT NOT NULL IDENTITY(1000,1))
GO
CREATE TRIGGER cycle_MiscDocumentNo ON Generate_DocumentNo FOR INSERT
AS
SET NOCOUNT ON
IF IDENT_CURRENT('Generate_DocumentNo')>=9999999 BEGIN
DBCC CHECKIDENT('Generate_DocumentNo',RESEED,0)
END
GO

CREATE TABLE Generate_SequenceNo (MiscSequenceNo SMALLINT NOT NULL IDENTITY(1,1))
GO
CREATE TRIGGER cycle_MiscSequenceNo ON Generate_SequenceNo FOR INSERT
AS
SET NOCOUNT ON
IF IDENT_CURRENT('Generate_SequenceNo')>=9 BEGIN
DBCC CHECKIDENT('Generate_SequenceNo',RESEED,0)
END
GO

CREATE TABLE Generate_Line_ItemNo (MiscLine_ItemNo SMALLINT NOT NULL IDENTITY(1,1))
GO
CREATE TRIGGER cycle_MiscLine_ItemNo ON Generate_Line_ItemNo FOR INSERT
AS
SET NOCOUNT ON
IF IDENT_CURRENT('Generate_Line_ItemNo')>=999 BEGIN
DBCC CHECKIDENT('Generate_Line_ItemNo',RESEED,0)
END
GO

My VB code used these statements:

INSERT INTO table DEFAULT VALUES, SELECT * FROM table, and
DELETE FROM table WITH (READPAST)

Using the DELETE FROM and READPAST were also a big help in accomplishing this task by keeping the tables to only one row.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources