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

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'

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

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

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

New DATETIME functions in Denali

New DateTime  Function in Denali

1. EOMONTH
    This will return last day for the month from the date at specified offset
   
    syntax
   
    EOMONTH ( start_date [, month_to_add ] )
 

Lag:-New function in sql server denali

LAG

We all have read about LEAD in previous blog . LAG is working opposite of LEAD where we can go backword in dataset.

We can get value from previous subsequent row with given offset value without using a self join or CTE