65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL 11 (代号 Denali) 入门 - 第二部分 (CTP 1 中的 T-Sql 功能)

Niladri_Biswas
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.90/5 (27投票s)

2011年2月27日

CPOL

29分钟阅读

viewsIcon

49214

本文将从 T-Sql 的角度探讨 Denali 为我们提供的新功能。

SQL 11 (代号 Denali) 入门 - 第二部分 (CTP 1 中的 T-Sql 功能)

目录

  1. 引言
  2. 背景
  3. TSql 新功能和增强功能
    1. 带结果集 (新功能)
    2. Throw 语句 (新功能)
    3. Offset 和 Fetch First/Next 子句 – Order by 子句的扩展 (增强功能)
    4. Sequence 对象 (新功能)
  4. 结论

引言

微软在技术领域最热门、最令人惊叹的开发之一于2010年11月8日发布了Sql Server 2011 (代号 Denali)的社区技术预览版 1 (CTP 1) 时浮出水面。CTP1 提供32位和64位版本。正如预期的那样，Denali 为 Sql 爱好者，无论是开发人员、管理员还是商业智能 (BI) 专业人士，都带来了一些新功能。在本系列文章中，我们将探讨Tsql 的增强和新功能。在第一部分中，我们已经看到了一些 SSMS 的增强和新功能。其余功能将在后续系列中介绍。

背景

在过去的几年里，微软为开发者带来了许多新技术。随着 Sql Server 2005 (代号Yukon) 的问世以及 Sql Server 2008 (代号Katmai) 和 Sql Server 2011 (代号Denali) 等后续版本的发布，Sql Server 的术语发生了巨大变化，在引入新功能、增强功能和改进方面保持了同样的步伐。在本文中，我们将从 TSql 的角度深入探讨 Denali 已经为我们提供的新功能。后续文章将侧重于管理员和 BI 领域的增强功能。

TSql 新功能和增强功能

一如既往，Denali 没有让 TSql 开发者失望。它带来了一些真正出色的功能，以减轻开发者的开发负担。

在下一节中，我们将探讨这些功能。

一、带结果集

Denali 的 With Result Sets 功能允许我们更改存储过程返回的结果集的列名和数据类型。

在深入探讨之前，让我们先回顾一下在早期版本的 Sql Server (Denali 之前) 中，我们如何从正在执行的存储过程中获取值。

为了演示，我们将使用下面的表 (tbl_Test) 作为示例，该表有3列。

Denali_Tsql_Part_2/1.jpg

现在，让我们使用下面的脚本向表中填充一些记录 (例如 1000 条)。

-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Test' AND type = 'U')
    DROP TABLE tbl_Test
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[tbl_Test](
	[Id] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[PhoneNumber] [int] NOT NULL
) ON [PRIMARY]
GO

--Populate the Cte with some records
;With Cte(Id,Name,PhoneNo) As (
Select 
	Id = 1
	,Name='Name' + CAST( 1 As Varchar(50))
	, PhoneNo=12345678
Union All 
Select 
	Id+1
	,Name= 'Name' + CAST( Id+1 As Varchar(50)) 
	, PhoneNo+1
From Cte
Where Id <1000
)
--Insert the records into the table
Insert Into dbo.tbl_test
Select * From Cte
Option( Maxrecursion 0)

--Display the records
Select * 
From tbl_Test

运行脚本将得到以下记录集 (部分)。

Id	Name	PhoneNumber
1	Name1	12345678
2	Name2	12345679
3	Name3	12345680
4	Name4	12345681
5	Name5	12345682

让我们编写一个存储过程来从表 tbl_Test 获取结果。

CREATE PROCEDURE dbo.Usp_FetchRecords
AS
BEGIN 

       Select 
			Id
			,Name
			,PhoneNumber
		From dbo.tbl_Test
END

为了从正在执行的存储过程中获取结果集，有多种可用方法，正如 Erland Sommarskog 在他的文章中所讨论的那样。然而，我们将只介绍其中一种方法。

临时表方法

--If the #Temp object exists in the tempdb, then drop it
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
Drop Table #Temp 
END

--Create a temporary table
CREATE TABLE #Temp 
(
	Id int, 
	EmpName Varchar(50),
	PhoneNo int 
) 
--Insert records into the Temporary table from the executed stored proc
INSERT INTO #Temp 
( 
	Id
	,EmpName
	,PhoneNo
)
EXEC dbo.Usp_FetchRecords

--Display the records inserted into the temporary table
Select * from #Temp

如果我们提前知道存储过程返回的列及其数据类型，则上述方法是可行的。

Denali 之前方法的缺点是：

  1. 没有一种方法是直接的。也就是说，我们需要借助临时表或变量，并在操作完成后将其丢弃；否则，它将占用不必要的数据库空间。
  2. 过程冗长
  3. 在 Open Row Set 或 Open query 的情况下，我们需要开启“Ad Hoc Distributed Queries”功能，然后才能继续。
  4. 在临时表或表变量方法的情况下，我们需要提前知道列的数据类型。

Denali (Sql Server 2011) 的 With Result Set 方法

Denali 的 With Result Set 克服了上述缺点。让我们看看它是如何实现的。让我们执行以下查询 (用于单个结果集)。

EXEC Usp_FetchRecords 

WITH RESULT SETS

(
       (      [Emp Id] int,

              [Emp Name] varchar(50),

              [Phone Number] varchar(50)

       )  

)

输出为 (部分输出)。

Emp Id	Emp Name	Phone Number
1	Name1		12345678
2	Name2		12345679
3	Name3		12345680
4	Name4		12345681
5	Name5		12345682

With Result Set 的一般语法是：

WITH RESULT SETS

(
       (      
             Column Name1 DataType [Size]
            , Column Name2 DataType [Size]
            ,  . .  . .  .  . .  . . . . .
            ,  . . . . . . . . . . . . . . 
            , Column Name-n DataType [Size]

       )  

      ,

       (      
             Column Name1 DataType [Size]
            , Column Name2 DataType [Size]
            ,  . .  . .  .  . .  . . . . .
            ,  . . . . . . . . . . . . . . 
            , Column Name-n DataType [Size]

       )  
       . . . . . . . . . . . . . . . . . .
       . .  . . . . . . . . . . . . . . . 
       ,

       (      
             Column Name1 DataType [Size]
            , Column Name2 DataType [Size]
            ,  . .  . .  .  . .  . . . . .
            ,  . . . . . . . . . . . . . . 
            , Column Name-n DataType [Size]

       )      
)

因此，我们可以得出结论，无论存储过程返回的结果集的列名是什么，我们都可以更改列名及其数据类型，只要数据类型转换与原始结果集兼容 (即与表中架构定义的数据类型兼容)。否则，数据库引擎将报告错误。

例如，在下面的示例中，我们将 Name 列的数据类型 (originally as varchar(50)) 更改为 int。

EXEC Usp_FetchRecords
WITH RESULT SETS

(
       (      [Emp Id] int,

              [Emp Name] int, -- Changed to int data type

              [Phone Number] varchar(50)

       )  

)

执行后，我们将收到以下错误消息：

Msg 8114, Level 16, State 2, Procedure Usp_FetchRecords, Line 5 Error converting data type varchar to int。

然而，将其更改为Text (例如) 数据类型则可以正常工作。

上述查询是为了演示如何使用 Execute 的 With Result Set 转换单个结果集。但是，它可以扩展到转换多个结果集。让我们看看如何实现。

考虑以下存储过程，它返回两个不同的记录集。

