Can't create sequence in SQL Server 2008

I have tried to create a sequence in SQL Server 2008 using the following query,

CREATE SEQUENCE serial START 100

I got the following syntax error,

Msg 102, Level 15, State 1, Line 1

Incorrect syntax nea开发者_开发问答r 'SEQUENCE'.

How to create a sequence in SQL Server 2008?

You just cannot do this.

SQL Server 2008 does not have the concept of a SEQUENCE - this is a new feature in SQL Server 2012 (MSDN documentation here).

Typically, you want to create an "auto-increasing" column for your table - in that case, use the IDENTITY column attribute instead:

CREATE TABLE dbo.YourTable
( TableID INT IDENTITY,
 .....

Make use of IDENTITY column will do your task.

Get full details on msdn : IDENTITY

How marc_s has wrote, SQL Server 2008 does not have sequences yet. But we can use some trick to imitate sequences behaviour. Sometimes we need generate identifier for some table without inserting any data. Or generate IDs for different tables. In such situation I use this structure.

Create a table:

create table IdSequence (id int identity(1,1))

Then I use this script to generate Id:

begin tran 
 insert into IdSequence output inserted.id default values 
rollback tran

In my app it looks like:

    public Int32 GetNextId()
    {
        Int32 id = 0;
        using (var transaction = Session.BeginTransaction())
        {
            id = Session.CreateSQLQuery("insert IdSequence output inserted.id default values").UniqueResult<Int32>();
            transaction.Rollback();
        }

        return id;
    }