2011年3月1日 星期二

SQL Server 2012(Code Name Denali) - 新T-SQL語法介紹 – Sequence

        上次介紹完SQL Server 2012 Denali的新功能(分頁功能)後，接下來我再介紹他的另一個功能，也就是Sequence，這個功能有點像是資料表欄位中的識別欄位，用來進行流水號的產生，但是以往流水號產生不能跨表格，也就是每個表格各有各的識別值，但是這樣一來如果N個表格需要產生唯一流水號時，只能透過程式的方式，將多個表格 union all 起來後，再找出最後一筆，如下列語法，實在不太方便：

select max(max_sn) mm_sn from
(
 select max(sn) max_sn from table_a
 union all
 select max(sn) max_sn from table_b
 union all
 select max(sn) max_sn from table_c
) aa

Sequence的使用上非常的簡單，當你開啟 [SQL Server Management Studio] 之後，在左邊的[Object Explorer]裡面的 Programmability -> Sequences 如下圖所示，當需要產生或移除Sequence也可以透過此區來執行即可(當然也可以透過T-SQL的方式來產生)，產生的方式如下圖：



 
建立完成後，使用的方式必須配合NEXT VALUE FOR SequenceName來產生識別值，使用的語法如下：

1、直接產生識別值：
select next value for SequenceName

2、將識別值直接寫入表格中：
insert into dbo.Employee
values(next value for SequenceName, 0, 'Cary', 'Hsu');

3、大量新增識別值與表格的值到另一個表格中，此範例主要是透過AdventureWorks2008R2的資料庫中Person的資料表，其中將Type為EM的全部新增到我新增的表格中：

3-1、建立表格：
CREATE TABLE [dbo].[Employee](
 [EMP_ID] [int] NOT NULL,
 [Person_ID] [int] NOT NULL,
 [First_Name] [nvarchar](20) NULL,
 [Last_Name] [nvarchar](20) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
 [EMP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

3-2、將符合的資料寫入到新增的[Employee]表格中：
insert into dbo.Employee
select next value for SequenceName,  BusinessEntityID, FirstName, LastName
from Person.Person
where PersonType = 'EM'

另外系統也另外提供一個Stored Procedure來進行跳號的功能，名稱為 [sp_sequence_get_range]，使用方法如下：
DECLARE
  @sequence_name nvarchar(100) = 'SequenceName',
  @range_size int = 'Jump Value',   --輸入你要跳號的數值  @range_first_value sql_variant,
  @range_last_value sql_variant,
  @range_cycle_count int,
  @sequence_increment sql_variant,
  @sequence_min_value sql_variant,
  @sequence_max_value sql_variant;
EXEC sp_sequence_get_range
  @sequence_name = @sequence_name,
  @range_size = @range_size,
  @range_first_value = @range_first_value OUTPUT,
  @range_last_value = @range_last_value OUTPUT,
  @range_cycle_count = @range_cycle_count OUTPUT,
  @sequence_increment = @sequence_increment OUTPUT,
  @sequence_min_value = @sequence_min_value OUTPUT,
  @sequence_max_value = @sequence_max_value OUTPUT;
SELECT
  @range_size AS [Range Size],
  @range_first_value AS [Sequence First Value],
  @range_last_value AS [Sequence Last Value],
  @range_cycle_count AS [Range Cycle Count],
  @sequence_increment AS [Sequence Increment],
  @sequence_min_value AS [Sequence Min Value],
  @sequence_max_value AS [Sequence Max Value];


 
最後如果你有需要重新設定識別值時候，你可以透過[Sequence Properties]的畫面來進行調整即可。

張貼者： caryhsu 於 下午3:43
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)