CREATE PROCEDURE [dbo].[Usp_ModifiedFetchRecords]
AS
BEGIN 

       Select 
			Id
			,Name
			,PhoneNumber
		From dbo.tbl_Test;

		Select
			Id
			,Name
		From dbo.tbl_Test
		Where PhoneNumber % 2 = 0
END

第二个 select 语句生成电话号码数字为偶数的姓名记录。

执行存储过程后的部分输出如下：

Denali_Tsql_Part_2/2.jpg

现在，让我们执行以下查询：

EXEC Usp_ModifiedFetchRecords 

WITH RESULT SETS

(
       (      [Emp Id From First Result Set] int,

              [Emp Name From First Result Set] varchar(50),

              [Phone Number From First Result Set] varchar(50)

       )  ,

	    (      [Emp Id From Second Result Set] int,

              [Emp Name From Second Result Set] varchar(50)             

       ) 

)

部分输出为：

Denali_Tsql_Part_2/3.jpg

但是，由于存储过程返回两个记录集 (本例中)，如果我们尝试在 With Result Sets 子句中只获取一个记录集，引擎将报告以下错误：

Msg 11535, Level 16, State 1, Procedure Usp_ModifiedFetchRecords, Line 11 EXECUTE 语句失败，因为其 WITH RESULT SETS 子句指定了 1 个结果集，而该语句尝试发送的结果集多于此数量。

可以看出，Execute 命令的 With Result Set 子句基本上是对存储过程的结果集进行转换。它消除了上述共享存储过程数据的过程的缺点。

如何从 With Result Set 获取值？

我们有时需要从 With Result Set 子句中获取值。在这种情况下，我们可以采用临时表或表变量方法。

在这里，我们将介绍表变量方法。

Declare @tblStoreWithResultSetsData Table
([Employee Id] int
, [Employee Name] varchar(50)
,[Emp Phone No] int)

insert into @tblStoreWithResultSetsData

EXEC Usp_FetchRecords

WITH RESULT SETS

(
       (      [Emp Id] int,

              [Emp Name] varchar(6), -- as a proof of concept, 
					  --  change the dataType size to 6. 
					  --	Records will be truncated

              [Phone Number] varchar(50)

       )  

) 
Select * From @tblStoreWithResultSetsData

输出符合预期 (显示最后 10 条记录)。

Denali_Tsql_Part_2/4.jpg

适用性

  1. 数据转换在 SSIS 中将变得更简单，正如这篇文章中所详细描述的那样。
  2. 更改数据类型而不更改架构。假设一个 dotnet 应用程序需要一个布尔类型，而底层架构为该列设计为 int 类型。理想情况下，我们会在运行时进行转换，例如 Case When <condition> Then 1 Else 0。而不是这样做，我们可以直接将数据类型更改为 bit。
  3. 另一个例子是，假设 dotnet 应用程序需要一个 int，但列类型是 float。
  4. 另一种用法可能是，假设架构已更改，而 DAL 层对此不知情。也许同一个存储过程从多个地方调用。在这种情况下，我们可以在运行时更改 With Result Set 中的列名，这样表架构和 DAL 逻辑将保持不变。

缺点

我们不能返回选定的列。列的数量必须与结果集中的数量相同。例如，如果我们这样写：

EXEC Usp_FetchRecords 

WITH RESULT SETS

(
       (      [Emp Id] int,              

              [Phone Number] varchar(50)

       )  

)

引擎将报告以下错误：

Msg 11537, Level 16, State 1, Procedure Usp_FetchRecords, Line 5 EXECUTE 语句失败，因为其 WITH RESULT SETS 子句为结果集编号 1 指定了 2 列，但该语句在运行时发送了 3 列。

二、Throw 语句

Sql Server 2011 (Denali) 的一项新功能是 Throw 语句。它与 Try...Catch 块结合使用，用于通知运行时异常的发生。当抛出异常时，程序会查找处理该异常的 catch 语句。通过在 catch 块中使用此语句，我们可以更改结果异常。此外，我们几乎可以在程序的任何地方抛出一个新异常。

在本文中，我们将查看 Sql Server 从 2000 年开始支持的各种异常。

我们还将考虑下面的表 (tbl_ExceptionTest) 用于我们将要查看的所有异常情况。

Denali_Tsql_Part_2/5.jpg

通过执行以下 DDL 来创建表。

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'tbl_ExceptionTest' AND type = 'U')
    DROP TABLE tbl_ExceptionTest
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_ExceptionTest](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Phone Number] [int] NOT NULL,
 CONSTRAINT [PK_tbl_ExceptionTest] PRIMARY KEY CLUSTERED 
(
	[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]

GO

目的是在运行时向表中插入一些记录，并在插入到 Phone Number 列时，插入一些无效数据，从而产生一些异常。

Sql Server 2000 (代号：Sphinx) 中的异常处理

1.使用 @@ERROR 全局系统变量

早在 Sphinx (Sql Server 2000 的代号) 时期，我们就有了 @@Error 系统变量，它被认为是 T-Sql 开发人员最有效的错误处理工具。它的职责是返回最后执行的 T-Sql 语句的错误号，其类型为 int。当发生错误时，@@ERROR 变量会存储相应的错误号。错误号可以是正数、负数或 0 (表示成功)。@@ERROR 的值会随着每个语句的执行而改变。

让我们看看 @@Error 系统变量的实际应用。

--If the #tblExceptionTest object exists in the tempdb, then drop it
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest 
End

--Create the #tblExceptionTest temporary table
Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)

--Beigns the transaction
Begin Transaction TranExcp__2000_@@Error

	--Variable Declarations
   Declare @ErrorNum int  -- a local variable to store the @@ERROR value  
   Declare @i int -- a local variable that acts as a counter

   --Initialize variables
   Set @i =1

   --Start Operation
   While(@i <= 4)
	Begin
		-- Simulating the situation where a user tries to enter a null value to the Phone Number column
		If(@i = 4)
			Begin
				Insert into #tblExceptionTest([Phone Number]) Values(null)
				Set @ErrorNum = @@ERROR
			End
		Else
			-- All records will be inserted successfully
			Begin
				Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')
			End
		Set @i = @i +1

	End -- End of while

	-- If there is any error, notify that and roll back the transaction
   If @ErrorNum <> 0 
	   Begin 
		  Rollback Transaction TranExcp__2000_@@Error
		  --Raise the custom error
		  RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1)       
	   End
   -- Commit the changes
   Else If @ErrorNum = 0 
		Begin
			Commit Transaction TranExcp__2000_@@Error
		End
   --Display the records
   Select * from #tblExceptionTest

执行程序会产生以下错误消息：

Msg 515, Level 16, State 2, Line 26 Cannot insert the value NULL into column 'Phone Number', table 'tempdb.dbo.#tblExceptionTest_____000000000023'; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 50000, Level 16, State 1, Line 43 Attempt to insert null value in [Phone Number] is not allowed

并且“结果”选项卡如预期所示，所有记录都已回滚。

@Error 方法的缺点

1) 必须在语句执行后立即检查 @@Error。

2) 由于 @@Error 值会随着代码中每个语句的执行而改变，因此我们需要使用局部变量来存储 @@error 值并在需要时使用它。

3) 除了自定义错误，还会出现系统定义的错误。

有关 @@Error 全局系统变量的更多信息，请访问 @@Error。

2.使用 @@TRANCOUNT 全局系统变量

