The T-SQL Features in SQL Server 2012 You Can’t Ignore

With the introduction of SQL Server 2012 data developers have new ways to interact with their databases. This session will review the powerful new analytic windows functions, new ways to generate numeric sequences and new ways to page the results of our queries. Other features that will be discussed are improvements in error handling and new parsing and concatenating features.

Transcript

1. Richie Rump @Jorriss http://jorriss.net
3. • Sequences • Data Paging • Analytic Window Functions • Conversion Functions • Logical Functions • Date/Time Functions • String Functions
4. • New construct to generate numeric sequences. • Ability to set min and max values • Cycle will set the current value to the min value when the max value is reached • Future values can be cached minimizing disk IO.
5. • Returns metadata for the supplied SQL statement. • Useful for determining column/database information for a SQL statement or Stored Procedure.
6. • If set to 0, no information is returned. • If set to 1, each query is analyzed as if it includes a FOR BROWSE option on the query. This will return base table names as the source column information. • If set to 2, each query is analyzed as if it would be used in preparing or executing a cursor. This will return view names as source column information.
7. • Allows the changing of column names and data types in the resultset of a stored procedure. • Yes, you can change column names when you call a stored procedure. • Note: Cannot be used with an INSERT..EXEC
8. • Allows for server side paging. • Not data pages (8K of awesome) but paging like on a web page. • OFFSET AND FETCH are arguments of the ORDER BY clause • OFFSET – Number of rows to skip • FETCH – Number of rows to return
9. • LAG • LEAD • FIRST_VALUE • LAST_VALUE • PERCENT_RANK • CUME_DIST • PERCENTILE_CONT • PERCENTILE_DISC
10. • The OVER clause determines the partitions and order of a rowset before the window function is applied. • WHAT CHU TALKING ABOUT WILLIS?
11. • PARTITON BY – Similar to GROUP BY but only applies to the window function and not the entire query. • ORDER BY – Specifies the order of the rows in the partition
12. • No longer need to use a self-join or CTE. • LAG: Access data from previous rows in the result set. • LEAD: Access data from future rows in the result set.
13. • FIRST_VALUE: Retrieves the first value in a partition. • LAST_VALUE: Retrieves the last value in a partition.
14. • Calculates a relative rank of a row. • (RANK() – 1) / (Total Rows – 1)
15. • Calculates the percentage of values less than or equal to the current value in the group. • COUNT(*) OVER (ORDER BY Col1) / Total Count
16. • Calculates a percentile value • Will interpolate the appropriate value • Can use to find the median – PERCENTILE_CONT(0.5) • CONT stands for continuous
17. • Calculates a percentile value • Like PERCENTILE_CONT but will select a value that exists in the set. • Can use to find the median – PERCENTILE_DISC(0.5) • DISC stands for discrete distribution
18. • Attempts to parse a string and convert it to a specified datatype. • Can only convert to a number or datetime • Uses the .Net CLR
19. • Like PARSE but if an error occurs returns a NULL. • Still uses the .Net CLR.
20. • Attempts to cast a value into a specified data type. Returns NULL if CONVERT fails.
21. • Immediate If aka Inline If • Takes a boolean expression and returns one of two values. • Shorthand for CASE. • Has the same limitations as CASE – Can only be nested to 10 levels
22. • Returns a value from a list based on a specified index. • If the specified index is not in the list NULL is returned. • Returns the data type based on data type precedence.
23. • Returns last date of a specified month. • Can specify a month_to_add argument to increment or decrement result.
24. • DATEFROMPARTS ( year, month, day) • DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) • DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) • DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) • SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) • TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
25. • Does what it says…concatenates strings together. • NULLs are automatically converted to empty strings. • Can pass other data types for concatenation.
26. • Simplifies the string formatting of dates and other data types. • No more memorizing numeric predefined format values. • Returns a string. • Uses .Net CLR
27. • Reduces the need to use RAISERROR in TRY/CATCH blocks. • Can provide custom error messages. • Always uses severity level 16.
29. Richie Rump @Jorriss jorriss@gmail.com http://jorriss.net http://slideshare.net/Jorriss
31. Denali T-SQL at a Glance - New and Enhanced Functions http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-tsql-glance-enhanced- functions-140785 T-SQL Improvements in SQL Server 2012 http://www.infoq.com/news/2012/03/T-SQL-2012 SQL SERVER – Denali – 14 New Functions – A Quick Guide http://blog.sqlauthority.com/2011/09/21/sql-server-denali-14-new-functions-a-quick- guide/ Performance Test of New Date Manipulation Functions (SQL Spackle) http://www.sqlservercentral.com/articles/Performance/89505/ Keyboard Shortcuts in Visual Studio 2012. Need to import settings. http://www.mssqltips.com/sqlservertip/2625/improvement-to-keyboard-shortcuts-in-sql- server-2012-management-studio/
32. • MSDN: http://msdn.microsoft.com/en- us/library/ff878091.aspx • Sequence: Why they aren’t just for surrogate keys: http://blog.infoadvisors.com/index.php/2012/02/16 /new-in-sql-server-2012-sequences-why-they-arent- just-for-surrogate-keys/ • Sequence in 2012: http://www.sql-server- performance.com/2011/sequence-sql-server-2011/ • Create Sequence: http://www.dbadiaries.com/new- t-sql-features-in-sql-server-2012-create-sequence/
33. • MSDN: http://msdn.microsoft.com/en- us/library/ff878602.aspx • http://blog.sqlauthority.com/2012/03/31 /sql-server-sp_describe_first_result_set- new-system-stored-procedure-in-sql- server-2012/
34. • MSDN: http://msdn.microsoft.com/en- us/library/ms188332.aspx • WITH RESULT SETS http://www.dbadiaries.com/new-t-sql- features-in-sql-server-2012-with-result- sets/
35. • MSDN: http://msdn.microsoft.com/en- us/library/ms188385.aspx • TSQL 2012 – OFFSET and FETCH: http://stevestedman.com/2012/04/tsql-2012- offset-and-fetch/ • OFFSET and FETCH: http://www.dbadiaries.com/new-t-sql- features-in-sql-server-2012-offset-and-fetch/
36. • LAG - MSDN: http://msdn.microsoft.com/en- us/library/hh231256 • LEAD - MSDN: http://msdn.microsoft.com/en- us/library/hh213125
37. • FIRST_VALUE - MSDN: http://msdn.microsoft.com/en- us/library/hh213018 • LAST_VALUE - MSDN: http://msdn.microsoft.com/en- us/library/hh231517
38. • MSDN: http://msdn.microsoft.com/en- us/library/hh213573.aspx • Introduction to PERCENT_RANK(): http://blog.sqlauthority.com/2011/11/18/sql- server-introduction-to-percent_rank-analytic- functions-introduced-in-sql-server-2012/
39. • MSDN: http://technet.microsoft.com/en- us/library/hh231078.aspx • Introduction to CUME_DIST: http://blog.sqlauthority.com/2011/11/08/sql- server-introduction-to-cume_dist-analytic- functions-introduced-in-sql-server-2012/
40. • PERCENTILE_CONT - MSDN: http://msdn.microsoft.com/en- us/library/hh231473.aspx • Introduction to PERCENTILE_CONT: http://blog.sqlauthority.com/2011/11/20/sql- server-introduction-to-percentile_cont-analytic- functions-introduced-in-sql-server-2012/ • What exactly does PERCENTILE_CONT do? http://www.sqlskills.com/BLOGS/BOBB/post/Wh at-exactly-does-PERCENTILE_CONT-do- anyhow.aspx
41. • PERCENTILE_DISC - MSDN: http://msdn.microsoft.com/en- us/library/hh231327 • Introduction to PERCENTILE_DISC: http://blog.sqlauthority.com/2011/11/22/sql- server-introduction-to-percentile_disc- analytic-functions-introduced-in-sql-server- 2012/
42. • Difference between PARSE(), TRY_PARSE(), TRY_CONVERT(): http://blog.sqlauthority.com/2011/09/09/sql-server- denali-conversion-function-difference-between-parse- try_parse-try_convert/ • PARSE - MSDN: http://msdn.microsoft.com/en- us/library/hh213316.aspx • TRY_PARSE - MSDN: http://msdn.microsoft.com/en- us/library/hh974669 • TRY_CONVERT - MSDN: http://msdn.microsoft.com/en-us/library/hh230993
43. • MSDN: http://msdn.microsoft.com/en- us/library/hh213574.aspx
44. • MSDN: http://msdn.microsoft.com/en- us/library/hh213019 • CHOOSE() – A Quick Introduction: http://blog.sqlauthority.com/2011/09/11/sql- server-denali-logical-function-choose-a-quick- introduction/
45. • MSDN: http://msdn.microsoft.com/en- us/library/hh213020.aspx • EOMONTH() – A Quick Introduction: http://blog.sqlauthority.com/2011/09/20/sql- server-denali-date-and-time-functions- eomonth-a-quick-introduction/
46. • A Quick Introduction: http://blog.sqlauthority.com/2011/09/19/sql- server-denali-date-and-time-functions- datefromparts-datetimefromparts- datetime2fromparts-timefromparts- smalldatetimefromparts- datetimeoffsetfromparts-a-quick-introduc/
47. • MSDN: http://msdn.microsoft.com/en- us/library/hh231515.aspx
48. • MSDN: http://msdn.microsoft.com/en- us/library/hh213505.aspx • New SQL Server Function to Format Dates: http://www.mssqltips.com/sqlservertip/2655/ne w-sql-server-function-to-format- dates/?utm_source=dailynewsletter&utm_mediu m=email&utm_content=headline&utm_campaig n=2012427 • Custom Numeric Formats - http://msdn.microsoft.com/en- US/library/0c899ak8.aspx#SpecifierD
49. • http://www.dbadiaries.com/new-t-sql- features-in-sql-server-2012-throw/