snippetsqlModerate
How do I create a table with a column that uses a sequence?
Viewed 0 times
createcolumnwithusessequencethathowtable
Problem
I have the following
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.
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
;
GOI 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.
Solution
Assign it as the default property for the column
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
CREATE SEQUENCE reference
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
Code Snippets
CREATE TABLE [MyTable]
(
[ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.MyTableID),
[Title] [nvarchar](64) NOT NULL
);Context
StackExchange Database Administrators Q#53261, answer score: 17
Revisions (0)
No revisions yet.