An Introduction to Sql 11 (Code Name Denali) –Part II (T-Sql Features in CTP 1)

An Introduction to Sql 11 (Code Name Denali) –Part II (T-Sql Features in CTP 1)

Table of Content

1. Introduction
2. Background
3. TSql New Features And Enhancements
   1. With Result Sets(New Feature)
   2. Throw Statement(New Feature)
   3. Offset and Fetch First/Next Clause – Extension to Order by clause(Enhanced Feature)
   4. Sequence Objects(New Feature)
4. Conclusion

Introduction

One of the hottest and awesome developments by Microsoft in the technology field was come into picture on 8th November, 2010 when they released the Community Technology Preview 1 (CTP 1) version of Sql Server 2011(Code name Denali). The CTP1 is available both in 32-bit and 64-bit versions. As expected, Denali has brought some new features for Sql lovers may that be developers, Administrators or Business Intelligence (BI) professionals. In this series we will explore on the enhancements and new features of TSql. In Part I we have seen some of the enhancements and new features of SSMS. About the rest of the features will look into the subsequent series.

Background

In the last few years, Microsoft has brought many technologies under the developers’ hood. A drastic change has been made in the Sql Server jargon with the advent of Sql Server 2005(code name Yukon) and in the subsequent releases like Sql Server 2008(code name Katmai) and Sql Server 2011(code name Denali), the same pace has been kept with introduction to new features and enhancements as well as improvements.In this article we will explore more on the new features that Denali has already offer us from TSql perspective. The subsequent articles will focus on the enhancements made in the Administrators and BI areas.

TSql New Features And Enhancements

As usual, Denali didnot upset the TSql developers. It has brought some really fantastic features inorder to ease the development of the developers.

In the following section, we will explore them.

I.With Result Sets

The With Result Sets feature of Denali allow us to change the column names and data types of the result set that a stored procedure returns.

Before going to explore that,let us observe how in earlier versions of Sql Server (pre-Denali), we used to get the values from an executing stored procedure.

For this demonstration, we will use the below table (tbl_Test), having 3 columns as the example.

Now let us populate some records (say 1000) to the table by using the below script

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

Running the script will bring the below record set (partial)

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

Let us write a stored procedure for fetching the result from the table tbl_Test.

CREATE PROCEDURE dbo.Usp_FetchRecords
AS
BEGIN 

       Select 
			Id
			,Name
			,PhoneNumber
		From dbo.tbl_Test
END

In order to get the result set from an executing stored procedure, there are various approaches available as discussed by Erland Sommarskog in his article.We will,however, look into one of the approaches

Temporary Table Approach

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

The above approach is fine if we know in advance the columns and their data types being returned by the stored procedure.

Disadvantages of the pre-Denali approaches are

1. None of the approach was straight forward. In the sense, we need the help of a temporary table or variable, dispose that once the operation is over;else that will consume unnecessary database space
2. Process was lengthy
3. In the case of Open Row Set or Open query, we need to turn on the ‘Ad Hoc Distributed Queries’ feature and then to proceed.
4. In the case of Temporary table or Table variable approach, we need to know in advance what is the data type of the column

Denali’s (Sql Server 2011) With Result Set Approach

Denali’s With Result Set has overcome the above drawbacks. Let us see how. Let us execute the below query(for single result set)

EXEC Usp_FetchRecords 

WITH RESULT SETS

(
       (      [Emp Id] int,

              [Emp Name] varchar(50),

              [Phone Number] varchar(50)

       )  

) 

The output being (partial output)

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

The general syntax of With Result Set will be

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]

       )      
)

Henceforth, we can make out that, irrespective of the column name(s) returned in the result set, we can change the column names and it’s data Types as long as the data Type conversion is compatible with the original result set(i.e. the data types defined in the table schema). Else the database engine will report error.

e.g. in the below example we have changed the Name columns data type(originally as varchar(50)) to int.

EXEC Usp_FetchRecords
WITH RESULT SETS

(
       (      [Emp Id] int,

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

              [Phone Number] varchar(50)

       )  

) 

Upon execution, we will receive the below error message

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

Whereas changing the same to Text (for example) data type works fine.

The above query was made for demonstration purpose of transforming a single Result Set with Execute’s With Result Set. However, it can be extended to transform for multiple result set. Let us see how.

Consider the below stored procedure, which returns two different record sets.

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

The second select statement generates the records for those Names who are having an even digit phone number.

The partial output is as under after executing the stored procedure

Now , let us execute the below query

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)             

       ) 

) 

