Denali – Day 19: Programmability Feature -T-Sql

ORDER BY .. OFFSET and FETCH :mysql LIMIT (srno ):

OFFSET is a location of the rows to be fetched and FETCH is how many number of rows to be fetched. This options should be specified with ORDER BY clause

By default

OFFSET is 0 means from the first row and FETCH is all rows, we can explicitly specify these values.

Eg.

Select a, b, c from alphbets1 order by d offset 5 fetch next 10 rows only;

This is skip first 5 rows and return next 10 rows.

This is similar to “TOP (N) .

But OFFSET..FETCH is used the paging solution. Which is fast and recommended.

THROW (try .. catch) error # >50000:

Till now we could use Try…Catch for exception handling, now similar to C++ language you could now we could “THROW” and error with try..catch…throw

Throw could raise an custom error which starts from error number 50001 onwards.

Syntax:

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

Eg.

…..

Throw 50001,”my custom error msg’,1;

This is little bit similar to “RAISEERROR” which is mostly to just raise an error but requires msg_id to be in sys.messages and specific format and need to provide security parameter. In throw it is not required security and generally it is with “try..catch ‘construct.

OVER clause:

Now OVER clause has an additional support of Windows functions.

Applied to:

• Ranking function
  
• Aggregate Funcaiton
  
• Analytic function
  
• NEXT Value FOR Function.
  

Syntax:

OVER ( [ < PARTITION BY..> ]

[ < ORDER BY..> ]

[ < ROW OR RANGE ..> ]

)

NEW FUNCTIONS:

Conversion functions

• PARSE (Transact-SQL)
  

  This is a translation function,
  

  PARSE ( string_value AS data_type [ USING culture ])
  

Eg. Select PARSE(getdate() as datetime2 USING ‘en-US’) as today1

• TRY_CONVERT (Transact-SQL)
  

  This is a extension to CONVERT statement to try for conversion if it fails instead of giving error it will return NULL.
  

  Eg. Select TRY_CONVERT(datetime2 ‘vinay’)
  

   

  Output:
  

  NULL
  

   

• TRY_PARSE (Transact-SQL)
  

Similar to PARSE with exception handling

Date and time functions:

FROMPARTS: means you can provide a parameter in parts to make the date/time output its revise of datepart() function.

So just provide the separate part of values to date? Function and it will return the conceding value of it. So if we see the syntax and example of the function you will come to know the purpose of the function.

• DATEFROMPARTS (Transact-SQL):
  

This function is for DATE concatenation

Syntax:

DATEFROMPARTS ( year,month,day)

This doesnot contains time values, here time part will be zero

Eg. Select DATEFROMPARTS ( 2012,05,19 )

Output:

2012-05-19

• DATETIMEFROMPARTS (Transact-SQL)
  

This function is for DATETIME concatenation

Syntax:

DATETIMEFROMPARTS ( year, month, day, hr, min, sec, mili)

This contains time values as well.

Eg. Select DATETIMEFROMPARTS ( 2012,05,19, 18, 00, 00, 0 )

Output:

2012-05-19 18:00:00:000

• DATETIME2FROMPARTS (Transact-SQL)
  

This function is for DATETIME2 concatenation

Syntax:

DATETIME2FROMPARTS ( year, month, day, hr, min, sec, fraction precision)

This contains time values as well.

Eg. Select DATETIME2FROMPARTS ( 2012,05,19, 18, 00, 00, 0, 0 )

Output:

2012-05-19 18:00:00:000000

• DATETIMEOFFSETFROMPARTS (Transact-SQL)
  

This function is for DATETIMEOFFSET concatenation

Syntax:

DATETIMEOFFSETFROMPARTS ( year, month, day, hr, min, sec, fraction,hr_offset,minute_offset,precision)

This contains time values as well.

Eg. Select DATETIMEOFFSETFROMPARTS ( 2012,05,19, 18, 00, 12, 0, 0 )

Output:

2012-05-19 18:00:00:000000 +12:00

• SMALLDATETIMEFROMPARTS (Transact-SQL)
  

This function is for SMALLDATETIME concatenation

Syntax:

SMALLDATETIMEFROMPARTS ( year, month, day, hr, min, sec)

Eg. Select SMALLDATETIMEFROMPARTS ( 2012, 05, 19, 18, 30 )

Output:

2012-05-19 18:30:00

• TIMEFROMPARTS (Transact-SQL)
  

This function is for TIME concatenation

Syntax:

TIMEFROMPARTS (hr, min, sec, fraction precision)

Eg. Select SMALLDATETIMEFROMPARTS ( 18, 30, 50, 0, 0 )

Output:

18:30:50

• EOMONTH (Transact-SQL) :
  

Returns end of month of that date,

EOMONTH( DATE,[month to add])

Eg. Select eomonth(getdate()) –today is 19/05/2012

Output

31/05/2012

Eg. Select eomonth(getdate(),2) –today is 19/05/2012 next month would be 06

Output

30/06/2012

This is very helpful function for developers to monthend report and others.

Logical functions:

• CHOOSE (Transact-SQL):
  

Choose the particular value from the list of array,

Synatex:

CHOOSE (location, Array of values…)

Eg.

Select CHOOSE(2, ‘Vinay’,'Rajesh’,'Sanjay’)

Output

Rajesh

• IIF (Transact-SQL) :
  

It is similar to C++ IIF function.

Syntax:

IIF(Condition,True,False)

Eg.

Select (1<2,’True’,'False’)

Output

True

String functions

• CONCAT (Transact-SQL)
  

  This is a concatenation string function for the provided string parameters.
  

  Syntax:
  

  CONCAT(‘string1′, ‘string2′…)
  

  Eg.
  

  Select CONCAT (‘Vinay’,’ Thakur’)
  

   

Output

Vinay Thakur

• FORMAT (Transact-SQL)
  

  This is the same old format function type, but it can also include optional culture as well.
  

   

  syntax:
  

  FORMAT ( value,format [, culture] )
  

   

   

  http://www.databasejournal.com/features/mssql/new-built-in-conversion-functions-in-sql-server-2012.html