Sequence Part 1:- New Feature in Denali

Sequences
Sequences are new feature in SQL Server Denali
Those who are aware with Oracle/Postgres are aware with sequence. However better late than never 🙂
MS has added sequence in Deanli

This can help us to solve many problems we are facing with Identity and programmer will have more control on DB.Sequences are not bounded to tables and they are controlled by applications.

Lets see with Example for it

Definition from BOL

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

    A sequence can be defined as any Intger Type
    Bigint is a default option   
   
 START WITH
    The first value from where the sequence starts. The value must be constant

 INCREMENT BY
    The increment value for the sequence. The value must be constant
 MINVALUE
    Minvalue for the sequence
 MAXVALUE
    Maxvalue for the sequence

CYCLE
    The sequence should restart when it reaches maxvalue
    Cycle will restart from minvalue/maxvalue not from start value
   

   
Lets see with an Example for this

CREATE SCHEMA test

Creating a sequence

CREATE sequence test.seq

AS int

start WITH 1

increment BY 1

create table testtable

(id int,

name varchar(10))

INSERT INTO testtable

VALUES

(next value for test.seq, ‘a’) ,

(next value FOR test.seq, ‘b’) ,

(next value FOR test.seq, ‘c’)

SELECT *

FROM   testtable 

Now we want to set the sequnce number to 10

Altering a sequence

ALTERsequence  test.seq

restart WITH 10

INSERT INTO testtable

VALUES

(next value for test.seq, ‘d’) ,

(next value FOR test.seq, ‘e’) ,

(next value FOR test.seq, ‘f’)

SELECT *

FROM   testtable

Use of Minvalue, Maxvalue, Cycle  


lets cycle the sequence  with range of values

CREATE sequence test.seqnew

AS int

start WITH 1

increment BY 1

minvalue 1

maxvalue 3

cycle

TRUNCATE TABLE testtable

INSERT INTO testtable

VALUES

(next value for test.seqnew, ‘a’) ,

(next value FOR test.seqnew, ‘b’) ,

(next value FOR test.seqnew, ‘c’) ,

(next value for test.seqnew, ‘d’) ,

(next value FOR test.seqnew, ‘e’) ,

(next value FOR test.seqnew, ‘f’)

   

SELECT *

FROM   testtable

    

    

Here you can see the seuqence has range from1 to 3 and after than it restarts with 1.

One more way to get primary key details of table

One more way to get primary key details of table

EXEC sp_pkeys
    ‘SalesOrderDetail’,
    ‘Sales’

‘Sales.SalesOrderDetail’

Exec  sp_pkeys
@table_name,
@table_owner,
@table_qualifier

@table_name – Table Name
@table_owner – Table Owner Name Default is Null
@table_qualifier – DB Name where proc is running  Default is Null

Lets see an Exampple

EXEC sp_pkeys   ‘SalesOrderDetail’,   ‘Sales’

Execute with Result Sets new option in Denali

Execute
We all are aware of Execute. Most common use is execute a procedure.
In Denali a new option is added define Metadata for result set using WITH RESULT SETS options.
Lets look into it

[ { EXEC | EXECUTE } ]
    {
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var }
        [ [ @parameter = ] { value
                           | @variable [ OUTPUT ]
                           | [ DEFAULT ]
                           }
        ]
      [ ,…n ]
      [ WITH [ ,…n ] ]
    }
[;]

::=
{
        RECOMPILE
    | { RESULT SETS UNDEFINED }
    | { RESULT SETS NONE }
    | { RESULT SETS ( [,…n ] ) }
}
::=
{
    (
         { column_name
           data_type
         [ COLLATE collation_name ]
         [ NULL | NOT NULL ] }
         [,…n ]
    )
    | AS OBJECT
        [ db_name . [ schema_name ] . | schema_name . ]
        {table_name | view_name | table_valued_function_name }
    | AS TYPE [ schema_name.]table_type_name
    | AS FOR XML
}

As we can see there are 3 options in Result Sets

1. RESULT SETS UNDEFINED
Its a default option when result sets are not provided.
Its return result as it is return by the procedure

2.RESULT SETS NONE
No result is returned. The procedure should not return any result sets else it will give error

3.RESULT SETS

As we can define it multiple ways

We can define column name, data type,collation for the result set while executing the query.
We can give object name whose definition will be used to return result sets
We can get result set as table type and XML

