T-SQL – identity vs. sequence

There are two ways to have a column which contains unique, ordered numbers can be treated as primary key.

Identity – the old concept. Is a column property. Only one column in a table can have identity.

CREATE TABLE Sales.MyOrders -- creating identity
(
orderid INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_MyOrders_orderid PRIMARY KEY,
custid INT NOT NULL
);

SELECT IDENT_CURRENT('HR.Employees'); -- returns last identity value

Sequence is relatively new concept. It is an database object, not a property.

CREATE SEQUENCE TestASequence; -- create sequence as bigint

CREATE SEQUENCE TestA2Sequence AS INT
MINVALUE -100 MAXVALUE 100 START WITH 0 NO CYCLE; -- create sequence as int

SELECT NEXT VALUE FOR TestA2Sequence; - selects the next sequence number


CREATE TABLE TestA -- create table using sequence
(
ID INT NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR TestA2Sequence,
NAME NVARCHAR(50) NOT NULL
);

ALTER TABLE TestA --alter existing table to have a sequence
ADD CONSTRAINT DF_TestA_ID DEFAULT (NEXT VALUE FOR TestA2Sequence) FOR ID;

INSERT INTO TestA(NAME) VALUES (N'Joe'); -- insert data using sequence

DROP SEQUENCE TestASequence; -- delete the sequence
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s