SQL Server 2008 Express Edition - how to create a sequence

Question

I am using SQL Server 2008 Express Edition.

I want to create a sequence with this code:

CREATE SEQUENCE Postoffice_seq AS bigint START WITH 1 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE; 

and mistake

Msg 343, Level 15, State 1, Line 1
Unknown object type "SEQUENCE" used in a CREATE, DROP, or ALTER statement.

Can anybody help me?

Best wishes!

Answer 1

SQL Server 2008 does not yet know the sequence - it will be introduced in SQL Server 2012 (fka "Denali").

For almost the same result, use the INT IDENTITY column:

 CREATE TABLE dbo.YourTable (YourID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, .... ) 

The IDENTITY column is automatically populated by SQL Server when a new row is inserted into the table. SQL Server ensures that it grows monotonously, starting from 1, increasing by 1 (you can set them at different values).

Basically, when you insert a row into such a table, you should not indicate the IDENTITY column in the list of columns to insert values ​​into - SQL Server will do this automatically for you.

Answer 2

Sequence objects are new with SQL Denali, SQL Server 2012 Here are some sample code http://www.kodyaz.com/sql-server-2012/number-of-sequences-in-sql-server-2012-using-sequence-objects .aspx for sequence objects in SQL 2012

You can find the implementation of the sequence table for SQL Server 2008 here: http://www.kodyaz.com/t-sql/sql-server-instead-of-trigger-with-sequence-table.aspx