SQL Server 2012 – TSQL
I have to confess I hadn’t paid too much attention to the changes to T-SQL in SQL Server 2012, and it was only a question at NextGenUG in Abingdon last night that prompted me to have a look. It turns out there are some really useful new commands, which I could certainly have done with when I was allowed near production code. So here’s what I found:
TRY_CONVERT() . If I had a penny for every time I feel foul of trying to convert to dates or numeric from random string data I had loaded up in my BI projects… Anyway this is better solution than ISNUMERIC() and ISDATE() and typically looks like this
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result
returns 2010-12-31 00:00:00.0000000
SELECT TRY_CONVERT(datetime2, '11/31/2010') AS Result
return NULL
so the same syntax as Convert, and you don’t have to do an initial test but you’ll want to include additional processing to handle the NULL when TRY_CONVERT() fails.There is also a new PARSE() & TRY_PARSE() which deal with converting dates and currency formats across different locales
FORMAT()
differs from CAST() and CONVERT() by providing localised output from localised input from a different locale e.g.
with the Current date is 15/11/2011 with my machine set to a locale of EN_GB
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result
returns 11/15/2011
so the same date but in US format
Note This uses the CLR (like HierarchyID and other newer data types) but it doesn't need to be turned on:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
DATEFROMPARTS() builds a date from separate variables from year month day..
SELECT DATEFROMPARTS ( 2010, 12, 31 )
returns 2010-12-31
This is one of a set of functions to build up dates and times to various formats e.g TIMEFROMPARTS(), DATETIMEFROMPART()CHOOSE() allows you to specify which item to pick in a list of items e.g.
SELECT CHOOSE( 2, 'IT Professional', 'DBA', 'Developer', 'Tester' ) AS Result
returns DBA
IIF() works in the same way as in Excel – IIF(condition, value if true, value if false)
CONCAT () joins strings together to one output e.g.
SELECT CONCAT ( 'DEEP', 'FAT',’’,’FRYER’, NULL, 50 ) AS Result
returns DEEPFAT FRYER50
Note: the output data type will vary according to what is put in and if one of the inputs is NULL then CONCAT() will just ignore the NULL value and join the non NULL values togetherTHROW allows you to raise an error in a TRY.. CATCH block e.g.
THROW 51000, 'we have a problem Houston.', 1;
This is a modification of the ORDER BY clause in a SELECT statement..
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
This allows to you to limit which part of the results are returned based on the ordering clause
SUMMARY
This isn’t an exhaustive list of all this the new T-SQL commands there is other new T-SQL for accessing the other new features in SQL Server 2012 e.g. table valued functions for semantic statistical search, file table, security enhancements etc.
Finally these new commands are all in the current beta of SQL Server 2012 (RC0), if you want to try them out.