How do I create a table with a column that uses a sequence?

Question

I have the following

CREATE TABLE [MyTable]
(
 [ID] [bigint] PRIMARY KEY NOT NULL,
 [Title] [nvarchar](64) NOT NULL
)
CREATE SEQUENCE MyTableID
 START WITH 1
 INCREMENT BY 1
 NO CACHE
 ;
GO

I want to insert new records on MyTable and set the ID to the next value of the sequence. How can I do it? A trigger perhaps, or is there some other way? How?

As I am using SQL Server 2012, I don't want to use Identity because of the gap bug.

Answer 1

Assign it as the default property for the column

CREATE TABLE [MyTable]
(
 [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.MyTableID),
 [Title] [nvarchar](64) NOT NULL
);

Future readers, a Sequence can have a gap if the service stops unexpectedly, the value of the entities in CACHE can be lost. Here, they are specifying no cache to mitigate that with the tradeoff of slower performance for the sequence object.

CREATE SEQUENCE reference

• BrunoLM: Is it safe to use cache in this case? At the connect link Microsoft tells us to use no cache.
• Techie Joe: @BrunoLM It depends on how big/small of a db we're talking about and how many transactions per second/minute we're talking about.

Answer 2

To use a SEQUENCE in an INSERT statement, you could try this:

INSERT INTO [MyTable] ([ID],[TITLE]) VALUES (NEXT VALUE FOR dbo.MyTableID, @TITLE) 

NEXT VALUE FOR dbo.MyTableID is the syntax for obtaining the next number from a SEQUENCE.

Answer 3

You can use one sequence for multiple tables, as demonstrated by the following example:

CREATE SEQUENCE dbo.MyTableID
 START WITH 1
 INCREMENT BY 1
 NO CACHE
 ;
GO
CREATE TABLE dbo.[MyTable1]
(
 [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.MyTableID),
 [Title1] [nvarchar](64) NOT NULL
);
CREATE TABLE dbo.[MyTable2]
(
 [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.MyTableID),
 [Title2] [nvarchar](64) NOT NULL
);

--Insert 2 rows
insert into [MyTable1] (title1)
select 'title11'
insert into [MyTable1] (title1)
select 'title12';
insert into [MyTable2] (title2)
select 'title21'
insert into [MyTable2] (title2)
select 'title22';
select f1.*, 'Into MyTable1' Tb from [MyTable1] f1
union all
select f1.*, 'Into MyTable2' Tb from [MyTable2] f1

Output:

+----+---------+---------------+
| ID | Title1 | Tb |
+----+---------+---------------+
| 1 | title11 | Into MyTable1 |
| 2 | title12 | Into MyTable1 |
| 3 | title21 | Into MyTable2 |
| 4 | title22 | Into MyTable2 |
+----+---------+---------------+

sqlfiddle

• Serj Sagan: This a terrible and very lazy way to do things! Don't do it!
• Esperento57: To say that or to say nothing is the same thing ...