How to create sequence in SQL Server 2008

Question

I am creating sequence in SQL Server with the following code. But it displays error as unknown object type. Please give a solution

Here's my code :

create sequence seqval start with 100 increment by 1 minvalue 0 maxvalue 0 no cycle  
 no cache;

thanks in advance

AFAIK there is no `SEQUENCE` syntax in sql server 2008 .It is available only in `2012` — praveen, Mar 23 '13 at 08:20
then can u plz let me know how to create auto generated key in sql2008??? — punitha, Mar 23 '13 at 08:48
Sequences are new in SQL Server **2012** and not available in earlier versions. Use `CREATE TABLE YourTable (ID INT IDENTITY)` - the mechanism in SQL Server 2008 is `IDENTITY`. [Read about `IDENTITY` on MSDN SQL Server Books Online](http://msdn.microsoft.com/en-us/library/ms186775%28v=sql.100%29.aspx) — marc_s, Mar 23 '13 at 10:02
possible duplicate of [SQL Server - Implementing sequences](http://stackoverflow.com/questions/6750614/sql-server-implementing-sequences) — Gabe, Dec 24 '13 at 17:23
Refer https://blogs.msdn.microsoft.com/sqlcat/2006/04/10/sql-server-sequence-number/ and https://gist.github.com/Jumpercables/7601830 — Kunal, Aug 18 '17 at 21:27

score 9 · Answer 1 · answered Feb 11 '16 at 16:44

9

You can do this.

--Create a dummy TABLE to generate a SEQUENCE. No actual records will be stored.
CREATE TABLE SequenceTABLE
(
    ID BIGINT IDENTITY  
);
GO

--This procedure is for convenience in retrieving a sequence.
CREATE PROCEDURE dbo.GetSEQUENCE ( @value BIGINT OUTPUT)
AS
    --Act like we are INSERTing a row to increment the IDENTITY
    BEGIN TRANSACTION;
    INSERT SequenceTABLE WITH (TABLOCKX) DEFAULT VALUES;
    ROLLBACK TRANSACTION;
    --Return the latest IDENTITY value.
    SELECT @value = SCOPE_IDENTITY();
GO

--Example execution
DECLARE @value BIGINT;
EXECUTE dbo.GetSEQUENCE @value OUTPUT;
SELECT @value AS [@value];
GO

answered Feb 11 '16 at 16:44

Graeme

1,068
12
14

1

If using this inside a TRANSACTION, you will need to change the procedure to remove the TRANSACTION and DELETE the row you INSERTed, or you need to use a Job to periodically clean up the table, as needed. – Graeme Feb 11 '16 at 22:05
Wouldn't it be better to use SELECT @value = IDENT_CURRENT('SequenceTABLE'); instead of SCOPE_IDENTITY(); to ensure the table you are getting the identity from is the one you want to? – Daniel Rodríguez Apr 24 '19 at 13:29

score 2 · Answer 2 · edited May 23 '17 at 11:55

Create a Numbers table; here's a SO question on the subject. Let's call it dbo.Number.

Have a table with an identity column. Set the seed and step to whatever is appropriate:

create table dbo.SequenceGenerator(ID int identity(1, 1), dummy int);

Then insert values from the numbers table and capture the newly-generated identity values:

declare @HowMany int = 3;  -- This determines how large a sequence you receive
                           -- at each itteration
declare @NewSequenceValue table (ID int);

insert dbo.SequenceGenerator(dummy)
output INSERTED.ID 
    into @NewSequenceValue
select Number from dbo.Numbers
where Number <= @HowMany;

select * from @NewSequenceValue;

Be sure to DELETE .. dbo.SequenceGenerator from time to time, else it will get big for no additional value. Do not TRUNCATE it - that will reset the IDENTITY column to its initally-declared seed value.

yes I agree. I found this blog entry http://blogs.msdn.com/b/askjay/archive/2012/10/08/sequence-objects-in-sql-2012-and-sql-2008.aspx, which describes the same solution nicely, and compares it with SQL 2012's capabilities. — NotSoOldNick, Jul 27 '15 at 05:18

score 2 · Answer 3 · edited Sep 30 '15 at 13:01

2

SQL Server 2008 can't create sequences, Sequence objects apply to SQL Server 2012 through current versions.

https://msdn.microsoft.com/es-es/library/ff878091(v=sql.120).aspx

You can use an IDENTITY in your table instead, for example:

CREATE TABLE Person(
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(255) NOT NULL
);

The starting value for IDENTITY is 1, and it will increment by 1 for each new record.

http://www.w3schools.com/sql/sql_autoincrement.asp

edited Sep 30 '15 at 13:01

beercohol

2,527
11
25

answered Jul 28 '15 at 13:16

ernesto petit

1,214
1
12
17

1

While a valid answer it's a little out of context. The OP did not state the sequence was intended for a tables key, it may be required and often is for use case other than a key. – Brett Ryan Mar 03 '16 at 15:28

score 1 · Answer 4 · edited Jun 10 '14 at 23:29

1

WITH N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N3)
SELECT * FROM nums

edited Jun 10 '14 at 23:29

Lucas Zamboulis

2,494
5
23
27

answered Jun 10 '14 at 22:53

ban

21
4

score 0 · Answer 5 · edited Sep 12 '19 at 06:41

0

We can't use Sequence easily in SQL Server 2008.

You can use CTE(Common Table Expressions) for Sequence Generation in SQL Server 2008

WITH NUM_GEN (n) AS
     ( 
            SELECT 1 
            UNION 
                  ALLSELECT n+1 
            FROM  NUM_GEN 
            WHERE n+1< MAX_VALUE 
     ) 
SELECT n 
FROM   NUM_GEN

edited Sep 12 '19 at 06:41

theduck

2,529
13
16
23

answered Sep 12 '19 at 05:33

Lankesh Meshram

1

score -3 · Answer 6 · answered Mar 23 '13 at 08:08

Are you sure you're running 2012? I had no trouble with:

CREATE SEQUENCE seqval
START WITH 100
INCREMENT BY 1 
minvalue 100 maxvalue 10000 NO CYCLE

Your 0,0 values generated a syntax error for me but a clear and simple one.

The minimum value for sequence object 'seqval' must be less than its maximum value.

How to create sequence in SQL Server 2008

6 Answers6

Linked

Related