The partial output being

However, since the stored procedure is returning two record set(in this case), if we try to obtain only one record set in the With Result Sets clause , the engine will report the below error

Msg 11535, Level 16, State 1, Procedure Usp_ModifiedFetchRecords, Line 11 EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.

As can be seen that the With Result Set clause of the Execute command basically does transformation on the result set of the stored procedure. It has eliminated the drawbacks of the aforesaid procedures for sharing data of the stored procedure.

How can get the values from the With Result Set

We may sometime need to get the value from the With Result Set clause. In such a case we can go ahead with a Temporary table or Table variable approach.

Here we will look into the Table Variable Approach

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

The output being as expected (last 10 records shown)

Applicability

1. Data conversion will become simpler in SSIS as described well in this article
2. Changing the data type without changing the schema. Suppose a dotnet application is expecting a Boolean and the underlying schema was designed as of type int for that column.Ideally we do a conversion at runtime as Case When <condition> Then 1 Else 0. Instead of that, we can directly change the data type to bit.
3. Another example can be say the dotnet application is expecting a int but the column type is float.
4. Another usage may be say the schema has been changed and the DAL layer is not aware of this. May be the same stored procedure is called from multiple places. In such a scenario, we can just change the column names at runtime in the With Result Set so that the table schema as well as the DAL logic will be un touched.

DrawBacks

We cannot return selected columns. The number of columns has to be same as that of the result set. For example, if we write something as under

EXEC Usp_FetchRecords 

WITH RESULT SETS

(
       (      [Emp Id] int,              

              [Phone Number] varchar(50)

       )  

) 

The engine will report the below error

Msg 11537, Level 16, State 1, Procedure Usp_FetchRecords, Line 5 EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.

II.Throw Statement

A new addition to Sql Server 2011(Denali) is the Throw statement. It is used in conjunction with the Try...Catch block and is used to notify the occurrence of runtime exception. When an exception is thrown, the program looks for the catch statement that handles this exception. By using this statement inside a catch block, we can change the resulting exception. Moreover, we can throw a new exception nearly anywhere in program.

In this article we will look into the various exceptions that Sql Server is supporting dated from Sql server 2000.

We will also take the below table(tbl_ExceptionTest) into consideration for all the cases of exceptions that we will look into.

The table is created by issuing the below 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

The intension is to insert some records at runtime into the table and while inserting into Phone Number column, we will insert some invalid data that will generate some exception.

Exception handling in Sql Server 2000(Code name:Sphinx)

1.Using @@ERROR global system variable

Dated back in Sphinx, the code name for Sql Server 2000, we had the @@Error system variable which was considered as the most effective error handling tool for the T-Sql developers.Its responsibility lies in returning the error number, which is of type int for the last T-Sql statement executed.@@ERROR variable stores the appropriate error number with it whenever an error is. The error number can be positive, negative, or 0(which indicates success). The value of @@ERROR changes with every execution of the statement.

Let us see, the @@Error system variable into action

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

Executing the program produces the below error message

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

And the Results Tab is as expected with all the records being rolled back.

Drawback of @Error approach

1)Checking for @@Error must be done immediately after execution of a statement.

2)As @@ Error values changes with every execution of statement in the code we need to use a local variable to store the @@error value and use it whenever needed.

3)Along with the custom error, the system defined error also appears

For more information on @@Error global system variable, please visit @@Error

2.Using @@TRANCOUNT global system variable

It returns the number of live transactions that are active for the current connection. Like @@ERROR system variable, this variable value also changes with every execution of the statement. Hence we should use a local variable to store the @@TRANCOUNT value and use it whenever needed.Each BEGIN TRANSACTION increases @@TRANCOUNT by 1, and each COMMIT TRANSACTION decreases its value by 1 and ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0. Records are committed only when @@ TRANCOUNT reaches 0.

Let us see, the @@Trancount system variable into action for the same scenario

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

For more information on @@TRANCOUNT global system variable, please visit @@TRANCOUNT

3.Using @@ROWCOUNT global system variable

It returns the number of rows affected in the most recently executed statement. Since it’s value changes with every execution of the statement, so it is better to store that in some local variable to to use it at later point of time.

Let us use the @@ROWCOUNT variable into action for the same scenario

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

For more information on @@ROWCOUNT global system variable, please visit @@ROWCOUNT

Exception handling in Sql Server 2005/2008(Code name:Yukon and Katmai respectively)

