CODEWITHSUNDEEP
sql-servernumberssql-server-2012sequence
TheWommies
TheWommies

Reputation: 5072

Sequence numbers in SQL Server 2012

For example you have a table where you define its definition of one of its column to use a sequence number like follows

CREATE TABLE Sales.MyOrders
(
orderid INT NOT NULL
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs),
.....

If you manually insert some values into this table where you explicitly say orderid = 1;

would the sequence number update as well?

Just I think if it doesn't there are higher chances for primary key violations

Upvotes: 2

Views: 243

Answers (1)

dani herrera
dani herrera

Reputation: 51665

No, the sequence number is not update as well. And you can check it easily following this 3 simple steps:

1) Create the sequencer and the table:

CREATE SEQUENCE S as int;

CREATE TABLE MyOrders
(
n int,
orderid INT NOT NULL
CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR S)
);

2) Insert some values:

insert into myorders ( n) values (1);
insert into myorders ( n) values (2);
insert into myorders ( n, orderid) values (3,3);
insert into myorders ( n) values (4);

3) Check results:

| N |     ORDERID |
----|-------------|--
| 1 | -2147483648 |
| 2 | -2147483647 |
| 3 |           3 |   <-- by hand entry
| 4 | -2147483646 |   <-- no gap

Conclusion

DEFAULT clause is only executed when no value (null) is assigned to column. If you provide a value to column then sequencer is not called.

Upvotes: 2

Related Questions