它返回当前连接的活动事务数量。与 @@ERROR 系统变量一样，此变量的值也会随着每个语句的执行而改变。因此，我们应该使用局部变量来存储 @@TRANCOUNT 值并在需要时使用它。每个 BEGIN TRANSACTION 都会使 @@TRANCOUNT 增加 1，每个 COMMIT TRANSACTION 会使其值减 1，而 ROLLBACK TRANSACTION 会将 @@TRANCOUNT 减至 0。只有当 @@TRANCOUNT 达到 0 时，记录才会被提交。

让我们在同一场景下看看 @@Trancount 系统变量的实际应用。

--If the #tblExceptionTest object exists in the tempdb, then drop it
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest 
End

--Create the #tblExceptionTest temporary table
Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)

--Beigns the transaction
Begin Transaction TranExcp__2000_@@TRANCOUNT

	--Variable Declarations
   Declare @TransactionCount int  -- a local variable to store the @@TRANCOUNT value  
   Declare @i int -- a local variable that acts as a counter

   --Initialize variables
   Set @i =1

   --Start Operation
   While(@i <= 4)
	Begin
		-- Simulating the situation where a user tries to enter a null value to the Phone Number column
		If(@i = 4)
			Begin
				Insert into #tblExceptionTest([Phone Number]) Values(null)
				Set @TransactionCount = @@TRANCOUNT
			End
		Else
			-- All records will be inserted successfully
			Begin
				Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')
			End
		Set @i = @i +1

	End -- End of while

	-- If there is any error, notify that and roll back the transaction	
   If @TransactionCount <> 0 
	   Begin 
		  Rollback Transaction TranExcp__2000_@@TRANCOUNT
		   --Raise the custom error
		  RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1)       
	   End
   -- Commit the changes
   Else If @TransactionCount = 0 
		Begin
			Commit Transaction TranExcp__2000_@@TRANCOUNT			
		End
   --Display the records
   Select * from #tblExceptionTest

有关 @@TRANCOUNT 全局系统变量的更多信息，请访问 @@TRANCOUNT。

3.使用 @@ROWCOUNT 全局系统变量

它返回最近执行的语句所影响的行数。由于其值会随着每次语句执行而改变，因此最好将其存储在某个局部变量中以便以后使用。

让我们在同一场景下看看 @@ROWCOUNT 变量的实际应用。

--If the #tblExceptionTest object exists in the tempdb, then drop it
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest 
End

--Create the #tblExceptionTest temporary table
Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)

--Beigns the transaction
Begin Transaction TranExcp__2000_@@ROWCOUNT
--Create a Save Point
Save Transaction TranExcp__SavePoint

	--Variable Declarations
   Declare @RowCount int  -- a local variable to store the @@ROWCOUNT value  
   Declare @i int -- a local variable that acts as a counter

   --Initialize variables
   Set @i =1

   --Start Operation
   While(@i <= 4)
	Begin
		-- Simulating the situation where a user tries to enter a null value to the Phone Number column
		If(@i = 4)
			Begin
				Insert into #tblExceptionTest([Phone Number]) Values(null)				
				Set @RowCount = @@ROWCOUNT
			End
		Else
			-- All records will be inserted successfully
			Begin
				Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')				
			End
		Set @i = @i +1

	End -- End of while
	
	-- If there is any error, notify that and roll back the transaction	
   If @RowCount = 0 
	   Begin 
		  --Roll the transaction back to the most recent save transaction 
		  Rollback Transaction TranExcp__SavePoint
		   --Raise the custom error
		  RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1)       
	   End
   -- Commit the changes
   Else If @RowCount <> 0 
		Begin
			Commit Transaction TranExcp__2000_@@ROWCOUNT			
		End
   --Display the records
   Select * from #tblExceptionTest

有关 @@ROWCOUNT 全局系统变量的更多信息，请访问 @@ROWCOUNT。

Sql Server 2005/2008 (代号：Yukon 和 Katmai) 中的异常处理

自从 Sql Server 2005 (代号 Yukon) 问世以来，一直延续到 Sql Server 2008 (代号 Katmai)，我们有了 Try...Catch 块。现在我们可以使用 TRY/CATCH 模型捕获事务中止错误，而不会丢失任何事务上下文。

让我们在同一场景下看看 TRY...CATCH 块的实际应用。

--If the #tblExceptionTest object exists in the tempdb, then drop it
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest 
End

Begin TRY
   --Create the #tblExceptionTest temporary table
	Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)

    Begin Transaction TranExcpHandlingTest_2005_2008
	   --Variable Declarations
       Declare @i int  -- a local variable that acts as a counter
	  
	   --Initialize variables  
       Set @i =1

       --Start Operation
       While(@i <= 4)
        Begin
			-- Simulating the situation where a user tries to enter a null value to the Phone Number column
            If(@i = 4)
                Begin
                    Insert into #tblExceptionTest([Phone Number]) Values(null)
                End
            Else
                -- All records will be inserted successfully
                Begin
                    Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')
                End
            Set @i = @i +1

        End -- End of while

		--If everything goes smooth, then commit the transaction
        Commit Transaction TranExcpHandlingTest_2005_2008		 
       
End Try
Begin Catch
	  --Handle the error
       Begin 
		  --Rollback the transaction
          Rollback Transaction TranExcpHandlingTest_2005_2008
		  --Raise the custom error
          RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) 
       End
End Catch

--Display the records
Select * From #tblExceptionTest

执行后，我们将收到以下内容：

Msg 50000, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed

可以看出，这次我们可以生成 RaiseError 函数中定义的自定义消息。Try...Catch 块看起来不错，并且使代码看起来整洁。逻辑的有效操作部分进入 TRY 块，错误处理部分保留在 Catch 块中。如果 Try 块中的代码块出现异常，控制将转到 Catch 块，回滚事务，然后程序其余部分继续执行。如果 Try 块内的所有语句都顺利运行，那么控制将永远不会进入 Catch 块，而是执行 End Catch 语句之后的第一个语句。此外，catch 块在异常情况下提供了足够的信息，应该捕获这些信息以获取有关程序失败的适当信息，例如：

  1. ERROR_NUMBER
  2. ERROR_SEVERITY
  3. ERROR_STATE
  4. ERROR_LINE
  5. ERROR_PROCEDURE
  6. ERROR_MESSAGE

因此，在上面的程序中，如果我们像下面这样修改 CATCH 块：

Begin Catch
	  --Handle the error
       Begin 
		  --Rollback the transaction
          Rollback Transaction TranExcpHandlingTest_2005_2008		 
		  SELECT
				ERROR_NUMBER() AS ErrorNumber,
				ERROR_SEVERITY() AS ErrorSeverity,
				ERROR_STATE() AS ErrorState,
				ERROR_PROCEDURE() AS ErrorProcedure,
				ERROR_LINE() AS ErrorLine,
				ERROR_MESSAGE() AS ErrorMessage;
       End
End Catch

我们将收到以下内容：

Denali_Tsql_Part_2/6.jpg

RaiseError 函数的缺点

1) 如果您还记得，在使用 Catch 块中的 RaiseError 函数时，报告的错误是第 45 行。

Denali_Tsql_Part_2/7.jpg

但它实际上是在第 24 行生成的，我们在那里写了 Insert into #tblExceptionTest([Phone Number]) Values(null)。

然而，ERROR_LINE() 函数确实报告了错误实际发生在哪一行。作为另一种概念证明，让我们像下面这样修改 CATCH 块：