Lets see with Example all one by one

Creating a procedure

CREATE PROC Getempdetail @persontype VARCHAR(50)

AS

    BEGIN

        SELECTTitle,

               FirstName,

               LastName

        FROM   Person.Person

        WHERE  PersonType LIKE@persontype

    END

Without result set

EXECGetempdetail ‘%EM%’

                        

                    

                    

Using Result Set                        

EXECGetempdetail ‘%EM%’

WITH result sets

(

                  (Emp_Title varchar(250),

             Emp_FirstName varchar(250),

             Emp_LastName   varchar(250))

)

Column names are as per the defined result set

 

Using Object

CREATE TABLE test

    (

         Emp_Title     VARCHAR(250),

         Emp_FirstName VARCHAR(250),

         Emp_LastName  VARCHAR(250)

    )

EXECGetempdetail ‘%EM%’

WITH result sets ( AS object[AdventureWorks2008R2].[dbo].[test])

Column names are as per the defined  object

Using Multiple Result Set

CREATE PROC Getempdetailanddesignation @persontype  VARCHAR(50),

                                       @designation VARCHAR(250)

AS

    BEGIN

        SELECTTitle,

               FirstName,

               LastName

        FROM   Person.Person

        WHERE  PersonType LIKE@persontype

        SELECTh.JobTitle,

               h.HireDate

        FROM   Person.Person p

               JOINHumanResources.Employee h

                   ONh.BusinessEntityID =p.BusinessEntityID

        WHERE  PersonType LIKE@persontype

               ANDJobTitle LIKE @designation

    END

EXECGetempdetailanddesignation ‘%EM%’,‘%Manager%’

WITH result sets

(

                  (Emp_Title varchar(250),

             Emp_FirstName varchar(250),

             Emp_LastName   varchar(250))

             ,

             (JobTitle varchar(250),

             HireDate Date)

)

Multiple result sets are defined by comma separator.

TRY_CONVERT:- New function in Denali

TRY_CONVERT

Tries to convert data type to a specified data type and if fails returns Null

Syntax

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type:- data_type in which we want to convert data
expression:- expression which will be converted to speicified data_type
style:-specified style, A integer number in which data will be converted. 
Its same as we specified in convert

Lets see example to understand it

If conversion is possible it will be return converted value else null

SELECTTry_convert (int, ‘1’),

       Try_convert (int, ‘a1’)

If conversion is not allowed it will return error

SELECTTry_convert(image, 123)

use of style

SELECTTry_convert (Varchar(50), Getdate(), 9)

THROW new function in Denali

THROW

It raise an exception and will transfer the code to CATCH block,while using TRY CATCH block
If not used in TRY CATCH then batch will be aborted.

SYNTAX

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

Error_number :- must be greater than 50000 , can be any one. Can be a variable also

Message :-  A custom message , dont need to be a printf like raiserror. Can be a variable also

State:- A value between 0 and 255. Indicates state of a statement associated. I am not sure how to use it till now. 🙂

1. If not used in try catch then batch will be aborted
2 If used in try then parameters should be passed
3 No need of parameter if used in CATCH
4.In TRY block the previous satement before catch must end with ;

A better idea can be achieved with Example

CREATE TABLEt

    (

         id   INT PRIMARY KEY,

         data VARCHAR(4)

    )

We will raise an error by inserting string lengh more than specified length in table

 BEGIN TRY

    INSERT INTO t

    VALUES      ( 1,

                  ‘data’)

    INSERT INTO t                — THIS WILL FAIL

    VALUES      (2,

                 ‘data1234’)

END TRY

BEGIN CATECH

    SELECT Error_message()

END CATCH

Now we will raise same error by using throw statement

 BEGIN TRY

    INSERT INTO t                — THIS WILL FAIL

    VALUES      ( 3,

                  ‘data1234’)

END TRY

BEGIN CATCH

    THROW

END CATCH

Now we want to issue throw in TRY block to generate custom error. Code after throw will be aborted

BEGIN TRY

   INSERT INTO t

   VALUES      ( 3,

                 ‘data’);

   THROW 242432,‘Why are you inserting data ?’,1

   INSERT INTO t                — THIS WILL ABORTED

   VALUES      ( 4,

                   ‘data’);

END TRY

BEGIN CATCH

    THROW

END CATCH

SELECT * FROM T