Since the advent of Sql Server 2005(code name Yukon) and lingering in Sql Server 2008(code name Katmai), we have Try...Catch block. We can now catch Transaction Abort Errors using the TRY/CATCH model without any loss of the transaction context.

Let us see the TRY...CATCH block into action for the same scenario

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

After execution, we will receive the below

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

As can be make out that this time we are able to generate our own custom message that is defined in the RaiseError function.The Try…Catch block looks good and makes the code clean in its appearance. The valid action part of the logic goes into the TRY block and the error handling part resides into the Catch block. If the piece of code within the Try block seems to be anomalous, the control goes to the Catch block, Rolls back the transaction and the rest of the program resumes. If all the statement within the Try block runs smoothly, then the control never enters the Catch block but executes the very first statement following the End Catch statement.Moreover, the catch block provides sufficient information during the anomalous situation which should be trapped for appropriate information to glean about the program failure like

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

Henceforth, in the above program, if we change the CATCH block like the below

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

We will receive the following

Drawback of RaiseError Function

1)If we remember, while using the RaiseError function in the Catch block, the error reported was line number 45.

But it was indeed generated in line number 24 where we wrote Insert into #tblExceptionTest([Phone Number]) Values(null)

However, the ERROR_LINE () function does report the actual line where the error has raised from. As an alternative proof of the concept, let us change the CATCH block as below

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

In this case, the engine gives the below report

So we can conclude that, by using RaiseError, we lose the original error line number.

2)Another drawback is that we cannot re-raise the same error. So if we write the below in the CATCH block

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

We will receive the below error message from the engine

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

The reason is that, for RaiseError to raise an error, the message number has to be there in the sys.messages table.

For more info on RaiseError, please visit:

1. A Closer Look Inside RaiseError-Sql Serevr 2005
2. Using RaiseError

Exception handling in Sql Server 2011(Code name:Denali)

The aforesaid drawbacks of RaiseError has been overcome by the new Throw command of Denali. Let us see how

The first drawback of the Raise Error that we encounter was that it does not retain the original error line number. Let us observe how far the fact is true while using Throw command.

Rewriting the Catch block of the above T-Sql code using Throw

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

Yields the below output

This is rather correct and hence proves our statement.

The second drawback was that, using RaiseError we cannot re-raise the same error because RAISE ERROR expects the number to be stored in the sys.messages. Throw does not expect the error number to be in the sys.messages table though the error number should be between 50000 and 2147483647(both inclusive).

As a part of this exercise, let’s change the Catch block as under

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

And we get the below

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

Though there are now many ways to handle exception is Sql Server, still every error that arises is not being caught by the Try..Catch construct.For example

a)Syntax error is being caught by the Query Editor parser of SSMS

b)Invalid object names

For example, if we do something as

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

And try to execute the same, we will receive the below error

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

So we can make out that, it is nearly impossible to trap such kind of errors.

But there is a tricky way of doing so. The idea is to create two stored procedures, call one inside the other in the Try..Catch block and trap the exception.As a proof of the above statement, we will take the above scenario into consideration and will go ahead to do the experiment.

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

And the result is as under

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

The Result pane gives the below

Code explanation

We have two stored procedure viz usp_InternalStoredProc and usp_ExternalStoredProc.In the usp_InternalStoredProcedure, we are trying to insert record into table (#tblInnerTempTable) wich does not have any existence.From the usp_ExternalStoredProcedure, we are calling the inner procedure and the exception is being caught in the outer procedures catch block.

Moreover, the error line (which is 8 here) is also correct.

Last but not the least the we need to terminate the previous batch by placing a semi colon before THROW as THROW command should be issued as a new batch else we will receive the below error

Incorrect syntax near 'THROW'.

More information on Throw statement can be found from Throw

III.Offset and Fetch First/Next Clause – Extension to Order by clause

In Denali, the order by clause has been enriched by the addition of two clauses

a)Offset

b)Fetch First or Fetch Next

Offset

This keyword is use to skip the number of rows before retrieving the rows for the projection. What the statement implies is that, suppose we have 100 records and we want to skip the first 10 records. So we need the records from 11 to 100. In this case if we issue something as

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

It will generate the expected record set.

If someone is aware with dot net from framework 3.0 onwards, we have the Skip extension method which skips over the elements in a collection specified to it and access the rest of the elements. The Offset clause is similar to the Skip extension method added from dot net framework 3.0.Once the record set is sorted in the order by clause with the columns specified, the Offset clause gets evaluated.

Situations where we can use Offset Clause

For the rest of the article about Offset , we will use the below record set

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