Begin Catch
	  --Handle the error
       Begin 
		  --Rollback the transaction
          Rollback Transaction TranExcpHandlingTest_2005_2008
	   DECLARE @errNumber INT = ERROR_NUMBER()
	   DECLARE @errMessage VARCHAR(500) = 'Attempt to insert null value in [Phone Number] is not allowed'
         --Raise the custom error          
	  RAISERROR('Error Number: %d, Message: %s', 16, 1, @errNumber, @errMessage)
       End
End Catch

在这种情况下，引擎会给出以下报告：

Denali_Tsql_Part_2/8.jpg

所以我们可以得出结论，通过使用 RaiseError，我们会丢失原始的错误行号。

2) 另一个缺点是，我们不能重新抛出相同的错误。所以如果我们写了下面的内容在 CATCH 块中：

Begin Catch
	  --Handle the error
       Begin 
		  --Rollback the transaction
          Rollback Transaction TranExcpHandlingTest_2005_2008		  
		  --Raise the custom error
		  RAISERROR(515, 16, 1)
       End
End Catch

我们将从引擎那里收到以下错误消息：

Msg 2732, Level 16, State 1, Line 46 Error number 515 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000

原因是，为了让 RaiseError 抛出一个错误，消息编号必须存在于 sys.messages 表中。

有关 RaiseError 的更多信息，请访问：

  1. 深入了解 RaiseError - Sql Serevr 2005
  2. 使用 RaiseError

Sql Server 2011 (代号：Denali) 中的异常处理

Denali 的新 Throw 命令克服了 RaiseError 的上述缺点。让我们看看它是如何实现的。

Raise Error 的第一个缺点是我们遇到的，它没有保留原始的错误行号。让我们看看在使用 Throw 命令时事实的准确性。

使用 Throw 重写上述 T-Sql 代码的 Catch 块。

Begin Catch
	  --Handle the error
       Begin 
		  --Rollback the transaction
          Rollback Transaction TranExcpHandlingTest_2011;
		  --Throw the error
          THROW 
       End
End Catch

产生以下输出：

Denali_Tsql_Part_2/9.jpg

这倒是正确的，因此证明了我们的陈述。

第二个缺点是，使用 RaiseError 我们不能重新抛出相同的错误，因为 RAISE ERROR 期望编号存储在 sys.messages 中。Throw 不需要错误编号存在于 sys.messages 表中，尽管错误编号应该在 50000 到 2147483647 之间 (包括两者)。

作为本次练习的一部分，让我们将 Catch 块更改为如下：

Begin Catch
	  --Handle the error
       Begin 
		  --Rollback the transaction
          Rollback Transaction TranExcpHandlingTest_2011;
		  --Throw the error
          THROW 50001,'Attempt to insert null value in [Phone Number] is not allowed',1
       End
End Catch

我们得到以下结果：

Msg 50001, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed

尽管现在有许多方法可以在 Sql Server 中处理异常，但并非所有出现的错误都能被 Try..Catch 构造捕获。例如：

a) 语法错误会被 SSMS 的查询编辑器解析器捕获。

b) 无效的对象名称。

例如，如果我们执行以下操作：

Begin Try
	 -- --Invalid object tblInvalid	 
     Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26'))
End Try

Begin Catch		  
	 --Throw the error
     THROW 
End Catch

并尝试执行，我们将收到以下错误：

Msg 208, Level 16, State 0, Line 3 Invalid object name 'tblInvalid’.

所以我们可以看出，几乎不可能捕获这类错误。

但有一种巧妙的方法可以做到。其思想是创建两个存储过程，在一个存储过程的 Try..Catch 块内调用另一个存储过程，并捕获异常。作为上述陈述的证明，我们将以上述场景为例并进行实验。

--Check if the stored procedure exists. If so drop it
If Exists (Select * from sys.objects where name = 'usp_InternalStoredProc' and type = 'P')
    Drop Procedure usp_InternalStoredProc
Go
-- Create the internal stored procedure
Create Procedure usp_InternalStoredProc
As
Begin
    Begin Transaction TranExcpHandlingTest_2011
       Begin Try	   			
			--Invalid object tblInvalid
			Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26'))
			--Commits the transaction
			Commit Transaction TranExcpHandlingTest_2011
		End Try
       Begin Catch           
		  If @@TRANCOUNT > 0 Rollback Transaction TranExcpHandlingTest_2011
          Print 'In catch block of internal stored procedure.... throwing the exception';  
          -- Throw the exception
          THROW
       End Catch
End
Go

-- Script for creating the External stored procedure
--Check if the stored procedure exists. If so drop it
If Exists (Select * from sys.objects where name = 'usp_ExternalStoredProc' and type = 'P')
    Drop Procedure usp_ExternalStoredProc
Go
-- Create the external stored procedure
Create Procedure usp_ExternalStoredProc
As
Begin
    Begin Try
       --Call the internal stored procedure
        Exec usp_InternalStoredProc
    End Try
    Begin Catch
        Print 'In catch block of external stored procedure.... throwing the exception'; 
		SELECT ERROR_NUMBER() AS ErrorNumber
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_PROCEDURE() AS ErrorProcedure
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_MESSAGE() AS ErrorMessage;       
        THROW
    End Catch
End
Go
--Executing the outer procedure
Exec usp_ExternalStoredProc

结果如下：

In catch block of external stored procedure.... throwing the exception
(1 row(s) affected)
 
Msg 208, Level 16, State 1, Procedure usp_InternalStoredProc, Line 8
Invalid object name 'tblInvalid'.

结果窗格显示如下：

Denali_Tsql_Part_2/10.jpg

代码解释

我们有两个存储过程，分别是 usp_InternalStoredProc 和 usp_ExternalStoredProc。在 usp_InternalStoredProcedure 中，我们试图将记录插入到不存在的表 (#tblInnerTempTable) 中。从 usp_ExternalStoredProcedure 中，我们调用内部过程，并在外部过程的 catch 块中捕获异常。

此外，错误行 (这里是 8) 也是正确的。

最后但同样重要的是，我们需要通过在 THROW 前放置分号来终止前一个批处理，因为 THROW 命令应该作为新的批处理发出，否则我们将收到以下错误：

Incorrect syntax near 'THROW'。

有关 Throw 语句的更多信息，请访问 Throw。

三、Offset 和 Fetch First/Next 子句 – Order by 子句的扩展

在 Denali 中，order by 子句通过添加两个子句得到了丰富：

a) Offset

b) Fetch First 或 Fetch Next

偏移量

此关键字用于在检索投影行之前跳过行数。该语句的意思是，假设我们有 100 条记录，我们想跳过前 10 条记录。在这种情况下，如果我们发出类似以下内容：

Select *
From  <SomeTable>
Order by  <SomeColumn>
Offset 10 Rows

它将生成预期的记录集。

如果有人熟悉 .NET 框架 3.0 及更高版本，我们有 Skip 扩展方法，它会跳过集合中指定的元素并访问其余元素。Offset 子句类似于 .NET 框架 3.0 中添加的 Skip 扩展方法。一旦记录集按照 Order by 子句和指定的列排序，Offset 子句就会被评估。

可以使用 Offset 子句的情况

在本文的其余部分，我们将使用以下记录集。

-- Declare a table variable
Declare @tblSample Table 
(	
	[Person Name] Varchar(50)
	,Age int
	,Address Varchar(100) 
)

-- Populate some data to the table
Insert into @tblSample  
Select 
		'Person Name' + CAST(Number AS VARCHAR)
		, Number
		, 'Address' + CAST(Number AS VARCHAR)
From master..spt_values 
Where Type = 'p' 
And Number Between 1 and 50

