Sequences, Synonyms and Other Objects

31.1 SEQUENCE (2012+)

Tied to one table Independent object, shareable across tables Value known after insert Value known before insert Many, with cycling and range allocation

CREATE SEQUENCE dbo.seq_Invoice AS BIGINT START WITH 1000 INCREMENT BY 1 CACHE 50 NO CYCLE;
SELECT NEXT VALUE FOR dbo.seq_Invoice;
CREATE TABLE dbo.Invoices (InvoiceNo BIGINT DEFAULT NEXT VALUE FOR dbo.seq_Invoice, ...);
EXEC sp_sequence_get_range @sequence_name = N'dbo.seq_Invoice', @range_size = 100, @range_first_value = @first OUTPUT;
ALTER SEQUENCE dbo.seq_Invoice RESTART WITH 5000;

31.2 Synonyms

CREATE SYNONYM dbo.RemoteOrders FOR LinkedSrv.SalesDB.dbo.Orders;
SELECT * FROM dbo.RemoteOrders;

31.3 Other Database Objects

Rules and defaults (legacy objects): deprecated. Use CHECK and DEFAULT constraints.

Assemblies (CLR) Service Broker: asynchronous messaging and queues (message types, contracts, queues, services, SEND, RECEIVE)

Database-scoped configurations: ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

External tables, data sources, file formats: PolyBase (Module 49)