12

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.

3 Answers 3

17

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

2
  • Is it safe to use cache in this case? At the connect link Microsoft tells us to use no cache.
    – BrunoLM
    Commented Nov 13, 2013 at 15:41
  • @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.
    – Techie Joe
    Commented Nov 13, 2013 at 16:08
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.

0

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

2
  • This a terrible and very lazy way to do things! Don't do it!
    – Serj Sagan
    Commented Jan 21, 2021 at 19:13
  • To say that or to say nothing is the same thing ... Commented Jan 21, 2021 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.