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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Look at the following article: https://www.learnjavaupdate.com/2023/04/sequence-in-sql.html

CREATE SEQUENCE company_seq
   START WITH 100
   INCREMENT BY 1
   MAXVALUE 999
   NOCACHE
   NOCYCLE;

The above script creates a sequence called company_seq that starts at 100 and increments by 1 for each subsequent call to the NEXTVAL function. The sequence has a maximum value of 999, meaning that once the sequence reaches 999, it will stop generating values. The NOCACHE option specifies that Oracle should not cache sequence values, and the NOCYCLE option specifies that the sequence should not cycle back to its starting value when it reaches its maximum value.

Once the sequence is created, you can use the NEXTVAL function to generate unique values:

INSERT INTO companies (company_id, company_name)

VALUES (company_seq.NEXTVAL, 'Dummy');

Answer 7

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.