情况 1) 跳过前 10 条记录并显示其余记录

让我们发出以下查询：

-- Fetch the records from 11 to 50
Select *
From @tblSample 
Order by Age  
Offset 10 Row -- or even Offset 10 Rows 

OR

-- Fetch the records from 11 to 50
Select *
From @tblSample 
Order by Age  
Offset (10) Rows -- or even Offset 10 Row

输出 (部分) 将是：

Person Name	Age	Address
Person Name11	11	Address11
Person Name12	12	Address12
 . . . . .  . . . . . . . . .  
 . . . . . .. . . . . . . . .
Person Name49	49	Address49
Person Name50	50	Address50

我们可以指定 Row 或 Rows，因为它们是同义词。

情况 2) 我们也可以在变量中指定要跳过的行数，如下所示：

-- Variable to hold the offset value
Declare @RowSkip As int
--Set the value of rows to skip
 Set @RowSkip = 10

-- Fetch the records from 11 to 50
Select *
From @tblSample 
Order by Age  
Offset @RowSkip Row -- or even Offset 10 Rows

情况 3) 我们可以在 Offset 子句内指定任何有效的 TSql 表达式：

-- Fetch the records from 14 to 50
Select *
From @tblSample 
Order by Age  
Offset (select MAX(number)/99999999 from  master..spt_values) Rows

select MAX(number)/99999999 from master..spt_values 将返回 14。因此，记录将跳过前 14 行并显示其余行。

情况 4) 我们甚至可以在 Offset 中指定用户定义函数，如下所示：

-- Fetch the records from 11 to 50
Select *
From @tblSample 
Order by Age  
Offset (select dbo.fn_test()) Rows -- or even Offset 10 Row

其中用户定义的标量函数定义如下：

CREATE FUNCTION fn_test()
RETURNS int
AS
BEGIN
	-- Declare the return variable
	Declare @ResultVar as int
	-- Enter some value to the return variable
	Select @ResultVar = 10
	-- Return the result of the function
	RETURN @ResultVar
END
GO

情况 5) 我们可以将 Offset 子句与视图、内联函数 (如上面情况 4 中所示)、派生表、子查询和通用表表达式中的 Order by 一起使用。

例如，在 CTE 中使用 Offset 和 Order by：

;With Cte As
(
	Select * 
	From @tblSample 
	Order By Age 
	Offset 10 Rows)

Select * 
From Cte

以下示例显示了在派生表中使用 Offset 和 Order by：

Select * 
From
	(Select * 
	From @tblSample 
	Where Age >10
	Order By Age
	Offset 10 Rows) As PersonDerivedTable

以下示例显示了在视图中结合使用 Offset 和 Order by 子句：

--Create the view
Create View vwPersonRecord AS
Select * FROM tblSample
GO

-- Select the records from the view
Select *
From vwPersonRecord
Where Age > 10
Order By Age
Offset 10 Rows

Offset 何时不起作用

a)由于它是 Order by 子句的扩展，所以它不能单独使用。因此，以下语句：

Select *
From @tblSample   
Offset (10) Rows

将报告以下错误：

Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '10'。

b)我们不能在 offset 子句中指定负值。所以：

Select *
From @tblSample 
Order by Age  
Offset (-10) Rows

将产生以下错误：

Msg 10742, Level 15, State 1, Line 22 The offset specified in a OFFSET clause may not be negative。

c)也不能在 offset 子句中使用整数以外的任何其他数据类型。

Select *
From @tblSample 
Order by Age  
Offset 10.5 Rows

OR

Select *
From @tblSample 
Order by Age  
Offset Null Rows

将报告为：

Msg 10743, Level 15, State 1, Line 24 The number of rows provided for a OFFSET clause must be an integer。

d)不能与 Over() 子句结合使用。考虑以下查询：

;With Cte As
(
	Select 
			*,
			Rn = Row_Number() Over(Order by Age Offset 10 Rows)
	From @tblSample
)

Select * from Cte

执行后，我们将收到以下错误消息：

Msg 102, Level 15, State 1, Line 22 Incorrect syntax near 'Offset'。

Fetch First / Fetch Next

此关键字用于检索指定的行数。该语句的意思是，假设我们有 100 条记录，我们想跳过前 10 条记录并获取接下来的 5 条记录。所以我们需要从第 11 到第 15 条记录。在这种情况下，如果我们发出类似以下内容：

Select *
From  <SomeTable>
Order by  <SomeColumn>
Offset 10 Rows
Fetch Next 5 Rows Only; -- OR Fetch First 5 Rows Only

它将生成预期的记录集。

这类似于 .NET 框架 3.0 自推出以来包含的 Take 扩展方法。

可以使用 Fetch First/Next 子句的情况

为了演示 Fetch Clause，我们将使用与 Offset 演示中相同的记录集。

情况 1) 跳过前 10 条记录并显示第一条或接下来的 10 条记录

让我们发出以下查询：

-- Fetch the records from 11 to 15
Select *
From @tblSample 
Order by Age  
Offset 10 Row 
Fetch First 5 Rows Only
输出为：
Person Name	Age	Address
Person Name11	11	Address11
Person Name12	12	Address12
Person Name13	13	Address13
Person Name14	14	Address14
Person Name15	15	Address15

情况 2) 我们也可以在变量中指定要跳过的行数，如下所示：

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 10
--Set the value of rows to fetch
 Set @RowFetch = 5

-- Fetch the records from 11 to 15
Select *
From @tblSample 
Order by Age  
Offset @RowSkip Row 
Fetch  Next @RowFetch Rows Only;

情况 3) 我们可以像 Offset 子句一样，在 Fetch First 或 Fetch Next 子句中指定任何有效的 TSql 表达式或用户定义的函数、子查询。

情况 4) 像 Offset 一样，我们可以将 Fetch First 或 Fetch Next 子句与视图、内联函数、派生表、子查询和通用表表达式中的 Order by 一起使用。

Fetch First/Fetch Next 何时不起作用

上面描述的 Offset 子句失败的情况同样适用于 Fetch Next / Fetch First 子句。此外，Fetch Next /First 子句必须出现在 Offset 子句之前，否则我们会遇到以下错误：

Invalid usage of the option Next in the FETCH statement。

如果我们执行以下查询：

Select *
From @tblSample 
Order by Age 
Fetch  Next 10 Rows Only

Sql Server 2005/2008 中 Offset 和 Fetch Next 的模拟

在 Sql Server 2005/2008 的前一个版本中，我们可以通过使用 Row_Number() 排名函数来实现，如下所示：

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 10
--Set the value of rows to fetch
 Set @RowFetch = 5

 ;With Cte As
 (
	Select 
		rn=ROW_NUMBER() 
		Over(Order by (Select 1) /* generating some dummy column*/ )
		,*
	From @tblSample
 )
-- Fetch the records from 11 to 15
Select 
	[Person Name]
	,Age
	,Address 
From Cte
-- Simulating the behaviour of Offset Clause and Fetch First/Fetch Next

Where rn Between  (@RowSkip+1) -- Simulating Offset Clause
		And  (@RowSkip+ @RowFetch) -- Simulating Fetch First/Fetch Next Clause

该程序的作用是，在通用表表达式 (Cte) 中，它会生成一个名为 rn 的虚拟行号列。在 Cte 外部，我们过滤要跳过的行和要获取的行之间的记录。

Sql Server 2000/7 中 Offset 和 Fetch Next 的模拟

