CODEWITHSUNDEEP
sql-serversql-server-2008sequence
punitha
punitha

Reputation: 71

How to create sequence in SQL Server 2008

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

Upvotes: 7

Views: 45664

Answers (7)

Nihar
Nihar

Reputation: 1

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');

Upvotes: 0

Lankesh Meshram
Lankesh Meshram

Reputation: 1

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

Upvotes: 0

Graeme
Graeme

Reputation: 1206

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

Upvotes: 9

ernesto petit
ernesto petit

Reputation: 1615

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

Upvotes: 2

ban
ban

Reputation: 21

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

Upvotes: 1

Michael Green
Michael Green

Reputation: 1491

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.

Upvotes: 2

corrin
corrin

Reputation: 63

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.

Upvotes: -3

Related Questions