Case 1)Skip the first 10 records and display the rest

Let us issue the below query

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

The output (partial) will be

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

We can specify either Row or Rows as they are synonyms.

Case 2) We can specify the Number of Rows to skip in a variable also as shown below

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

Case 3) We can specify any valid TSql expression inside the Offset clause

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

The select MAX(number)/99999999 from master..spt_values will return the value as 14. Hence the records will be skipped by the first 14 rows and will display the rest.

Case 4) We can even specify a user define function inside the Offset as under

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

Where the user defined scalar function is defined as under

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

Case 5)We can use the Offset clause along with Order by inside views, inline functions(as seen above in Case 4), derived tables, subqueries, and common table expressions.

e.g. Offset with Order by inside a CTE

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

Select * 
From Cte

The below example shows the working of Offset with Order by inside a Derived Table

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

The below example shows the working of Offset with Order by clause in conjunction with a view

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

When will offset not work

a)Since it is an extension to the Order by clause, so it alone cannot be use Hence, the statement

Select *
From @tblSample   
Offset (10) Rows

will report the below error

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

b)We cannot specify a negative value in the offset clause. So

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

will produce the below error

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

c)Also we cannot provide any other data type except for integer in the offset clause

Select *
From @tblSample 
Order by Age  
Offset 10.5 Rows

OR

Select *
From @tblSample 
Order by Age  
Offset Null Rows 

will report as

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

d)Cannot be use in conjunction with the Over() clause. Consider the below query

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

Select * from Cte

Upon execution we will receive the below error statement

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

Fetch First / Fetch Next

This keyword is use for retrieving the specified number of rows.What the statement indicates is that, suppose we have 100 records and we want to skip the first 10 records and want to take the next 5 records. So we need the records from 11 to 15. In this case if we issue something as

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

It will generate the expected record set.

It is similar to the Take extension method which has been included since dot net framework 3.0.

Situations where we can use Fetch First/Next Clause

For the demonstration purpose of the Fetch Clause, we will use the same record set that we have used for Offset demonstration.

Case 1) Skip the first 10 records and display the first or next 10 records

Let us issue the below query

-- Fetch the records from 11 to 15
Select *
From @tblSample 
Order by Age  
Offset 10 Row 
Fetch First 5 Rows Only

The output being

Person Name	Age	Address
Person Name11	11	Address11
Person Name12	12	Address12
Person Name13	13	Address13
Person Name14	14	Address14
Person Name15	15	Address15

Case 2) We can specify the Number of Rows to skip in a variable also as shown below

-- 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;

Case 3) We can specify any valid TSql expression or user define function, sub query inside the Fetch First or Fetch Next clause just like Offset clause

Case 4) As like Offset, we can use the Fetch First or Fetch Next clause along with Order by inside views, inline functions, derived tables, sub queries, and common table expressions.

When will Fetch First/Fetch Next not work

The situation described above for the failure of Off set clause, equally holds good for the Fetch Next / Fetch First clause. Moreover,the Fetch Next /First clause must precede with the Offset clause else we will encounter the below error

Invalid usage of the option Next in the FETCH statement.

If we execute the below query

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

Simulation of Offset and Fetch Next in Sql Server 2005/2008

In the previous version of 2005/2008 of Sql Server, we could have achieved the same by using the Row_Number() ranking function as shown below

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

What the program does is that, inside the Common Table Expression(Cte), it is generating a dummy row number column named as rn.And outside the Cte, we are filtering the records between the rows to skip and Rows to fetch.

Simulation of Offset and Fetch Next in Sql Server 2000/7

In the days of Sql Server 2000 or previous version, there was neither the concept of Ranking function (introduce since Sql server 2005) nor there was any Offset or Fetch First/Next statement (introduce in Sql server 2011). However, still we could have achieve the same by the usage of a Temporary table with an identity field which will act as pseudo Row Numbers as shown below

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