在 Sql Server 2000 或更早版本中，既没有排名函数 (自 Sql server 2005 起引入) 的概念，也没有 Offset 或 Fetch First/Next 语句 (在 Sql server 2011 中引入)。但是，我们仍然可以通过使用带有标识字段的临时表来实现，该字段将充当伪行号，如下所示：

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 10
--Set the value of rows to fetch
 Set @RowFetch = 5

--If the #Temp object exists in the tempdb, then drop it
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
Drop Table #Temp 
END

--Create a temporary table
Create Table #Temp
(
	Rn int Identity
	,[Person Name] Varchar(50)
	,Age int
	,Address Varchar(100) 
)

-- Insert the records into the Temporary table
Insert Into #Temp([Person Name],Age,Address)
Select  [Person Name],Age,Address
From @tblSample

-- Fetch the records from 11 to 15
Select 
	[Person Name]
	,Age
	,Address  
From #Temp

-- Simulating the behaviour of Offset Clause and Fetch First/Fetch Next
Where Rn Between  (@RowSkip+1) -- Simulating Offset Clause
		And  (@RowSkip+ @RowFetch) -- Simulating Fetch First/Fetch Next Clause

在这里，我们首先创建一个临时表，其字段与原始表相同，并添加一个类型为 int 且带有标识的额外列。该列将充当伪行号。然后，我们将临时表 (这里是 #Temp) 填充原始表中的记录，并选择指定范围内的记录。

注意：~ 这是处理问题的一种方法。然而，还有其他方法可以做到。也许我们可以使用 Tally table 作为数字表。

要了解更多生成数字表的方法，请访问 此网站。

Offset 和 Fetch First/Next 的实际用法

我相信我们到目前为止关于 Order by 子句的 Offset 和 Fetch First/Next 扩展子句的讨论已经清楚地说明了它们是什么、它们的使用目的等等。现在让我们看一些它们可以在实际场景中使用的例子。我们还将查看它们在其他 Sql server 版本中的实现，并将对所有版本的某些测试用例进行性能基准测试。我们将使用 100 万条数据进行实验，并使用 Tally table 进行脚本设置。

首先运行 Tally table 脚本，然后运行下面提供的脚本。

--Drop the table tblSample if it exists
IF OBJECT_ID('tblSample','U') IS NOT NULL BEGIN
	DROP TABLE tblSample
END
GO

-- Create the table
Create Table tblSample (
	[Person ID]	Int Identity
	,[Person Name] Varchar(100)
	,Age Int
	,DOB Datetime
	,Address Varchar(100) 
)
GO

-- Populate 1000000(ten lacs) data to the table
Insert into tblSample  
Select 
		'Person Name' + CAST(N AS VARCHAR)
		, N
		,DATEADD(D,N, '1900-01-01')
		,'Address' + CAST(N AS VARCHAR)
From dbo.tsqlc_Tally 
Where  N Between 1 and 1000000

-- Project the records
Select * 
From tblSample

用法 1：服务器端分页

分页是大多数应用程序中显示记录的一种常见实现。现在，这可以在客户端应用程序或服务器端应用程序中完成。但是，在客户端这样做会增加客户端应用程序的负担，因为获取整个记录集并将其保存在内存中，然后选择范围内的记录会导致严重的性能影响。另一方面，如果可以在数据库端完成，那么客户端应用程序将只获取它们在某个时间点感兴趣的记录，从而提高客户端应用程序的性能。

为了实验，我们将跳过前 20000 条记录，获取接下来的 50000 条记录。

Sql Server 7/2000 方法

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

USE TSQLDB;
GO       
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 20000
--Set the value of rows to fetch
 Set @RowFetch = 50000

--If the #Temp object exists in the tempdb, then drop it
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
Drop Table #Temp 
END

--Create a temporary table
Create Table #Temp
(
	Rn int Identity
	,[Person ID] int
	,[Person Name] Varchar(50)
	,Age int
	,DOB datetime
	,Address Varchar(100) 
)

-- Insert the records into the Temporary table
Insert Into #Temp([Person ID],[Person Name],Age,DOB,Address)
Select  [Person ID],[Person Name],Age,DOB,Address
From dbo.tblSample

-- Fetch the records from 11 to 15
Select 
	[Person ID]
	,[Person Name]
	,Age
	,DOB
	,Address  
From #Temp

-- Simulating the behaviour of Offset Clause and Fetch First/Fetch Next
Where Rn Between  (@RowSkip+1) -- Simulating Offset Clause
		And  (@RowSkip+ @RowFetch) -- Simulating Fetch First/Fetch Next Clause

GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

脚本与上面描述的脚本相似，因此不再赘述。

服务器执行时间为：

SQL Server Execution Times:
 CPU time = 110 ms,  elapsed time = 839 ms.

IO 统计信息如下：

Scan count 1, 
logical reads 8037, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

Sql Server 2005/2008 方法

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

USE TSQLDB;
GO       
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 20000
--Set the value of rows to fetch
 Set @RowFetch = 50000

;With Cte As
 (
	Select 
		rn=ROW_NUMBER() 
		Over(Order by (Select 1) /* generating some dummy column*/ )
		,*
	From dbo.tblSample
 )
-- Fetch the records from 11 to 15
Select 
	[Person ID]
	,[Person Name]
	,Age
	,DOB
	,Address  
From Cte
-- Simulating the behaviour of Offset Clause and Fetch First/Fetch Next

Where rn Between  (@RowSkip+1) -- Simulating Offset Clause
		And  (@RowSkip+ @RowFetch) -- Simulating Fetch First/Fetch Next Clause


GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

服务器执行时间为：

SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 631 ms.

IO 统计信息如下：

Scan count 1, 
logical reads 530, 
physical reads 0,
read-ahead reads 1549, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

Sql Server 2011 方法

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

USE TSQLDB;
GO       
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 20000
--Set the value of rows to fetch
 Set @RowFetch = 50000

Select *
From dbo.tblSample 
Order by (Select 1)  
Offset @RowSkip Row 
Fetch  Next @RowFetch Rows Only;

GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

服务器执行时间为：

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 626 ms.

IO 统计信息如下：

Scan count 1, 
logical reads 530, 
physical reads 0, 
read-ahead reads 1439, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

我们主要关注 CPU 时间 (执行查询所花费的时间) 和经过时间 (查询运行所花费的时间)。三个版本 CPU 和执行时间的表格比较如下：

Sql Server 版本 CPU 时间 已用时间
2000 110 毫秒 839 毫秒
2005/2008 78 毫秒 631 毫秒
2011 46 毫秒 626 毫秒

我们可以推断，与其他方法相比，Denali 的 Off Set 和 Fetch First/Next 子句提供了更好的性能。请注意，CPU 时间和经过时间可能因机器而异，但新的 Order by 子句扩展功能在 Denali 中的性能一直更好，如前所述。

用法 2：TOP 子句的替代方案

在某些情况下，这个新功能可以替代 TOP 子句。考虑以下情况，我们将按降序获取 Top 10 条记录的列表：

Sql Server Denali 之前的 SQL 方法

Select Top(10)	
	[Person ID]
	,[Person Name]
	,Age
	,DOB
	,Address 
From dbo.tblSample
Order By Age Desc

Sql Server Denali 方法

Select 
	[Person ID]
	,[Person Name]
	,Age
	,DOB
	,Address 
From dbo.tblSample
Order By Age Desc
Offset 10 Rows

参考：Order by Clause

四、Sequence 对象

虽然在 Oracle、DB2、PostgreSQL 和许多其他 RDBMS 术语中并非新事物，但在 Sql Server 领域首次出现了一个新朋友：Sequence。

那么，序列是什么？ 

它生成数字序列，就像 Sql 表中的标识列一样。但序列号的优点是序列号对象独立于表。它是 SQL Server 存储内存计数器的存储点。

考虑以下在 Sql Server 2008 中编写的程序。只需创建一个包含两列的表，其中一列是标识列。

Create Table WithOutSequence1
(
	EmpId int identity not null primary key
	,EmpName varchar(50) not null
)

Insert into WithOutSequence1
Select 'Niladri' Union All
Select 'Deepak'

Select * from WithOutSequence1

同样，创建一个具有相同架构的另一个表，如下所示：

Create Table WithOutSequence2
(
	EmpId int identity not null primary key
	,EmpName varchar(50) not null
)

Insert into WithOutSequence2
Select 'Niladri' Union All
Select 'Deepak'

Select * from WithOutSequence2

可以发现，我们在创建表时就被迫在两个表中都写入了标识列，即我们不能在另一个表中重用一个表的 EmpId 列。

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 }

