張小呆的碎碎唸

SQL SERVER 2012 的新功能不少，其中有一個 T-SQL 的功能就是 SEQUENCE(順序)，這個功能在 ORACLE 上是存在的功能。而 SQL 2012 能夠提供這個功能，對於筆者而言確實是一個福音。

為什麼這樣說呢!? 原因很簡單，那就是「取號」這個功能，在 ORACLE 中，常見以 SEQUENCE 來做為取號之用，而在 SQL 2012 以前，筆者大多都是用一個 取號檔資料表 來做為取號的紀錄，所以在控制上，就要特別的去留意處理，就深怕哪天發生取到相同號碼的危機，所以有時會特別地去用 FOR UPDATE 這等具有危險性的語法來管控，除了取號外，還需要自行去設計所謂的取號區間及循環等等記錄用的資料表，可謂是多工又耗時。

然而現在，有了這個功能後，對於日後的取號功能，確實可以省去許多的時間了，以下就來透過一些範例，來實作這好玩的功能吧!!

1.建立 SEQUENCE(順序)

介面：




語法：

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]

CREATE SEQUENCE dbo.MYSEQ
AS [INT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
CYCLE
CACHE;

2.修改 SEQUENCE(順序)

語法：

ALTER SEQUENCE [schema_name. ] sequence_name
[ RESTART [ WITH <constant> ] ]
[ INCREMENT BY <constant> ]
[ { MINVALUE <constant> } | { NO MINVALUE } ]
[ { MAXVALUE <constant> } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]

ALTER SEQUENCE dbo.MYSEQ
MAXVALUE 2000
NO CYCLE
NO CACHE;

結果：


3.刪除 SEQUENCE(順序)

語法：

DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ] sequence_name } [ ,...n ]
[ ; ]

DROP SEQUENCE dbo.MYSEQ;

4.使用在語法中

建立測試用資料表：
CREATE TABLE [dbo].[MY_TEST_TABLE](
[UID] [int] NOT NULL,
[UNAME] [nvarchar](50) NOT NULL,
[UPDTIME] [datetime] NOT NULL
) ON [PRIMARY]

--建立資料，以下語法為 SQL 2012 的新功能之一，新增多筆資料時可用
INSERT [dbo].[MY_TEST_TABLE]
VALUES(NEXT VALUE FOR DBO.MYSEQ, N'張大呆', GETDATE())
,(NEXT VALUE FOR DBO.MYSEQ, N'張二呆', GETDATE())
,(NEXT VALUE FOR DBO.MYSEQ, N'張小呆', GETDATE());

結果：


註：用於 SELECT 的方式，可參考「1.建立 SEQUENCE(順序)」的第四張圖片

用於在欄位的預設值中：

語法：
ALTER TABLE [dbo].[MY_TEST_TABLE] ADD CONSTRAINT [DF_MY_TEST_TABLE_UID] DEFAULT (NEXT VALUE FOR [dbo].[MYSEQ]) FOR [UID]

結果：


5.使用sp_sequence_get_range來取得 SEQUENCE 的區間

語法：

--以下程式碼來自於微軟MSDN範例
DECLARE
@FirstSeqNum sql_variant
, @LastSeqNum sql_variant
, @CycleCount int
, @SeqIncr sql_variant
, @SeqMinVal sql_variant
, @SeqMaxVal sql_variant ;

EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.MYSEQ'
, @range_size = 5
, @range_first_value = @FirstSeqNum OUTPUT
, @range_last_value = @LastSeqNum OUTPUT
, @range_cycle_count = @CycleCount OUTPUT
, @sequence_increment = @SeqIncr OUTPUT
, @sequence_min_value = @SeqMinVal OUTPUT
, @sequence_max_value = @SeqMaxVal OUTPUT ;

-- The following statement returns the output values
SELECT
@FirstSeqNum AS FirstVal
, @LastSeqNum AS LastVal
, @CycleCount AS CycleCount
, @SeqIncr AS SeqIncrement
, @SeqMinVal AS MinSeq
, @SeqMaxVal AS MaxSeq ;

結果：


6.透過程式來呼叫sp_sequence_get_range

畫面：


程式：

string strConnStr = @"***連線字串***";
using (SqlConnection sc = new SqlConnection(strConnStr))
{
sc.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = sc;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sys.sp_sequence_get_range";
cmd.Parameters.AddWithValue("@sequence_name", "dbo.MYSEQ");
cmd.Parameters.AddWithValue("@range_size", 5);

 

SqlParameter sp_FirstValue = new SqlParameter("@range_first_value", SqlDbType.Variant);
sp_FirstValue.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp_FirstValue);

 

SqlParameter sp_LastValue = new SqlParameter("@range_last_value", SqlDbType.Variant);
sp_LastValue.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp_LastValue);

 

cmd.ExecuteNonQuery();

 

label1.Text += sp_FirstValue.Value;
label2.Text += sp_LastValue.Value;
}
}

結果：


呆言呆語：一次取五個號碼，這樣可以再透過程式來做一些加工，對於慣用於取號檔資料表的筆者，這樣的功能，確實是很好用的...

7.使用 sys.sequences 來查詢 SEQUENCES 的相關資訊

語法：

SELECT *
FROM SYS.SEQUENCES;

結果：


參考：
Using SQL Server 2012 T-SQL New Features
SQL Server 2012 的 T-SQL 新功能 – SEQUENCE 物件
SQL Server 2012 - Sequence Object
Sequence Numbers
sp_sequence_get_range (Transact-SQL)
CREATE SEQUENCE (Transact-SQL)
ALTER SEQUENCE (Transact-SQL)
DROP SEQUENCE (Transact-SQL)
NEXT VALUE FOR (Transact-SQL)
順序屬性 (一般頁面)
sys.sequences (Transact-SQL)