SQL Server - Creating SEQUENCE

  Hello all,

     I am trying to create sequence in sql server with the following code---


CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE

 But it is not working, is the sequnce is support in sql server or not, please suggest me the write way.

hi,

CREATE SEQUENCE syntax

CREATE SEQUENCE name
  [ INCREMENT increment_value ]
  [ MINVALUE minimum_value ]
  [ MAXVALUE maximum_value ]
  [ START start_value ]
  [ CACHE cache_value ]
  [ CYCLE ]

Creates a new sequence generator that can be used to generate an iterative sequence of values. Sequence generators have a number of uses including the creation of primary keys for a table. The INCREMENT, MINVALUE, MAXVALUE, START, and CACHE values are all optional.

The INCREMENT value specifies how the sequence increments each iteration. By default a sequence generator increments by 1. The MINVALUE and MAXVALUE values specify the bounds of the sequence generator. By default MINVALUE and MAXVALUE are 0 and Long.MAX_VALUE respectively. The START value specifies the first key (exclusive) of the generator. The CACHE value specifies how many keys should be cached ahead of time.

Below is an example that creates a new sequence generator called 'seq_key_1' that starts at 10 and increments by 2 each iteration;

CREATE SEQUENCE seq_key_1 INCREMENT 2 START 10

A sequence generator is accessed by a call to the NEXTVAL function. The NEXTVAL function iterates the generator and returns the next value from the sequence. The NEXTVAL function is an atomic operation and guarantees that no two identical values will be returned regardless of the frequency or concurrency of calls to the function. Below is a simple example;

SELECT NEXTVAL('seq_key_1')

Hi,

Sequence can be created either

1. Using TSQL statement or by
2. Using SQL Server Management Studio (SSMS)

Note : Sequence is an object that has start value, increment value and end value defined in them and this sequence can be added to a column whenever required rather than defining an identity column individually for tables.

Lets take a quick look,

How to create a SEQUENCE using TSQL Statements

• Sequence can be created using a Create SEQUENCE Syntax

------ Create a SEQUENCE object on schema "dbo" by the name of TEST_Sequence
CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE

Lets take a quick look, How to use to SEQUENCE in populating values in table

--- Creating a Table Named Customer
CREATE TABLE Customer
 (
  Id        INT NOT NULL,
  Name    VARCHAR(100) NOT NULL
  )
go
----Populating Customer table, using TEST_Sequence to generate the Id column:
INSERT Customer (Id, Name)
VALUES
(NEXT VALUE FOR TEST_Sequence, 'Ram'),
(NEXT VALUE FOR TEST_Sequence, 'Rita'),
(NEXT VALUE FOR TEST_Sequence, 'Ron')

Lets’ Look at results, which we inserted using SEQUENCE

---Selecting Records form Customer Table
SELECT * FROM Customer

http://sqlserver-training.com/wp-content/uploads/image47.png

Hope it helps,,,,

  But i am also using the same statement and sql server 2005 is giving
the Error like this :

      Msg 102, Level 15, State 1, Line 1

      Incorrect syntax near 'SEQUENCE'.

I tested on my local system that are working what do you mean by creating sequance when we are creating it "A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. " But nothing see you physically until unless you use that sequance number in insert statement.

But if you don't have any specific use than just use identity column inseted of that sequance simply put auto incremneted column when creating table as follows

Create table tblname
(
Sno int identity(1,1)
SName varchar(200)
)

That's Ok,  but during generating sequence the error i am facing

      Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'SEQUENCE'.

 
            do u have an idea to solve the error OR is Sequence is supported by SQL Server Or not...

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created.  
It is mainly associated with indexing also check this below thing
This SEQUENCE feature come with Sql server 2012.
see more details in this link
http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx 

In older version best way is you can use identity column 
also see this link
http://www.extremeexperts.com/sql/Yukon/SequenceID.aspx 

Sequence is introduced in Sql Server 2011 onwards. so, if you would be using prior version, you would be definitely getting the error

Refer below post for using sequence
http://blog.sqlauthority.com/2011/01/29/sql-server-2011-introduction-to-sequence-simple-example-of-sequence/

Ok , OK  I got it. Thanks for Reply ....

Ok , OK  I got it. Thanks for Reply ....

You Welcome!