Practical T-SQL

This is the first in what will be a series of posts on data profiling. This covers one of the simpler profiling tasks, which is profiling a well typed table for the purpose of better understanding the data in the table. This is something I’ve had to do frequently for a variety of reasons, so I’ve developed a relatively simple stored procedure that I use regularly.

In my situation I have to operate on a large number of different databases across multiple instances. What I’ve done is I’ve created a single database called DataStudio, which is copied to all of the instances, that houses a set of data analysis and ETL tools. One of the tool that features prominently in that toolkit is the stored procedure listed below called GetDataStats, which generates a standard profile report on a well typed table, meaning a table for which I believe all of the columns are of an appropriate type.

For tables in which all of the columns are character columns or tables that I know need to be re-factored I have another profiling procedure that seeks to determine the best possible type for columns, which will be the subject of a later post.

I typically use this GetDataStats procedure as a quick and easy first shot at better understanding the content of a table and doing some some general bounds checking above and beyond any bounds checking defined by the schema.

Below is a sample of the output from the procedure as run against a table of stock market index data.

table_name       column_name   column_type   value_count  null_count   null_percent  distinct_count  sum_of             avg_of          top_10_values                                              bottom_10_values
---------------- ------------- ------------- ------------ ------------ ------------- --------------- ------------------ --------------- ---------------------------------------------------------- --------------------------------------------------------
dbo.adj_INDEX    trade_date    datetime      981088       0            0             2666            NULL               NULL            [Jan  1 2001 12:00AM][Jan  2 2001 12:00AM][Jan  3 2001 ... [Mar 22 2011 12:00AM][Mar 21 2011 12:00AM][Mar 18 201...
dbo.adj_INDEX    ticker        varchar       981088       0            0             368             NULL               NULL            [ADR][ADVA][ADVN][ADVQ][AEX][AMZ][ATX][BANK][BDI][BFX]     [ZWI][ZRS][ZOC][ZAX][YIH][XWH][XUO][XUH][XTV][XTC]
dbo.adj_INDEX    high          decimal       760150       220938       0.2251969     267300          2418520957848.34   3181820.637974  [*NULL*][0.0000][0.0050][0.0100][0.0150][0.0200][0.0300... [3174239000.0000][3066776000.0000][3054216000.0000][3...
dbo.adj_INDEX    low           decimal       760150       220938       0.2251969     265535          1144237193618.56   1505444.518789  [*NULL*][-19887.0000][-4.3400][-4.2360][-0.4700][-0.410... [2676176000.0000][2671483000.0000][2632589000.0000][2...
dbo.adj_INDEX    close         decimal       760150       220938       0.2251969     262146          2417073929787.85   3179887.634848  [*NULL*][0.0000][0.0050][0.0100][0.0150][0.0200][0.0250... [3174239000.0000][3066776000.0000][3054216000.0000][3...

As you can see the procedure returns one row for each column in the table. Value_count is the number of rows that have a value other than NULL. Null_count obviously is the number of rows with a NULL value. Null_percent returns the percentage of NULLs as a value between 0 and 1, and the sum_of and avg_of values are only calculated for numeric columns.

Instead of simply showing the minimum and maximum values, I show the top 10 and bottom 10 values (the results have been truncated here for display purposes).

I place the values within brackets in order to more easily show leading and trailing white-space. I return NULL values as the value [*NULL*] because I've come across instances where the literal string "NULL" has existed in tables, which has caused much confusion as one can imagine.

The procedure has three parameters, only two of which are required, a database name and table name. The schema is to be included with the table name the way I have the procedure written. This simplifies the parameters by not requiring a third parameter, and also makes it easy to populate the parameters by dragging and dropping the table name within SSMS. The third, and optional, parameter is @dropresults which by default is set to drop the summary table after the results are returned. A reason not to drop the summary table after returning the results would be if you want to run further queries or processes against the profile data.

EXEC DataStudio.dbo.sp_GetDataStats
  @database ='dbname',
  @tableName ='dbo.table';

The procedure itself is as follows:
CREATE PROCEDURE [dbo].[sp_GetDataStats]
  @database nvarchar(128),-- Database Name
  @tableName nvarchar(128),-- Name of the table to be examined. Include schema in the name
  @dropResults bit= 1 -- 1 = Yes, drop the summary table after returning results | 0 = No, don't drop the summary table after returning results

WITH RECOMPILE
AS-- =============================================
-- Author: R. G. Price
-- Create date: 2012/03/01
-- Description: This procedure profiles a table by creating a summary table, into which summary information
-- is inserted. The procedure uses a cursor to loop through each column in a table performing a series of queries,
-- then inserts the results into the summary table.
-- =============================================

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

BEGIN

  DECLARE @sql nvarchar(max);

  SET @sql =N'IF EXISTS (SELECT * FROM sysobjects WHERE xtype = ''U'' AND name = ''_DataStats'+@database+''') DROP TABLE [_DataStats'+@database+'];';

  EXEC(@sql);

  SET @sql =N'CREATE TABLE [_DataStats'+@database+'] (table_name nvarchar(128),column_name nvarchar(128),column_type nvarchar(128),value_count bigint,null_count bigint,null_percent real,distinct_count bigint,sum_of float, avg_of float, top_10_values nvarchar(max), bottom_10_values nvarchar(max));';

  EXEC(@sql);

  SET @sql =
  N'DECLARE @columnName nvarchar(128),
  @columnType nvarchar(128),
  @iSQL nvarchar(max);

  DECLARE columns_cursor CURSOR FAST_FORWARD
  FOR
  SELECT
    COLUMN_NAME, DATA_TYPE
  FROM
    ['+@database+'].INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_SCHEMA+''.''+TABLE_NAME = '''+@tableName+'''
  ORDER BY
    ORDINAL_POSITION

  OPEN columns_cursor

  FETCH NEXT FROM columns_cursor
  INTO @columnName, @columnType

  WHILE @@FETCH_STATUS = 0
  BEGIN
  RAISERROR(@columnName, 1, 1) WITH NOWAIT;

  SET @iSQL =
  N''DECLARE @count bigint, @distCount bigint, @nullCount bigint, @sumOf float, @avgOf float, @myTop nvarchar(max), @myBottom nvarchar(max);

  SELECT @count = COUNT_BIG(*) FROM ['+@database+'].'+@tableName+' WHERE [''+@columnName+''] IS NOT NULL;
  SELECT @distCount = COUNT_BIG(DISTINCT [''+@columnName+'']) FROM ['+@database+'].'+@tableName+';
  SELECT @nullCount = COUNT_BIG(*) FROM '+@database+'.'+@tableName+' WHERE [''+@columnName+''] IS NULL;

  IF ''''''+@columnType+'''''' IN (''''bigint'''', ''''real'''', ''''float'''', ''''decimal'''', ''''money'''', ''''numeric'''')
  BEGIN
    EXEC sp_executesql N''''SELECT @colSum = SUM([''+@columnName+'']) FROM ['+@database+'].'+@tableName+''''', N''''@colSum float OUTPUT'''', @colSum = @sumOf OUTPUT;

    EXEC sp_executesql N''''SELECT @colAvg = AVG([''+@columnName+'']) FROM ['+@database+'].'+@tableName+' WHERE [''+@columnName+''] != 0'''', N''''@colAvg float OUTPUT'''', @colAvg = @avgOf OUTPUT;
  END
  ELSE IF ''''''+@columnType+'''''' IN (''''tinyint'''', ''''smallint'''', ''''int'''')
  BEGIN
    EXEC sp_executesql N''''SELECT @colSum = SUM(CAST([''+@columnName+''] as bigint)) FROM ['+@database+'].'+@tableName+''''', N''''@colSum float OUTPUT'''', @colSum = @SumOf OUTPUT;

    EXEC sp_executesql N''''SELECT @colAvg = AVG(CAST([''+@columnName+''] as bigint)) FROM ['+@database+'].'+@tableName+' WHERE [''+@columnName+''] != 0'''', N''''@colAvg float OUTPUT'''', @colAvg = @avgOf OUTPUT;
  END

  SET @myTop = '''''''';

  SELECT
    @myTop += ''''[''''+ISNULL(CAST([''+@columnName+''] as varchar(50)), ''''*NULL*'''')+'''']''''
  FROM
    (SELECT DISTINCT TOP 10 [''+@columnName+''] FROM ['+@database+'].'+@tableName+' ORDER BY [''+@columnName+''] ASC) t

  SET @myBottom = '''''''';

  SELECT
    @myBottom += ''''[''''+ISNULL(CAST([''+@columnName+''] as varchar(50)), ''''*NULL*'''')+'''']''''
  FROM
    (SELECT DISTINCT TOP 10 [''+@columnName+''] FROM ['+@database+'].'+@tableName+' ORDER BY [''+@columnName+''] DESC) b

  INSERT INTO [_DataStats'+@database+']

  VALUES('''''+@tableName+''''', ''''''+@columnName+'''''', ''''''+@columnType+'''''', @Count, @NullCount, CASE @NullCount WHEN 0 THEN 0 ELSE CAST(@nullCount as float)/(CAST(@count+@nullCount as float)) END, @distCount, @sumOf, @avgOf, @myTop, @myBottom);'';

  EXEC(@iSQL);

  FETCH NEXT FROM columns_cursor
  INTO @columnName, @columnType;
  END

  CLOSE columns_cursor;
  DEALLOCATE columns_cursor;';

  EXEC(@sql);

  SET @sql ='SELECT * FROM [_DataStats'+@database+'];';

  EXEC(@sql);

  IF @dropResults = 1
  BEGIN
    SET @sql ='DROP TABLE [_DataStats'+@database+'];';

    EXEC(@sql);
  END

  SET NOCOUNT OFF;
END

This procedure can take a while to run. I've run it on tables with hundreds of columns and millions of rows, but it takes several hours to complete on such a table in the environments I've run it in. Never the less, it's still worth it because it's something you can simply kick off to run in the background or over night and it's still faster for assessing the content of a table than hunting and pecking or doing ad hoc analysis. For tables with dozens of columns and  thousands of rows, however, it typically completes in a matter of seconds or minutes.

What this procedure does:

The procedure starts out by creating a "permanent" table into which the summary information will be inserted. I use a permanent table instead of a temporary table or table variable because there are scenarios in which I want to retain the summary information for later use. If there is no need to retain the data then the data is dropped as indicated by the @dropResults parameter.

Dynamic SQL has to be used throughout in order to accommodate being able to run the procedure on any given database and table. You'll notice the addition of bracket delimiters around the database name and column names used in the dynamic SQL. The more technically correct way to do this would be to use the QUOTENAME function since it handles escaping of brackets should they exist in the object name, however it's more complicated to do this and for my use I didn't feel it was worth the trouble since we don't have any objects with brackets in the object name.

In addition, you'll see that the table name is never delimited. This is because I'm using a single parameter for the schema and table name. In order to properly delimit the values I'd have to parse the string and delimit the schema and table name separately. If we had table names with invalid characters in them I'd go to the trouble to do that as the first step in the procedure, but I have not yet had to run this on table with names that include invalid characters so again it's not something I've gone to the trouble to do.

The main body of the procedure actually includes nested dynamic SQL. A cursor is used to loop through each column in the table. The columns are pulled from the INFORMATION_SCHEMA, as opposed to sys system catalog views because INFORMATION_SCHEMA is simpler to use for this purpose, and INFORMATION_SCHEMA is defined by the SQL-92 standard, and thus less likely to change in the future anyway.

The use of a cursor is appropriate in this situation because the processing of each column requires procedural logic.

Inside the cursor loop I begin by using RAISEERROR() WITH NOWAIT to report the name of the column that is currently being processed. The objective of using RAISEERROR is not to throw an error, but rather to report the status of the procedure in real time. The PRINT function can't be used for this purpose because PRINT statements aren't returned until the entire procedure completes running, which would defeat the purpose of a status update. By using RAISEERROR() WITH NOWAIT with a severity and status of 1, the status is reported immediately as the procedure runs without interrupting the flow of the procedure, which let's you know how far along the procedure is. This works even within a TRY...CATCH black as well by the way. One could easily make this status message even more informative if desired by adding a count reporting something like "Processing "Column Name" : (X out of Y)", where X is the ordinal position of the column being processed out of the total count of columns.

Within the cursor loop nested dynamic SQL is created in order to make use of the column variables populated by the cursor (yes it would be nice if SQL Server were object oriented so we wouldn't have to do this type of stuff).

Within the nested dynamic SQL (iSQL for inner SQL) variables are declared to hold the values that will be inserted into the summary table.

You'll notice that sp_executesql is used for the SUM and AVG values. This is because a compile time error will be generated when the dynamic SQL is created for non-numeric columns, even though those columns would never be summed or averaged due to the IF statement which will prevent the SUM and AVG functions from being applied to non-numeric types. In order to prevent the compiler error the SUM and AVG lines have to be performed within yet a third nesting of dynamic SQL, but since variables are scoped within dynamic SQL in order to return the values to the to calling outer scope sp_executesql has to be used with an OUTPUT parameter.

When the dynamic SQL for this step is actually created it looks like the following:

IF 'datetime' IN ('bigint','real','float','decimal','money','numeric')

This of course seems like a silly statement, but this is how we do things in the non-object oriented world. In the object oriented world we would be doing something like if column.type in ()...

The reason for the ELSE statement is that SUM returns a value of the same type that was passed to it, so the the SUM of an int, smallint, or tinyint column exceeds the size of the passed in type, it will cause an error. In other words, if you SUM a column of tinyint (whose valid range is from 0..255) and the SUM is greater than 255, it will raise a run time error. To get around this its best to cast all integers smaller than bigint to a bigint when doing SUMs.

Notice that the AVG being done here is not a true average, because of the WHERE clause, which says WHERE column != 0. I did this because I'm operating on tables with lots of dubious zero values which can cause the averages to be kind of meaningless. The average returned here is the average of non-zero, and obviously non-NULL (automatically eliminated by the AVG function), values.

Lastly the top 10 and bottom 10 values are built as strings by concatenating each of the values together using the += operator. For backwards compatibility with versions of SQL older than 2008 you can simply change this to @myTop = @myTop + column, etc.

The value of each of these variables is then inserted into the summary table.

Upon completion of the cursor the results are selected from the table and the table is dropped if specified by the @dropResults parameter.

So there you have it.

Note that due to the way this procedure is written, you can't run this multiple times against tables in the same database concurrently. I have, however, created another procedure to sequentially run this procedure against all tables in a database.