Month: March 2012

Working on my Presentations for SQL Connections Fall 2012

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

Posted in Classes, SQL 2012, Steve Tagged with:

Risk Assessment Matrix for SQL Server Upgrades

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

Posted in Steve Tagged with: , , ,

Cumulative Distribution Function (CDF) – Analyzing the Roll of Dice with TSQL

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

Posted in Analytic TSQL, SQL 2012, TSQL 2012 Tagged with: , , , ,

More TSQL 2012 Analytics CUME_DIST – Cumulative Distribution

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

Posted in Analytic TSQL, SQL 2012, TSQL 2012 Tagged with:


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

Posted in Classes, SQL 2012, TSQL 2012 Tagged with: ,

Using the OUTPUT Clause in TSQL for Auditing

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

Posted in TSQL Tagged with: , , , ,

More TSQL Analytic Functions – PERCENT_RANK

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

Posted in SQL 2012, TSQL 2012 Tagged with: , ,

TSQL Analytic Functions LEAD and LAG

SQL Server 2012 introduces 8 new analytic functions. This post will cover 2 of them LEAD and LAG, which can be used to reference a value in the row before or the row after the current row. If you have

Posted in SQL 2012, TSQL 2012 Tagged with: , , , , , , ,


In a previous article I covered the usage of ROWS PRECEDING and FOLLOWING in the over clause.  For this example I am going to use the same database and tables that I created in the previous example to show ROWS UNBOUNDED

Posted in SQL 2012, TSQL, TSQL 2012 Tagged with: , , ,

Accessing the registry from TSQL on SQL Server 2012 and 2008R2

With SQL Server versions 2008R2 and 2012, you can access the registry to get the settings for the current instance of SQL Server.  Here is how it works using the dynamic management view (DMV) called dm_server_registry. From there you get get

Posted in TSQL, TSQL 2012 Tagged with: , , ,