Category: TSQL 2012 New Features

Sequences in SQL server 2012 – Implementing,Managing, Performance

3 Comments

Today we will  learn a new feature in SQL server 2012 – Sequences

What is Sequence and Why do we need in a Relational database?

Sequence is a linear progression of any numeric value with a specified interval , well sequence is not a new concept for RDMS , Oracle has been using this feature for a while and SQL server manges to use the same concept with IDENTITY column and on the other side Oracle does not have the IDENTITY column feature

So what is the advantage of using Sequence Number over Identity columns ?

  • The application requires a number before the insert into the table is made.
  • The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
  • The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
  • The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause’s ORDER BY clause.
  • An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
  • You need to change the specification of the sequence, such as the increment value.

How Sequence works ?

How to create Sequences in SQL Server 2012

create sequence

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How to use sequence

 

 

 

 

 

 

 

 

 

 

 

 

 

Using Sequences

update [dbo].[product]

set ProductID = next value for [dbo].[Masterproductid]

select name,minimum_value,maximum_value,current_value

from sys.sequences

where name = ‘Masterproductid’

select max(ProductID) as productid from dbo.product

TSQL

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 } ]
[ ; ]

Performance sequences

 

 

 

 

 

 

 

 

 

Limitations

  • Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.
  • Uniqueness is not automatically enforced for sequence values. The ability to reuse sequence values is by design. If sequence values in a table are required to be unique, create a unique index on the column. If sequence values in a table are required to be unique throughout a group of tables, create triggers to prevent duplicates caused by update statements or sequence number cycling.
  • The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables. When created with the CACHE option, an unexpected shutdown, such as a power failure, can lose the sequence numbers in the cache.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

code

–@ SQL JUNKEI SHARE
–TABLE TO TEST THE PERFORMANCE OF SEQUENCES
CREATE TABLE [dbo].[SEQUENCEPRODUCT](
[ProductID] [int] NULL,
[Name] [nvarchar](50) NULL,

) ON [PRIMARY]

GO

ALTER TABLE [dbo].

[SEQUENCEPRODUCT] ADD CONSTRAINT [DEFSEQFORPRODUCT1] DEFAULT (NEXT VALUE FOR [MASTERPRODUCTID]) FOR [ProductID]
GO

ALTER SEQUENCE MASTERPRODUCTID

RESTART WITH 1;

–TABLE TO TEST PERFORMNACE OF IDENTITY
CREATE TABLE [dbo].[IDENTPRODUCT](
[ProductID] INT IDENTITY (1,1) ,
[Name] [nvarchar](50) NULL,

) ON [PRIMARY]

GO
CREATE TABLE SOURCEPRODUCT (NAME NVARCHAR(50))
–INSERT TEST DATA

GO
CREATE PROCEDURE INSERTIDENT
AS
BEGIN
INSERT INTO IDENTPRODUCT(NAME)
SELECT NAME FROM SOURCEPRODUCT
END
GO
CREATE PROCEDURE INSERTSEQUENCE
AS
BEGIN
INSERT INTO SEQUENCEPRODUCT(NAME)
SELECT NAME FROM SOURCEPRODUCT
END

SELECT COUNT(*) AS ‘NUMBER OF TEST ROWS’ FROM SOURCEPRODUCT
–4943950
DECLARE @STARTTIME DATETIME = GETDATE()
EXEC INSERTIDENT
SELECT DATEDIFF(SS,@STARTTIME,GETDATE()) AS ‘ENDTIME IDENTITY’
GO
DECLARE @STARTTIME DATETIME = GETDATE()
EXEC INSERTSEQUENCE
SELECT DATEDIFF(SS,@STARTTIME,GETDATE()) ‘ENDTIME SEQUENCE’
GO

— USING IN INSERT STATEMENT
INSERT INTO PRODUCT (PRODUCTID,NAME)

VALUES (NEXT VALUE FOR DBO.MASTERPRODUCTID,’TV’)
–USING IN SELECT WITH ORDER CLAUSE
SELECT NEXT VALUE FOR DBO.MASTERPRODUCTID OVER (ORDER BY NAME) AS ORDERPROD

FROM PRODUCT

–USING IN UPDATE
UPDATE DBO.PRODUCT

SET PRODUCTID = NEXT VALUE FOR DBO.MASTERPRODUCTID
–SEQUENCE IN TRANSACTIONS
—NO EFFECT ON SEQUENCE LIKE IDENTITY
SELECT CURRENT_VALUE FROM SYS.SEQUENCES WHERE NAME = ‘MASTERPRODUCTID’

BEGIN TRAN

INSERT INTO PRODUCT (PRODUCTID,NAME)

VALUES (NEXT VALUE FOR DBO.MASTERPRODUCTID,’TV’)
ROLLBACK TRAN

