Richie Rump @Jorriss http://jorriss.net 

No content

• Sequences • Data Paging • Analytic Window Functions • Conversion Functions • Logical Functions • Date/Time Functions • String Functions 

• 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. 

• Returns metadata for the supplied SQL statement. • Useful for determining column/database information for a SQL statement or Stored Procedure. 

• 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. 

• 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 

• 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 

• LAG • LEAD • FIRST_VALUE • LAST_VALUE • PERCENT_RANK • CUME_DIST • PERCENTILE_CONT • PERCENTILE_DISC 

• The OVER clause determines the partitions and order of a rowset before the window function is applied. • WHAT CHU TALKING ABOUT WILLIS? 

• 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 

• 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. 

• FIRST_VALUE: Retrieves the first value in a partition. • LAST_VALUE: Retrieves the last value in a partition. 

• Calculates a relative rank of a row. • (RANK() – 1) / (Total Rows – 1) 

• Calculates the percentage of values less than or equal to the current value in the group. • COUNT(*) OVER (ORDER BY Col1) / Total Count 

• Calculates a percentile value • Will interpolate the appropriate value • Can use to find the median – PERCENTILE_CONT(0.5) • CONT stands for continuous 

• 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 

• Attempts to parse a string and convert it to a specified datatype. • Can only convert to a number or datetime • Uses the .Net CLR 

• Like PARSE but if an error occurs returns a NULL. • Still uses the .Net CLR. 

• Attempts to cast a value into a specified data type. Returns NULL if CONVERT fails. 

• 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 

• 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. 

• Returns last date of a specified month. • Can specify a month_to_add argument to increment or decrement result. 

• 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 ) 

• Does what it says…concatenates strings together. • NULLs are automatically converted to empty strings. • Can pass other data types for concatenation. 

• Simplifies the string formatting of dates and other data types. • No more memorizing numeric predefined format values. • Returns a string. • Uses .Net CLR 

• Reduces the need to use RAISERROR in TRY/CATCH blocks. • Can provide custom error messages. • Always uses severity level 16. 

No content

Richie Rump @Jorriss [email protected] http://jorriss.net http://slideshare.net/Jorriss 

No content

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/ 

• 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/ 

• 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/ 

• 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/ 

• 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/ 

• LAG - MSDN: http://msdn.microsoft.com/en- us/library/hh231256 • LEAD - MSDN: http://msdn.microsoft.com/en- us/library/hh213125 

• FIRST_VALUE - MSDN: http://msdn.microsoft.com/en- us/library/hh213018 • LAST_VALUE - MSDN: http://msdn.microsoft.com/en- us/library/hh231517 

• 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/ 

• 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/ 

• 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 

• 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/ 

• 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 

• MSDN: http://msdn.microsoft.com/en- us/library/hh213574.aspx 

• 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/ 

• 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/ 

• 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/ 

• MSDN: http://msdn.microsoft.com/en- us/library/hh231515.aspx 

• 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 

• http://www.dbadiaries.com/new-t-sql- features-in-sql-server-2012-throw/