Wednesday, May 8, 2013

SQL Server - Determine Column Uniqueness

This is a quick and dirty stored procedure that one can use to determine if a column or set of columns is a candidate for a primary key or unique index. The procedure takes a table name and a concatenated list of columns to be checked. Non-char columns must be CAST as VARCHAR.

Source

-------------------------------------------------------------------------------
-- Date:   November 10, 2010
-- Author: Keith R. Davis
-- Desc:   Shows the percentage of unique values in a column, so that one can
--         determine if it is a candidate for indexing.  Non-char columns must
--         be CAST. FOR DEVELOPMENT USE ONLY!
--         
--         Example:
--
--         EXEC UtilShowUniqueness 'MyTable', 'Col1 + CAST(Col3 AS varchar)'
--
-- Param:  @TableName - name of the table to analyze
--         @Columns   - columns to check for uniqueness
-- Return: Nothing
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[UtilShowUniqueness](
 @TableName sysname, 
 @Columns sysname
 )
AS

BEGIN
 SET NOCOUNT ON

 EXEC (
  'SELECT ((SELECT COUNT(DISTINCT ' + @Columns + ') 
   FROM ' + @TableName + ') * 100) /
   (SELECT rows FROM sysindexes 
  WHERE id = OBJECT_ID(''' + @TableName + ''') AND indid < 2) AS percent_unique'
 )
END;

Example

In this example we will check if the numeric columns DayNumInMonth and YearMonth create an unique combination.

EXEC UtilShowUniqueness 'DimDate', 'CAST(DayNumInMonth AS varchar) + CAST(YearMonth AS VARCHAR)'

Result

percent_unique
--------------
100

This stored procedure has been tested on SQL Server 2008 and 2008 R2, your mileage may vary on other versions of SQL Server.

Monday, May 6, 2013

Site Facelift

I don't have much to report other than if you are reading this, you are seeing a number a changes I have made to the site since the last time you were here:

  • New header
  • Image slider
  • Static home page
  • Theme color tweaks

I hope it makes discovering what we do easier. The blog is still intact with all the technical articles you expect. I am not sure if I am satisfied with the changes, so leave any comments and/or suggestions you might have here. Thank you for your feedback!