Freigeben über

SQL Server 2012 – TSQL

  • Artikel

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;

OFFSET..FETCH

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.

Comments

  • Anonymous
    November 16, 2011
    Thanks Andrew, handy

  • Anonymous
    November 20, 2011
    Thanks for the overview. I couldn't read some parts of the article, because they were "chopped off" by the right column of the blog. So I had to copy paste everything into Word. Maybe this is browser related?

  • Anonymous
    November 25, 2011
    Koen, I had to redit a bit of this post which upset the formatting hopefully you can read it properly now Andrew

  • Anonymous
    December 05, 2011
    Very useful overview, I had been wondering what was new in there for T-SQL, it never seems to get as much attention as other areas.