By Posted on Posted in Analytic TSQL, SQL 2012, TSQL 2012 No Comments Tagged with

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 »

By Posted on Posted in Classes, SQL 2012, TSQL 2012 No Comments 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 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 »

By Posted on Posted in TSQL No Comments Tagged with , , , ,

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 …

Using the OUTPUT Clause in TSQL for Auditing Read more »

By Posted on Posted in SQL 2012, TSQL 2012 No Comments Tagged with , ,

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 …

More TSQL Analytic Functions – PERCENT_RANK Read more »

By Posted on Posted in SQL 2012, TSQL 2012 No Comments Tagged with , , , , , , ,

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 read my OVER clause articles you will recognize the REVENUE table. Just a table listing department revenue year over year …

TSQL Analytic Functions LEAD and LAG Read more »

By Posted on Posted in SQL 2012, TSQL, TSQL 2012 2 Comments 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 both PRECEEDING and FOLLOWING. One of the new features available in TSQL in SQL Server 2012 is the ROWS UNBOUNDED …

Transact SQL OVER Clause – ROWS UNBOUNDED PRECEDING or FOLLOWING Read more »

By Posted on Posted in TSQL, TSQL 2012 No Comments Tagged with , , ,

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 at all sorts of information about the current SQL Server instance.  Give dm_server_registry a try.

By Posted on Posted in SQL 2012, TSQL, TSQL 2012 1 Comment Tagged with , ,

First for this example, and a few to follow we need to create the database that we are going to play around in. Next a review of the OVER clause as it is support prior to SQL Server 2012. Which produces the following output. Which gives you the average revenue and sum or the revenue for each department.  In the …

ROWS PRECEDING and FOLLOWING in TSQL 2012 Read more »

By Posted on Posted in SQL 2012 2 Comments Tagged with , ,

One of the new Dynamic Management Views introduced in SQL Server 2012 is the dm_server_services which returns info on the services running that are associated with SQL Server. select * from sys.dm_server_services;   Give it a try.   Looking this over, the first thing that comes to mind is to to use this to be able to detect if the SQL Server Agent Process is …

New DMV in SQL Server 2012 sys.dm_server_services Read more »

By Posted on Posted in SQL 2012, SSRS No Comments Tagged with , , ,

There is no more Business Intelligence Development Studio (BIDS) in SQL Server 2012, instead it has been replaced with the SQL Server Data Tools that uses the core of Visual Studio 2010, with the Report Builder and Report Designer built in to the new Visual Studio. The UI has changed colors, and looks a bit sharper, but overall you have …

No more Business Intelligence Development Studio in SQL 2012 Read more »