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 »

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 »

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 »

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.

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 »

Part of SQL Server running queries is that once a query is analyzed, parsed and compiled, that compiled plan is kept in memory so that it can be quickly re-used in the future. If you are using proper parameterized queries, most of your queries will get kept around and re-used saving lots of CPU and memory on SQL Server. If …

How big is your procedure cache? Read more »

Here is a script that I created to get the size of all of the databases on one SQL Server. Generally I stay away from temp tables, especially global temp tables, but I didn’t see a good way to do this without them. CREATE TABLE ##alldatabasesizes   (      dbname    VARCHAR(1024),      type_desc VARCHAR(1024),      name      VARCHAR(1024),      size      INTEGER   ); EXECUTE Sp_msforeachdb ‘INSERT INTO ##AllDatabaseSizes  SELECT db_name() as dbName, type_desc,  name, size FROM [?].sys.database_files’ SELECT * FROM   ##alldatabasesizes; DROP TABLE ##alldatabasesizes

Working on a new report for the SQL Server Health reports, I needed to display the amount of free disk space on a SQL Server. EXEC MASTER..Xp_fixeddrives; Which was useful if I just wanted to look, but I needed to use the results in a query, and I didn’t want to put the results into a temp table, so here is …

Determining free disk space with TSQL Read more »

As part of my planning for the SQL Saturday Presentation in Vancouver I am creating an hour long presentation on Common Table Expressions. The easiest way to do a recursive query in SQL server is to use a recursive CTE (Common Table Expression). What is a Common Table Expression in SQL Server? Similar to the ease of a temporary table …

Recursive CTE’s Read more »

Download the Thanksgiving query sample code here. I thought it would be fun to put together a query to get us in the spirit of Thanksgiving dinner tonight. This query is using techniques from another recent posting on CSV formatting query output.   Here is my Thanksgiving gift to you, the Thanksgiving day query.   Don’t copy and paste, your browser …

A Fun Thanksgiving Day Query. Read more »