Here we are first creating a temporary table with the same fields as that of the original table and added an extra column of type int with identity on. This column will act as a pseudo row number.Then we are populating the temporary table (here #Temp) with the records from the original table and selecting the records between the range specified.

N.B.~ This is one of the ways of approaching the problem. However, there are other ways also of doing the same. May be we can use a Tally table that can acts as a number table.

Visit this site for more ways of generating number table.

Practical Usages of Offset and Fetch First/Next

I believe that whatever we have discussed so far about the Offset and Fetch First/Next extension clauses of Order by clause, has made the idea clear as what they are, the purpose of their usage etc.Now let us see some example as where they can be use in real time scenario. We will also see the implementation of the same in other Sql server versions and will make a performance bench mark testing for some of the test cases for all the versions.We will use 1 million of data for our experiment and the Tally table for our script set up.

First run the Tally table script followed by the script provided below.

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

Usage 1: Server Side Paging

Paging is a very common implementation in most of the applications for displaying the records. Now this can be done either at the client side application or at the server side application. But doing so in the client side will increase the load on the client application as fetching the whole records set and keeping them into the memory, then choosing the records within the range will give a serious performance impact. On the other hand, if it can be done at the database side, then the client application will get only those records in which they will be interested in at that point of time and hence the client application’s performance will boost.

For the experiment sake we will skip the first 20000 records and will take the next 50000 records.

Sql Server 7/2000 approach

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

The script is similar to the above described script and hence no further explanation is given.

The Server Execution time is

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

And the IO statistics is as under

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 approach

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

The Server Execution time is

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

And the IO statistics is as under

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 approach

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

The Server Execution time is

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

And the IO statistics is as under

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.

We are interested mostly in the CPU time (time use to execute the query) and the Elapsed time (time taken by the query to run).A tablular comparison for the CPU and Executed Time of the three versions is as under

Sql Server Version	CPU Time	Elapsed Time
2000	110ms	839 ms
2005/2008	78ms	631 ms
2011	46ms	626 ms

We can infer that, Denali’s Off Set and Fetch First/Next clause gives a better performance as compared to the other methods.Kindly note that, the CPU time and Elapsed time may differ from machine to machine, but the performance has always been better for the new extension feature of Order by clause in Denali as described.

Usage 2: An alternative to TOP Clause

This new features can be a substitute for TOP clause in certain situations.Consider the below situation where we will get the list of Top 10 records in descending order

Sql Server Pre-Denali Approach

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

Sql Server Denali Approach

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

Reference:Order by Clause

IV.Sequence Objects

Though not new in the Oracle, DB2, PostgreSQL and many other RDBMS jargon but first time in Sql Server arena is a new friend, Sequence.

So what is a sequence? 

It generates sequence of numbers just like an identity column in Sql tables. But the advantage of sequence numbers is that the sequence number object is independent of table. It is a point of storage where SQL Server will keep an in memory counter.

Consider the below program written in Sql Server 2008. Simply creating a table with two columns, one being the identity.

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

Likewise create another table with the same schema as under

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

As can be figure out that we are forced to write the identity column in both the tables at the time of creation i.e. we cannot reuse the EmpId column of one table in another.

Sequence helps us to do so. Let us see how.

The general syntax for creating a sequence is as under

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 }

So let us first create a sequence as

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

After execution of this statement, in the Sequences node we will find that out sequence object has been created

Once the sequence object is in place, next we can create the table and populate it with the values as under

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;

Likewise if we create another table say WithSequence2, there we can easily use the 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;

The Sequence being created can be viewed from the system catalog sys.sequences as under

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

N.B.~ I am deliberately using these column names for which I will show something else at a later point of time.

If we need to get complete metadata information about the Sequence created from the sys.sequences catalog, we can query as Select * from sys.sequences.Kindly note that, the Is_Exhausted status is zero(0) now(as depicted in the above diagram). We will talk about this very soon.

DataTypes for which Sequence can be defined

1. Int
2. Smallint
3. Tinyint
4. Bigint
5. Decimal
6. Numeric

It is not mandatory for a sequence to start with 1. It can be started from anywhere within the range of the data type. For example, the range of int data type lies between -2147483648 to 2147483647

Now if we give something as the below

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

We will receive the below error

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

Likewise if we specify the maximum range value while creating the sequence as under

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

The engine will report the below message

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.

And the Is_Exhausted column of the sys.sequences as become 1.

Which indicates that the sequence cannot be use any more.Now if we want to create a table using the GenerateNumberSequence we will receive the below error

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

So it can be inferred that, the engine is asking to Restart the sequence object.For doing so, we need to use the RESTART WITH clause of the Sequence object as under

ALTER SEQUENCE dbo.GenerateNumberSequence
RESTART WITH 1;

The Restart With value must be an integer and whose range has to be MINVALUE >= RESTART WITH VALUE <=MAXVALUE.It initiates the current value of a SEQUENCE object to its initial value or the value mentioned.

Suppose we would have written as

ALTER SEQUENCE dbo.GenerateNumberSequence
RESTART WITH 10;

Then after execution of the below query

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

