不同於插入資料列時產生的識別欄位值,應用程式可以藉由呼叫 NEXT VALUE FOR 函數,在插入資料列之前取得下一個序號。 此序號是在呼叫 NEXT VALUE FOR 時配置的,即使該編號從未插入資料表也一樣。 NEXT VALUE FOR 函數可當做資料表定義中資料行的預設值使用。 您可以使用 sp_sequence_get_range 一次取得多個序號範圍。
下列範例會建立名為 Test 的結構描述、名為 Orders 的資料表,以及名為 CountBy1 的順序,然後使用 NEXT VALUE FOR 函數,將資料列插入資料表。
CREATE SCHEMA Test;
GO
CREATE TABLE Test.Orders
(
OrderID INT PRIMARY KEY,
Name VARCHAR (20) NOT NULL,
Qty INT NOT NULL
);
GO
CREATE SEQUENCE Test.CountBy1
START WITH 1
INCREMENT BY 1;
GO
INSERT Test.Orders (OrderID, Name, Qty)
VALUES ( NEXT VALUE FOR Test.CountBy1, 'Tire', 2);
INSERT test.Orders (OrderID, Name, Qty)
VALUES ( NEXT VALUE FOR Test.CountBy1, 'Seat', 1);
INSERT test.Orders (OrderID, Name, Qty)
VALUES ( NEXT VALUE FOR Test.CountBy1, 'Brake', 1);
GO
SELECT *
FROM Test.Orders;
GO
結果集如下所示。
OrderID Name Qty
1 Tire 2
2 Seat 1
3 Brake 1
B. 在插入資料列之前呼叫 NEXT VALUE FOR
下列範例會使用在範例 A 中建立的 Orders 資料表來宣告名為 @nextID的變數,然後使用 NEXT VALUE FOR 函數,將此變數設定為下一個可用的序號。 應用程式應該會針對訂單進行一些處理,例如為客戶提供其潛在訂單的 OrderID 編號,然後驗證訂單。 不論這項處理可能需要多少時間,或者在處理期間加入其他多少訂單,原始的編號都會保留給這個連接使用。 最後, INSERT 陳述式會將訂單加入至 Orders 資料表。
DECLARE @NextID AS INT;
SET @NextID = NEXT VALUE FOR Test.CountBy1;
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (@NextID, 'Rim', 2);
CREATE SCHEMA Audit;
GO
CREATE SEQUENCE Audit.EventCounter
AS INT
START WITH 1
INCREMENT BY 1;
GO
CREATE TABLE Audit.ProcessEvents
(
EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
EventTime DATETIME DEFAULT (getdate()) NOT NULL,
EventCode NVARCHAR (5) NOT NULL,
Description NVARCHAR (300) NULL
);
GO
CREATE TABLE Audit.ErrorEvents
(
EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
EventTime DATETIME DEFAULT (getdate()) NOT NULL,
EquipmentID INT NULL,
ErrorNumber INT NOT NULL,
EventDesc NVARCHAR (256) NULL
);
GO
CREATE TABLE Audit.StartStopEvents
(
EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
EventTime DATETIME DEFAULT (getdate()) NOT NULL,
EquipmentID INT NOT NULL,
StartOrStop BIT NOT NULL
);
GO
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (248, 0);
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (72, 0);
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (2735, 'Clean room temperature 18 degrees C.');
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (18, 'Spin rate threshold exceeded.');
INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)
VALUES (248, 82, 'Feeder jam');
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (248, 1);
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (1841, 'Central feed in bypass mode.');
SELECT EventID,
EventTime,
Description
FROM Audit.ProcessEvents
UNION
SELECT EventID,
EventTime,
EventDesc
FROM Audit.ErrorEvents
UNION
SELECT EventID,
EventTime,
CASE StartOrStop WHEN 0 THEN 'Start' ELSE 'Stop' END
FROM Audit.StartStopEvents
ORDER BY EventID;
結果集如下所示。
EventID EventTime Description
1 2009-11-02 15:00:51.157 Start
2 2009-11-02 15:00:51.160 Start
3 2009-11-02 15:00:51.167 Clean room temperature 18 degrees C.
7 2009-11-02 15:00:51.180 Central feed in bypass mode.
D. 在結果集中產生重複的序號
下列範例會示範序號的兩種功能:循環以及在 SELECT 陳述式中使用 NEXT VALUE FOR 。
CREATE SEQUENCE CountBy5
AS TINYINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE;
GO
SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup,
Name
FROM sys.objects;
GO
E. 使用 OVER 子句來產生結果集的序號
下列範例會使用 OVER 子句並依照 Name 排序結果集,然後再加入序號資料行。
USE AdventureWorks2022;
GO
CREATE SCHEMA Samples;
GO
CREATE SEQUENCE Samples.IDLabel
AS TINYINT
START WITH 1
INCREMENT BY 1;
GO
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
ProductID,
Name,
ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';
CREATE SCHEMA Test;
GO
CREATE TABLE Test.Department
(
DepartmentID SMALLINT IDENTITY (1, 1) NOT NULL,
Name NVARCHAR (100) NOT NULL,
GroupName NVARCHAR (100) NOT NULL CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC)
);
GO
INSERT Test.Department (Name, GroupName)
VALUES ('Engineering', 'Research and Development');
GO
INSERT Test.Department (Name, GroupName)
VALUES ('Tool Design', 'Research and Development');
GO
INSERT Test.Department (Name, GroupName)
VALUES ('Sales', 'Sales and Marketing');
GO
SELECT *
FROM Test.Department;
GO
ALTER TABLE Test.Department
ADD DepartmentIDNew SMALLINT NULL;
GO
UPDATE Test.Department
SET DepartmentIDNew = DepartmentID;
GO
ALTER TABLE Test.Department DROP CONSTRAINT [PK_Department_DepartmentID];
ALTER TABLE Test.Department DROP COLUMN DepartmentID;
GO
EXECUTE sp_rename 'Test.Department.DepartmentIDNew', 'DepartmentID', 'COLUMN';
GO
ALTER TABLE Test.Department ALTER COLUMN DepartmentID SMALLINT NOT NULL;
ALTER TABLE Test.Department
ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC);
SELECT MAX(DepartmentID)
FROM Test.Department;
CREATE SEQUENCE Test.DeptSeq
AS SMALLINT
START WITH 4
INCREMENT BY 1;
GO
ALTER TABLE Test.Department
ADD CONSTRAINT DefSequence DEFAULT ( NEXT VALUE FOR Test.DeptSeq) FOR DepartmentID;
GO
SELECT DepartmentID,
Name,
GroupName
FROM Test.Department;
INSERT Test.Department (Name, GroupName)
VALUES ('Audit', 'Quality Assurance');
GO
SELECT DepartmentID,
Name,
GroupName
FROM Test.Department;
Salespeople engage with multiple customers every single day. They're responsible to qualify leads, nurture opportunities and to do so they need to be laser focused and act effectively in a timely manner. Sales organizations continuously invest in ongoing training and coaching so that salespeople are familiar with best practices and operate according to the business processes recommended in their organization. Sequences help sellers align to these processes by following a predefined set of consecutive action