所以，让我们先创建一个序列，如下所示：

IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateNumberSequence' AND TYPE='SO')
    DROP Sequence GenerateNumberSequence
GO
SET ANSI_NULLS ON
GO
CREATE SEQUENCE GenerateNumberSequence
START WITH 1
INCREMENT BY 1;
GO

执行此语句后，在 Sequences 节点中，我们将找到创建的序列对象。

Denali_Tsql_Part_2/11.jpg

一旦序列对象就位，接下来我们可以创建表并使用以下值填充它：

Create Table WithSequence1
(
EmpId int not null primary key
,EmpName varchar(50) not null
);

Insert into WithSequence1(EmpId, EmpName)
VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Niladri'),
(NEXT VALUE FOR GenerateNumberSequence, 'Deepak')

SELECT * FROM WithSequence1;

同样，如果我们创建另一个表，例如 WithSequence2，我们可以轻松使用 GenerateNumberSequence。

Create Table WithSequence2
(
EmpId int not null primary key
,EmpName varchar(50) not null
);

Insert into WithSequence2(EmpId, EmpName)
VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Niladri'),
(NEXT VALUE FOR GenerateNumberSequence, 'Deepak')

SELECT * FROM WithSequence2;

可以从系统目录 sys.sequences 中查看创建的序列，如下所示：

SELECT 
	Name
	,Object_ID
	,Type
	,Type_Desc
	,Start_Value
	,Increment
	,Minimum_Value
	,Maximum_Value
	,Current_Value
	,Is_Exhausted
 FROM sys.sequences
Denali_Tsql_Part_2/12.jpg

注意：~ 我故意使用这些列名，我将在稍后展示一些其他内容。

如果需要从 sys.sequences 目录获取关于创建的 Sequence 的完整元数据信息，我们可以查询 Select * from sys.sequences。请注意，Is_Exhausted 状态现在是零 (0) (如上图所示)。我们很快就会讨论这一点。

可以定义 Sequence 的数据类型

  1. Int
  2. Smallint
  3. Tinyint
  4. Bigint
  5. 十进制
  6. 数值

序列不一定必须从 1 开始。它可以从数据类型范围内的任何地方开始。例如，int 数据类型的范围在 -2147483648 到 2147483647 之间。

现在，如果我们输入如下内容：

CREATE SEQUENCE GenerateNumberSequence
START WITH -2147483649 --outside the range of the int datatype boundary
INCREMENT BY 1;

我们将收到以下错误：

An invalid value was specified for argument 'START WITH' for the given data type。

同样，如果我们创建序列时指定最大范围值，如下所示：

CREATE SEQUENCE GenerateNumberSequence
START WITH 2147483647 --the max range of the int datatype
INCREMENT BY 1;

引擎将报告以下消息：

The sequence object 'GenerateNumberSequence' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values。

并且 sys.sequences 的 Is_Exhausted 列变为 1。

Denali_Tsql_Part_2/13.jpg

这表明序列不能再使用。现在，如果我们想使用 GenerateNumberSequence 创建一个表，我们将收到以下错误：

The sequence object 'GenerateNumberSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated。

所以可以推断，引擎要求重新启动序列对象。要做到这一点，我们需要使用 Sequence 对象的 RESTART WITH 子句，如下所示：

ALTER SEQUENCE dbo.GenerateNumberSequence
RESTART WITH 1;

Restart With 值必须是整数，其范围必须是 MINVALUE >= RESTART WITH VALUE <= MAXVALUE。它将 SEQUENCE 对象当前值初始化为其初始值或指定值。

假设我们写了如下内容：

ALTER SEQUENCE dbo.GenerateNumberSequence
RESTART WITH 10;

那么在执行以下查询后：

Insert into WithSequence1(EmpId, EmpName)
VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Niladri'),
(NEXT VALUE FOR GenerateNumberSequence, 'Deepak')

SELECT * FROM WithSequence1;

我们将收到以下输出：

EmpId	EmpName
-----	-------
10	Niladri
11	Deepak

正如可以注意到的，序列号已从 10 开始。

如何获取序列对象的当前值、最大值和最小值？

答案是从 sys.sequences 目录。

MAX 和 MIN VALUE

这些是序列起始值的边界值。假设我们写了如下内容：

CREATE SEQUENCE GenerateNumberSequence
START WITH 1 
INCREMENT BY 1
MINVALUE 10
MAXVALUE 20

虽然 Min Value 是 10，Max Value 是 20，但我们试图从 1 (Start With 1) 开始序列。这绝对超出了边界范围。因此，我们将收到一条错误消息：

The start value for sequence object 'GenerateNumberSequence' must be between the minimum and maximum value of the sequence object。

现在考虑当序列的下一个值达到最大边界限制的情况。在这种情况下，我们将收到一条错误消息，如：

The sequence object 'GenerateNumberSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated。

为了解决这个问题，我们有两种选择：

a) 使用 RESTART 或 RESTART WITH 选项 (上面讨论过) 重新启动序列。

b) 使用 Cycle 选项。

Cycle 选项

如果序列的下一个值超过最大值，它将把序列重置为最小值。

例如，如果我们创建了一个序列，如下所示：

CREATE SEQUENCE GenerateNumberSequence
START WITH 20 
INCREMENT BY 1
MINVALUE 10
MAXVALUE 20
CYCLE

并且在达到最大值后，我们将得到如下输出：

EmpId	EmpName
-----	-------
10	Deepak
20	Niladri

对于相同的 select 语句：

Insert into WithSequence1(EmpId, EmpName)
VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Niladri'),
(NEXT VALUE FOR GenerateNumberSequence, 'Deepak')

SELECT * FROM WithSequence1;

如果我们仔细观察输出，我们会发现记录被交换了。理论上应该是：

EmpId	EmpName
-----	-------
20	Niladri
21	Deepak

但是，由于第二条记录越过了边界限制，因此它已被循环到序列的最小值 10。然而，第一条记录仍在边界范围内。此时，如果我们查看 sys.sequences 目录，我们会发现当前值设置为 10。

Denali_Tsql_Part_2/14.jpg

下次运行该语句时，值将是：

EmpId	EmpName
----	-------
11	Niladri
12	Deepak

此时，序列会检查记录插入的顺序。由于“Niladri”出现在“Deepak”之前，并且 Current_Value 列的值是 10，因此记录插入为：

Next_Value = Current_Value + Increment，即 10 + 1 被分配给“Niladri”。

