# 序號Sequence Numbers

## 使用順序Using Sequences

• 進行插入資料表的作業之前，應用程式需要一個編號。The application requires a number before the insert into the table is made.

• 應用程式需要在多個資料表或資料表中多個資料行之間共用單一編號序列。The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.

• 達到指定的編號時，應用程式必須重新啟動編號序列。The application must restart the number series when a specified number is reached. 例如，指派 1 到 10 的值之後，應用程式就會再次開始指派 1 到 10 的值。For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.

• 應用程式要求依照另一個欄位排序順序值。The application requires sequence values to be sorted by another field. NEXT VALUE FOR 函數可以將 OVER 子句套用至函數呼叫。The NEXT VALUE FOR function can apply the OVER clause to the function call. OVER 子句會確保傳回的值都按照 OVER 子句之 ORDER BY 子句的順序來產生。The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.

• 應用程式要求同時指派多個編號。An application requires multiple numbers to be assigned at the same time. 例如，應用程式需要保留五個序號。For example, an application needs to reserve five sequential numbers. 如果同時針對其他處理序發出編號，要求識別值可能會在序列中產生間距。Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. 呼叫 sp_sequence_get_range 可以一次擷取順序中的許多編號。Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.

• 您必須變更順序的規格，例如遞增值。You need to change the specification of the sequence, such as the increment value.

## 一般用法Typical Use

``````CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;
``````

``````CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;
``````

## 範例Examples

There are additional examples in the topics CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL), and sp_sequence_get_range.

### A.A.在單一資料表中使用序號Using a sequence number in a single table

``````--Create the Test schema
CREATE SCHEMA Test ;
GO

-- Create a table
CREATE TABLE Test.Orders
(OrderID int PRIMARY KEY,
Name varchar(20) NOT NULL,
Qty int NOT NULL);
GO

-- Create a sequence
CREATE SEQUENCE Test.CountBy1
INCREMENT BY 1 ;
GO

-- Insert three records
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

-- View the table
SELECT * FROM Test.Orders ;
GO
``````

`OrderID Name Qty`

`1 Tire 2`

`2 Seat 1`

`3 Brake 1`

### B.B.在插入資料列之前呼叫 NEXT VALUE FORCalling NEXT VALUE FOR before inserting a row

``````DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Test.CountBy1;
-- Some work happens
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (@NextID, 'Rim', 2) ;
GO
``````

### C.C.在多個資料表中使用序號Using a sequence number in multiple tables

``````CREATE SCHEMA Audit ;
GO
CREATE SEQUENCE Audit.EventCounter
AS int
INCREMENT BY 1 ;
GO

CREATE TABLE Audit.ProcessEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EventCode nvarchar(5) NOT NULL,
Description nvarchar(300) NULL
) ;
GO

CREATE TABLE Audit.ErrorEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EquipmentID int NULL,
ErrorNumber int NOT NULL,
EventDesc nvarchar(256) NULL
) ;
GO

CREATE TABLE Audit.StartStopEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
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 threashold 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.') ;
-- The following statement combines all events, though not all fields.
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 ;
GO
``````

`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.`

`4 2009-11-02 15:00:51.167 Spin rate threshold exceeded.`

`5 2009-11-02 15:00:51.173 Feeder jam`

`6 2009-11-02 15:00:51.177 Stop`

`7 2009-11-02 15:00:51.180 Central feed in bypass mode.`

### D.D.在結果集中產生重複的序號Generating repeating sequence numbers in a result set

``````CREATE SEQUENCE CountBy5
AS tinyint
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE ;
GO

SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;
GO
``````

### E.E.使用 OVER 子句來產生結果集的序號Generating sequence numbers for a result set by using the OVER clause

``````USE AdventureWorks2012 ;
GO

CREATE SCHEMA Samples ;
GO

CREATE SEQUENCE Samples.IDLabel
AS tinyint
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%' ;
``````

### F.F.重設序號Resetting the sequence number

``````SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
``````

``````ALTER SEQUENCE Samples.IDLabel
``````

``````SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
``````

### G.G.將資料表從識別變更為順序Changing a table from identity to sequence

``````-- Create a schema
CREATE SCHEMA Test ;
GO

-- Create a table
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 three rows into the table
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

-- View the table that will be changed
SELECT * FROM Test.Department ;
GO

-- End of portion creating a sample table
--------------------------------------------------------
-- Add the new column that does not have the IDENTITY property
ALTER TABLE Test.Department
GO

-- Copy values from the old column to the new column
UPDATE Test.Department
SET DepartmentIDNew = DepartmentID ;
GO

-- Drop the primary key constraint on the old column
ALTER TABLE Test.Department
DROP CONSTRAINT [PK_Department_DepartmentID];
-- Drop the old column
ALTER TABLE Test.Department
DROP COLUMN DepartmentID ;
GO

-- Rename the new column to the old columns name
EXEC sp_rename 'Test.Department.DepartmentIDNew',
'DepartmentID', 'COLUMN';
GO

-- Change the new column to NOT NULL
ALTER TABLE Test.Department
ALTER COLUMN DepartmentID smallint NOT NULL ;
-- Add the unique primary key constraint
ALTER TABLE Test.Department
ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED
(DepartmentID ASC) ;
-- Get the highest current value from the DepartmentID column
-- and create a sequence to use with the column. (Returns 3.)
SELECT MAX(DepartmentID) FROM Test.Department ;
-- Use the next desired value (4) as the START WITH VALUE;
CREATE SEQUENCE Test.DeptSeq
AS smallint
INCREMENT BY 1 ;
GO

-- Add a default value for the DepartmentID column
ALTER TABLE Test.Department
ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)
FOR DepartmentID;
GO

-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
-- Test insert
INSERT Test.Department (Name, GroupName)
VALUES ('Audit', 'Quality Assurance') ;
GO

-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
GO
``````