Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

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

share|improve this answer
    
Is it safe to use cache in this case? At the connect link Microsoft tells us to use no cache. –  BrunoLM Nov 13 '13 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 Nov 13 '13 at 16:08
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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