此时 Current_Value 将为 11。对于第二条记录，通过相同的方式，序列值变为 12。

No Cycle 选项

使用此选项时，当序列的下一个值达到最大边界限制时，我们将收到相同的错误消息：

The sequence object 'GenerateNumberSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated。

带 Over 子句的 Sequence

我们可以将序列与 Over 子句结合使用，以生成运行编号，如下所示：

--Declare a table
Declare @tblEmp Table
(
EmpId int identity 
,EmpName varchar(50) not null
)
--Populate some records
Insert Into @tblEmp   
Select 'Niladri' Union All
Select 'Arina' Union All
Select 'Deepak' Union All
Select 'Debasis' Union All
Select 'Sachin' Union All
Select 'Gaurav' Union All
Select 'Rahul' Union All
Select 'Jacob' Union All
Select 'Williams' Union All
Select 'Henry'

--Fire a query
SELECT 
	e.*
	, Seq = NEXT VALUE FOR GenerateNumberSequence OVER (ORDER BY EmpName) 
FROM @tblEmp e

输出

Denali_Tsql_Part_2/15.jpg

可以发现，尽管记录已被排序，但序列已正确添加到排序结果中。这意味着，首先对记录进行排序，然后应用序列。

Next Value For 函数的限制

它永远不能与以下项结合使用：

  1. Check constraints
  2. Default objects
  3. Computed columns
  4. 视图
  5. User-defined functions
  6. User-defined aggregates
  7. Sub-queries
  8. Common table expressions
  9. Derived tables
  10. 顶部
  11. Over
  12. 输出
  13. On
  14. 其中
  15. Group By
  16. Having
  17. Order By
  18. Compute
  19. Compute By

sp_sequence_get_range

如果我们观察上面使用 NEXT VALUE FOR 向表中插入值的处理方法，我们会为 Values 子句的每个级别进行插入，这似乎有些繁琐。相反，我们可以使用 sp_sequence_get_range 来获取一系列值，并将其用于在内部填充。让我们看看如何实现。

--Drop the Sequence object if it exists
IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateRangeNumberSequence' AND TYPE='SO')
    DROP Sequence GenerateRangeNumberSequence
GO
-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_RangeSequence' AND type = 'U')
    DROP TABLE tbl_RangeSequence
GO
SET ANSI_NULLS ON
GO
--Create the sequence
CREATE SEQUENCE GenerateRangeNumberSequence
START WITH 1 
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2000
CYCLE
GO
--Create the table
CREATE TABLE [dbo].[tbl_RangeSequence](
	[EmpId] [int] NOT NULL,
	[EmpName] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[EmpId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--Declare the needed parameter for the sp_sequence_get_range
DECLARE
  @sequence_name nvarchar(100) = N'GenerateRangeNumberSequence',
  @range_size int = 1000,
  @range_first_value sql_variant,
  @range_last_value sql_variant,  
  @sequence_increment sql_variant,
  @sequence_min_value sql_variant,
  @sequence_max_value sql_variant;

--Execute the stored procedure sp_sequence_get_range
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,  
  @sequence_increment = @sequence_increment OUTPUT,
  @sequence_min_value = @sequence_min_value OUTPUT,
  @sequence_max_value = @sequence_max_value OUTPUT;

-- Display the values
SELECT
  @range_size AS [Range Size],
  @range_first_value AS [Start Value],
  @range_last_value AS [End Value], 
  @sequence_increment AS [Increment],
  @sequence_min_value AS [Minimum Value],
  @sequence_max_value AS [Maximum Value];

--Build the range of values in the CTE
;With Cte As
(
Select Rn = 1, SeqValue = Cast(@range_first_value as int)
Union All 
Select Rn+1, Cast(SeqValue as int) + Cast( @sequence_increment as int)
From Cte
Where Rn<@range_last_value
)
--Insert 100 Records
Insert into tbl_RangeSequence(EmpId, EmpName)
Select SeqValue,'Name' + Cast(SeqValue as varchar(3)) 
From Cte
Where SeqValue<=100
Option (MaxRecursion 0)

----Display the result
SELECT * FROM tbl_RangeSequence

输出 (部分) 如下：

Denali_Tsql_Part_2/16.jpg

因此，我们可以看到序列将递增到 1000，这些值将不会在任何地方使用，并且可以在各种表之间操作 (在本例中只是一个插入操作)。

在表之间共享 Sequence

我们可以将 Next Value For 语句用作表的默认值，如下面的代码片段所示：

USE TestDB; -- Assume we have such a database.Else we need to create one
GO

--Create the sequence
CREATE SEQUENCE GenerateNumberSequence AS INT
START WITH 1 
INCREMENT BY 1
NO CYCLE;
GO

--Create the first table and use the GenerateNumberSequence sequence
CREATE TABLE TestTbl1 
(
	Id INT DEFAULT NEXT VALUE FOR GenerateNumberSequence
	,Name VARCHAR(50)
);
GO

--Create the second table and use the GenerateNumberSequence sequence
CREATE TABLE TestTbl2 
(
	Id INT DEFAULT NEXT VALUE FOR GenerateNumberSequence
	,Name VARCHAR(50)
);
GO

--Insert some records in the first table
INSERT INTO TestTbl1(Name) VALUES('Aditi'),('Soumen'),('Pratiksha'),('Arina'),('Niladri');
GO

--Insert some records in the second table
INSERT INTO TestTbl2(Name) VALUES('Sachin'),('Vinay'),('Satish'),('Nil'),('Zahir');
GO

--Project records from first table
SELECT * FROM TestTbl1;
GO

--Project records from second table
SELECT * FROM TestTbl2;
GO

--Finally drop the objects
DROP TABLE TestTbl1;
DROP TABLE TestTbl2;
DROP SEQUENCE GenerateNumberSequence;

Sequence 和 Identity 列的比较

我们不应将两者视为相同，因为：

1) Identity 列是表特定的，而 Sequence 是表独立的。

2) 我们可以使用 sp_sequence_get_range 创建一个序列范围，而 Identity 列无法做到。

3) 在序列的情况下，我们可以使用 Minimum 和 Maximum Value 定义边界。而 Identity 无法做到。

4) Sequence 中存在循环，而 Identity 列中不存在。

关于序列的最后几句话

  1. 正如 Aaron Bertrand 在这篇文章中所指出的，与 Identity 列相比，Sequence 提供了更好的性能。
  2. 我们可以授予 Sequence 对象 Alter、control、references、update、Take Ownership 和 View definition 等权限。

请注意，也可以不使用 T-Sql 代码创建序列，如上一篇文章中所述。

参考文献

  1. CREATE SEQUENCE
  2. 创建和使用序列号
  3. sp_sequence_get_range

结论

Denali 为我们带来了很多东西。在本系列文章中，我们看到了：

* With Result Set 子句，其适用情况，与 Denali 之前的方法的优势，从 With Reuslt Set 获取值，其适用性和缺点。

* Throw 语句相对于 Denali 之前版本的优势 (例如 @@Error, @@TranCount, @@RowCount, RaiseError 等)，以及处理 Try..Catch 块中未捕获错误的方法。

* Offset 和 Fetch First/Next 语句的优势，它们的适用性和当前限制，以及在服务器端分页方面比 Denali 之前的 SQL 方法提高的性能。

* Sequence 对象的优势和适用性，其创建以及与 Identity 列的比较研究。

在下一部分中，我们将看到 SSIS 中所做的改进。敬请关注并分享您对本文的看法。

© . All rights reserved.