SELECT CURRENT_VALUE FROM SYS.SEQUENCES WHERE NAME = ‘MASTERPRODUCTID’

SELECT NAME FROM PRODUCT WHERE NAME = ‘TV’
–SQL JUNKIE SHARE

— REPLACING THE IDENTITY WITH SEQUENCES BY MODIFYING THE DEFAULT CONSTRAINT

—-CHECK CURRENT MAX VALUE IN PRODUCTS TABLE

SELECT MAX(PRODUCTID) AS MAXPRODUCTID FROM PRODUCT

— CURRENT VALUE FOR THE SEQUENCE

SELECT CURRENT_VALUE ‘BEFORE INSERT’ FROM SYS.SEQUENCES WHERE NAME =’MASTERPRODUCTID’
–MODIFY THE SEQUENCE TO STRAT FROM 504

ALTER SEQUENCE MASTERPRODUCTID

RESTART WITH 505;

–CHECK THE CURRENT VALUE FOR SEQUENCE

SELECT CURRENT_VALUE FROM SYS.SEQUENCES WHERE NAME =’MASTERPRODUCTID’

— MODIFY THE TABLE FOR DEFAULT CONSTRAINT

ALTER TABLE PRODUCT

ADD CONSTRAINT DEFSEQFORPRODUCT DEFAULT (NEXT VALUE FOR MASTERPRODUCTID) FOR PRODUCTID

–TEST IT BY INSERTING A VALUE
INSERT INTO PRODUCT (NAME)

VALUES (‘FOO’)

–CHECK PRODUCT AND SEQUENCES

SELECT PRODUCTID AS ‘PRODUCT ID AFTER INSERT’ FROM PRODUCT WHERE NAME = ‘FOO’

SELECT CURRENT_VALUE AS ‘CURRENTVALUE AFTER INSERT IN PRODUCT’ FROM SYS.SEQUENCES WHERE NAME =’MASTERPRODUCTID’

Advertisement

THROW statement TSQL 2012

1 Comment

Today,

We will go through the THROW error handling technique which Microsoft is introducing in SQL server 2012

at first we will be looking at RAISERROR () and will compare this with THROW and find out the difference and advantages , disadvantages

Highlights of RAISERROR()

  • RAISERROR allows you to throw an error based on either an error number or a message, and you can define the severity level and state of that error:
    1
    2
    3
    RAISERROR(50001, 16, 1);
    --or
    RAISERROR('Ooops', 16, 1);
  • If you call RAISERROR with an error number, that error number has to exist in sys.messages.
  • You can use error numbers between 13001 and 2147483647 (it cannot be 50000) with RAISERROR.

RAISERROR can return either:

  • A user-defined error message that has been created using the sp_addmessage system stored procedure. These are messages with a message number greater than 50000 that can be viewed in the sys.messages catalog view.
  • A message string specified in the RAISERROR statement.

RAISERROR can also:

  • Assign a specific error number, severity, and state.
  • Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
  • Substitute argument values into the message text, much like the C language printf_s function.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Drawbacks of RAISERROR()

  • One of the biggest draw back of the RAISERROR() is the in ability  of re throwing the error in catch block of TRY-CATCH  error handling technique introduced in SQL SERVER 2005

RAISERROR() in TRY-CATCH

 

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
   we lost whole point of catching with a fair level of complexity in the catch block

XACT_ABORT

As every T-SQL programmer worth his (or her) salt should know, an exception does not roll back a transaction by default

I.e. the following code would cause two rows to be inserted in the table



when using raise error to throw the same error lets see what happens




keeping these things in mind Microsoft has introduced a new technique to replace the RAISERROR()


THROW SYNTAX THROW


[ { error_number | @local_variable }, { message | @local_variable },     { state | @local_variable } ] [ ; ] error_number


Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.


message


Is an string or variable that describes the exception. message is nvarchar(2048).


state


Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.


    

  • The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
    • 

  • If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.
    • 

  • If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be ended.
    • 





Three Main Differences Between RAISERROR and THROW




The following table lists differences between the RAISERROR and THROW statements.










RAISERROR statement
THROW statement




If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.
The error_number parameter does not have to be defined in sys.messages.




The msg_str parameter can contain printf formatting styles.
The message parameter does not accept printf style formatting.




The severity parameter specifies the severity of the exception.
There is no severity parameter. The exception severity is always set to 16.










 as we can see this how error can be re thrown in try catch block  One good feature that I like in RAISERROR is Custom formatting the error message as shown below the simple one with substitution arguments please use the below link http://msdn.microsoft.com/en-us/library/ms178592(v=SQL.110).aspx  


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


References:


http://msdn.microsoft.com/en-us/library/ms186788(v=SQL.110).aspx


http://msdn.microsoft.com/en-us/library/ms177497(v=SQL.105).aspx


http://msdn.microsoft.com/en-us/library/ms189583.aspx