SELECT * FROM WithSequence1;

We would have received the below output

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

As can be noted that, the sequence numbers has been started from 10.

How to get the current, maximum and minimum value of the Sequence object

The answer is from the sys.sequences catalog.

MAX and MIN VALUE

These are the boundary values for the Start Value of the sequence. Suppose we have written something as under

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

Though the Min Value is 10 and the Max Value is 20, but we are trying to start the sequence from 1(Start With1).This is absolutely outside the boundary range. Hence we will receive an error message

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

Now consider the situation when the next value of the sequence has reached the maximum boundary limit.In such a case we will receive an error message as

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

To culminate this problem, we have two options

a)To Restart the sequence by using the RESTART or RESTART WITH option(discussed above)

b)To use Cycle option

Cycle Option

If the next value of the sequence exceeds that of the maximum value, then it resets the sequence to the minimum value

For example, if we have created a sequence as under

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

And after the maximum value has been reached, we will get the output as

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

For the same select statement

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

SELECT * FROM WithSequence1;

If we observer the output properly, we will find that the records has been swapped. It should have been hypothetically

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

But since the second record has crossed the boundary limit, so it has been recycled to the minimum value of the sequence which is 10.However,the first record is still inside the boundary range.At this point if we look into the sys.sequences catalog, we will find that the current value is set to 10.

The next time we run the statement, the values will be

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

At this point the sequence checks the order in which the record is being inserted. Since “Niladri” comes before “Deepak”, and the Current_Value column’s value is 10, so the records got insert as

Next_Value = Current_Value + Increment i.e. 10 +1 has been assigned to “Niladri”.

At this point the Current_Value will be 11. For the second record, the sequence value has become 12 by adopting the same way.

No Cycle Option

By using this option when the next value of the sequence will reach the maximum boundary limit, we will get the same error message as

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

Sequence with Over clause

We can use sequence in conjunction with Over clause in order to generate a running number as under

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

Output

As can be figured out that, though the Records have been sorted but the sequence has been added properly to the sorted result. This implies that, first the records has been sorted and then the sequence has been applied.

Limitation of Next Value For Function

It can never be use in conjunction with

1. Check constraints
2. Default objects
3. Computed columns
4. Views
5. User-defined functions
6. User-defined aggregates
7. Sub-queries
8. Common table expressions
9. Derived tables
10. Top
11. Over
12. Output
13. On
14. Where
15. Group By
16. Having
17. Order By
18. Compute
19. Compute By

sp_sequence_get_range

If we observe in the above approache of inserting values into the table using NEXT VALUE FOR, we are doing it for every level of the Values clause which seems to be bit tedious.Instead, we can use the sp_sequence_get_range to get a range of values and can use the same in order to populate the same inside.Let us see how.

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

The output (partial) being as under

Thus we can see that the sequence will be incremented till 1000 and these values will remain unused anywhere and can be use in various operations across tables (in this case it is just an insert operation).

Sharing Sequence among tables

We can use the Next Value For statement as Default Value in a table as being shown in the following code snippet

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;

Comparison between Sequence and Identity Column

We should not consider both as same since

1)Identity Column is table specific while Sequence is table independent

2)We can create a range of sequence using sp_sequence_get_range which is not possible using identity column

3)In case of sequence we can define the boundary using the Minimum and Maximum Value. While the same is not possible in case of identity

4)Cycling is there in sequence and absent in Identity column

Last few words about sequence

1. Sequence gives better performance as compared to Identity Column as indicated by Aaron Bertrand in this article
2. We can grant permission like Alter, control, references, update, Take Ownership and View definition to the sequence objects

Note that sequence can also be created without using T-Sql code as described in the previous article.

References:

1. CREATE SEQUENCE
2. Creating and Using Sequence Numbers
3. sp_sequence_get_range

Conclusion

Denali has brought a lot to us.In the current series we have seen

* With Result Set clause,situations where it is applicable,it's advantages over the pre-Denali approaches,fetching values from With Reuslt Set,it's applicability and drawbacks.

* Advantages of Throw statment over the pre-Denali verisons(e.g. @@Error,@@TranCount,@@RowCount,RaiseError etc.),a way to handle eror not being catched in the try..catch block

* Advantages of Offset and Fetch First/Next statement, their applicability and current limitations,perfomace boost over the pre-Denali approaches in server side paging.

* Advantages and applicability of Sequence objects,creation of the same and a comparative study with Identity Column.

In the next part we will see the improvements made in SSIS.So stay tune and share your opinion about the article.