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

Somesh Yadav replied to dipak sinha on 13-Feb-12 05:14 AM

Hi,

Sequence can be created either

Using TSQL statement or by

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_SequenceCREATESEQUENCE TEST_Sequence
ASINTSTARTWITH 1
INCREMENT BY 1
MINVALUE 0
NO MAXVALUE

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

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'.

Web Star replied to dipak sinha on 13-Feb-12 05:23 AM

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

kalpana aparnathi replied to dipak sinha on 13-Feb-12 05:26 AM

hi,

CREATE SEQUENCE myseq INCREMENT 1 MINVALUE 1 START 1

http://www.mckoi.com/database/SQLSyntax.html#7

Seems as though your 'with' and 'by' statements are superfluous.

Regards,

dipak sinha replied to Web Star on 13-Feb-12 05:30 AM

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...

Web Star replied to dipak sinha on 13-Feb-12 05:56 AM

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

Sandeep Mittal replied to dipak sinha on 13-Feb-12 06:06 AM

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