So far after playing around with SQL Server 2012 there are many new features to SQL. Overall I think my favorites are in the Analytic Functions category. Here is my breakdown of the top 10 transact SQL enhancements to SQL Server 2012. 10. New SEQUENCE object 9. CHOOSE function 8. Analytics – Analytic Functions – PERCENT_RANK 7. Analytics PERCENTILE_DISC and PERCENTILE_CONT 6. …
One of the common questions that I get asked after my Unleashing CTE Presentation is “Can we use CTEs in SSRS reports?” The answer is YES, Common Table Expressions work great in SSRS reports, one of my favorite ways of cleaning up some of those big ugly SSRS report queries is to use CTEs.
For years I have heard the question of how can I do an IIF in TSQL? Honestly I never thought it would be that useful since the CASE statement is easy enough to use, but after seeing it in TSQL 2012, I can safely say I like it. The answer used to be, just use the CASE statement, and the …
Over the last week I have pitched 8 presentations for SQL Connections in Vegas at the end of October 2012. Hoping that 3 of my presentations get accepted. Here is the list so far. Using the New Analytic Functions in SQL Server 2012 Exploring the TSQL Enhancements in SQL Server 2012 Unleashing Common Table Expressions in SQL Server Using SSRS …
Working on my Presentations for SQL Connections Fall 2012 Read more »
A couple years ago in my Incident Safety Officer class with the fire department we studied risk assessment for any incident that the fire department may respond to. Since that time I have used a similar risk assessment matrix for SQL Server upgrades, and other big SQL Server maintenance tasks. The risk assessment matrix as it is used by the …
After the last post on Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST(), I realized that although I showed how to use it, I didn’t really explain what it means, or when to use it. That is where this example comes in. First lets take an example that generates simulated dice rolls. What are the odds …
Cumulative Distribution Function (CDF) – Analyzing the Roll of Dice with TSQL Read more »
Continuing on the TSQL 2012 Analytic Series now on to the CUME_DIST function SQL Server 2012 introduces another new analytic function. The Cumulative Distribution Function CUME_DIST() refers to the probability that the value of a random variable falls within a specified range. CUME_DIST is the function that maps values to their percentile rank in a distribution. CUME_DIST function calculates the possibility …
More TSQL 2012 Analytics CUME_DIST – Cumulative Distribution Read more »
Like the other new Analytic functions, PERCENTILE_DISC and PERCENTILE_CONT require the use of the OVER clause. For this example I will be using almost the same revenue table in the sample database that I set up for the LEAD and LAG posting earlier in the week, and the PERCENT_RANK posting yesterday, just a few values changed to show the differences between these …
More TSQL 2012 Analytics PERCENTILE_DISC and PERCENTILE_CONT Read more »
The OUTPUT clause is often times underappreciated by the TSQL programmer. There are many really interesting things that you can do with the OUTPUT clause, from inserting to two tables with one statement or to replace the SCOPE_IDENTITY option. For this posting I am looking into using the OUTPUT clause for auditing. By auditing in this example, I specifically mean …
Percent rank is defined as the number of values that are the same or less than the current value divided by one less than the number of values. Percent rank is different than PERCENTILE, stay tuned for PERCENTILE_DISC and PERCENTILE_CONT which are different from PERCENT_RANK. For this example I will be using the same revenue table in the sample database …