6

I'm using SQL Server 2008 Express Edition.

I wanna create a sequence with this code:

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

and the error is

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

Can anyone help me?

Best Regards!

sam yi
  • 4,806
  • 1
  • 29
  • 40
user437925
  • 101
  • 1
  • 2
  • 3

2 Answers2

10

SQL Server 2008 doesn't know sequences yet - that'll be introduced in SQL Server 2012 (f.k.a. "Denali").

For pretty much the same result, use an INT IDENTITY column instead:

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

The IDENTITY column is automatically filled by SQL Server at the time you insert a new row into the table. SQL Server makes sure it's monotonically increasing, starting at 1, increasing by 1 (you can set these to different values, if needed).

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

Sequence objects are new with SQL Denali, SQL Server 2012 Here is some sample codes 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 a sequence table implemantation for SQL Server 2008 here : http://www.kodyaz.com/t-sql/sql-server-instead-of-trigger-with-sequence-table.aspx

Eralper
  • 6,461
  • 2
  • 21
  • 27
  • I remember being able to do this in postgresql... and loved it. Very happy it was added to SQL 2012! – sam yi Jan 03 '14 at 22:34