TSQL 2012 some new features

Here are some new features of TSQL 2012 demonstrated :
/**********
3 types of casting
**********/SELECT TRY_PARSE('4000' as int) as CInt SELECT TRY_PARSE('4000a' as int) as CInt-- though TRY_CAST is not a recognized built in function name, it still worksSELECT TRY_CAST('4000' as int) as CInt SELECT TRY_CAST('4000a' as int) as CIntSELECT try_convert(int, '4000') as CIntSELECT try_convert(int, '4000a') as CInt　

Get End Of Month, given a date
******//*****SELECT EOMONTH(getdate());SELECT EOMONTH('2012-02-01');　

Log function
*********//********-- Log to the base e also referred as lnSELECT Log(10) ;-- Log to the base 10 or any other numberSELECT Log(10,10) ;
Concatination
*********//********-- used instead of "+"SELECT Concat('Abcd', ' , ', 'efgh', ' - ', 'ijkl', ' + ', 'mnop', '....');-- major benifit of using Concat over "+"SELECT 'Abcd' + ' , ' + 'efgh' + ' - ' + 'ijkl' + null + ' + ' + 'mnop' + '....' ;-- Returns NULLSELECT Concat('Abcd', ' , ', 'efgh', ' - ', 'ijkl', null,' + ', 'mnop', '....');-- returns result eleminating NULL　
　
　/*********
Culture sensitive date format
-- first param : calue
second param : d for date , t for time, '' for date time
third param culture info
for all the culture info, refer to
http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo(VS.80).aspx
**********/select FORMAT(getdate(),'d','en-US')select FORMAT(getdate(),'d','hi')select FORMAT(getdate(),'d','en-GB')select FORMAT(getdate(),'d','kok')select FORMAT(getdate(),'d','sa-IN')select FORMAT(getdate(),'d','ta')　select format( 199.991, '$0.00', 'en-GB');

Pagination in SQL Server 2012

Pagination in SQL Server 2012 is implemented using “OFFSET X ROWS”, followed by “FETCH NEXT Y ROWS ONLY” . you can replace X and Y with integer variables as code given below.

DECLARE @offset INT; DECLARE @rows INT;  SET @offset = 300;  SET @rows = 15; SELECT * FROM Person.Person ORDER BY BusinessEntityID OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY

The above example will fetch next 15 rows represented by @rows, starting from offset 300 represented by @offset of BusinessEntityId.

Enjoy!