Auditing Stored Procedure Use in 2005

Auditing Stored Procedure Use in 2005

TL/DR: Generate your own list of extinct stored procedures, or create a frequency map of stored procedure calls.

Another blog post and a long delay since my last one, so my apologies to regular readers (if I have any, of course! :-) ).  If this isn’t your first visit, I hope you found value in my previous posts.  Now, I’ve dreamt up something else.  Today’s topic is how to get a list of stored procedures in your database that simply aren’t used any more (or conversely, are used, and you need a frequency map of how often, by whom, etc.)

Some background: My current shop has around 3000 stored procedures spread over two dozen databases.  I inherited most of these, and they came with no documentation or description of any kind.  Formatting, comments, structured development, even naming conventions were largely absent.  Sadly, nobody knew what ran what – or, more accurately, those who did, left.

So I created the following which assisted me in identifying those stored procedures in my architecture that were simply no longer used.  That, to paraphrase John Cleese, were ex-stored procedures.  They were no more.  They were, in fact, extinct.

Follow and modify the steps below to suit you.  If you have any suggestions for improvement, I’d be glad to hear them – a day where I don’t learn something new is a day wasted.

Step 1:  Set up the server-side trace. Define the trace as follows, or use Profiler and create your own.  You’ll need to capture all SP: Starting and RPC: Starting events, with TextData and DatabaseName columns as a minimum.  I’ve got some extra columns here so I can mess with the data at a later date.

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 50000

exec @rc =

sp_trace_create @TraceID output, 0, N'<output_path>\file', @maxfilesize, NULL

if (@rc != 0) goto error

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 11, 1, @on

exec sp_trace_setevent @TraceID, 11, 14, @on

exec sp_trace_setevent @TraceID, 11, 35, @on

exec sp_trace_setevent @TraceID, 11, 12, @on

exec sp_trace_setevent @TraceID, 11, 60, @on

exec sp_trace_setevent @TraceID, 42, 1, @on

exec sp_trace_setevent @TraceID, 42, 14, @on

exec sp_trace_setevent @TraceID, 42, 35, @on

exec sp_trace_setevent @TraceID, 42, 12, @on

exec sp_trace_setevent @TraceID, 42, 60, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

-- display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

go

Step 2:  When ready, start the trace.

exec sp_trace_setstatus @TraceID, 1

Step 3:  Collect the trace information for as long as you wish.  I recommend 24 hours as a minimum.  To get a true picture of SP usage, you would run this trace for around a week, uninterrupted, to capture all scheduled jobs in that time-frame.  Be careful that you know about jobs with a longer interval, and that the planned run doesn’t coincide with other affecting work, such as planned downtime.

The size of the output of the trace is proportional to the SP call load on the database, so plan for enough disk space.  For me, 24 hours of tracing an average of 210 RPC and local SP calls/sec resulted in 23GB of trace file.

Step 4:  You can monitor what’s going on in sys.traces by querying any column.  However I’ve used a bit of mathematics to make the output more meaningful.  E.g.

DECLARE @starttime DATETIME, @now DATETIME, @delta BIGINT

SET @starttime = ( SELECT start_time FROM sys.traces WHERE status = 1 )

SET @now = GETDATE()

SET @delta = DATEDIFF(ss,@starttime,@now)

select      st.start_time,

            @now [time_now],

            st.file_position, st.event_count, @delta [seconds_elapsed],

            ((24 * 60 * 60) - @delta ) [seconds_left],

            CAST((CAST(ROUND((CAST([file_position] AS DECIMAL(18,2)) / CAST(@delta AS DECIMAL(18,2))),2) AS DECIMAL(18,2)) / 1024768 * 60) AS DECIMAL(18,4)) [MB_per_min],

            CAST((CAST(ROUND((CAST([file_position] AS DECIMAL(18,2)) / CAST(@delta AS DECIMAL(18,2))),2) AS DECIMAL(18,2)) / 1024768 * 60 * 60) AS DECIMAL(18,4)) [MB/hr],

            CAST((CAST(event_count AS DECIMAL(18,2)) / CAST(@delta AS DECIMAL(18,2))) AS DECIMAL(18,2)) [avg_events_per_sec],

            CAST((CAST(@delta AS DECIMAL(18,2)) / ((24 * 60 * 60)) * 100) AS DECIMAL(18,3)) [trace_%_complete],

            (CAST((CAST(ROUND((CAST([file_position] AS DECIMAL(18,2)) / CAST(@delta AS DECIMAL(18,2))),2) AS DECIMAL(18,2)) / 1024768 * 60) AS DECIMAL(18,4)) * 60 * 24) [est_total_file_size_MB]

           

from sys.traces st

This code can be simplified – I think I’ve gotten a bit CAST-happy – so feel free to amend to suit your purpose.  This will output useful columns, such as the # of events so far, the seconds elapsed since starting, the seconds to go to a predefined target, % complete, and a forecast of how much disk space will be required (relies on reasonably linear load pattern).

Step 5:  When you’re finished, stop your trace and remove the server-side definition.

exec sp_trace_setstatus @id = 2, @status = 2

exec sp_trace_setstatus @id = 2, @status = 0

Step 6:  Now, you need to put your trace information into a table, so it can be queried.  This is straightforward.  My work setup is (sadly) 2005 Standard Edition at the moment, but if you’re on 2008+ then use Extended Events rather than fn_trace_gettable, since the latter is deprecated.

CREATE TABLE SANDBOX.dbo.spAuditTraceData ( DatabaseName NVARCHAR(MAX), TextData NVARCHAR(MAX) )

INSERT INTO SANDBOX.dbo.spAuditTraceData

      SELECT DatabaseName, TextData

      FROM fn_trace_gettable('F:\del\sp_audit_output.trc',1)     

(Note: this takes a while.  One useful method I’ve found of finding out how long is to use perfmon and measure an average of the Disk Write Bytes/sec counter.  Then knock off a reasonable amount depending on how busy your server is (hopefully you’re doing this on a quiet dev box).  Then take your total file size (your trace output file) and divide this by your average writes.  Then divide again by 60.  The result is how many minutes you can expect to wait for this step to complete.  For me (23GB) – about 40 minutes (yes, I have high I/O contention and a shitty SAN).

Step 7:  Now you need to create your procedure to compare your captured trace data with the stored procedures in your database, and count up how many times each procedure was executed.  Here’s my take on doing this.  I’ve included the SP header so you know how it works.  Hopefully, you’ll be able to modify and improve upon it, particularly as I’m using two nested CURSORs (for the many-to-many comparisons), which is hardly efficient.  However, like my aging rustbucket of a Ford Escort, it works.

In the code below, I’m using the database ‘SANDBOX_DEV’ and tweaked the parameters to suit the environment.  You will need to do the same.

ALTER PROCEDURE dbo.FindDeadSprocs

      ( @truncateExistingData BIT = 1, @database_name VARCHAR(100) )

AS BEGIN

--------------------------------------------------------------------------------

--  SP:     SANDBOX_DEV.dbo.FindDeadSprocs

--  Author: Derek Colley

--  Updated:      04/09/2012 by Derek Colley.

--

--  Purpose:      Will return the count of times a sproc has been called.

--                      Separates out into two output tables.

--                      Useful for identifying extinct procedures in a database.

--

--  Inputs:       Trace data loaded into SANDBOX_DEV.dbo.spAuditTraceData

--                      in format ( [database_id] BIGINT, [text_data] VARCHAR(MAX) )

--

--  Outputs:      SANDBOX_DEV.dbo.deadSPs

--                      SANDBOX_DEV.dbo.aliveSPs (includes [count] column).

--

--    Parameters:       @truncateExistingData (BIT) = 1

--                            @database_name VARCHAR(100) -- no default, mandatory.

--------------------------------------------------------------------------------

SET NOCOUNT ON

-- get a list of all stored procedures in non-system databases into a temp table var

DECLARE @t1_94875234 TABLE (

      [uid] BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,

      [database_name] VARCHAR(100), [schema_name] VARCHAR(MAX),         [proc_name] VARCHAR(MAX),

      [concat_string] AS ( [schema_name] + '.' + [proc_name] ),

      [count] BIGINT DEFAULT 0 NULL

      )

DECLARE @sql NVARCHAR(MAX)

SET @sql=N'

IF ''?'' NOT IN (''MASTER'',''MODEL'',''MSDB'',''TEMPDB'')

BEGIN

      SELECT      ''?'' AS [database_name], SCHEMA_NAME(p.[schema_id]) [schema_name],      p.[name], 0

      FROM  ?.sys.procedures p WITH (NOLOCK)

      WHERE p.type_desc = ''SQL_STORED_PROCEDURE''

END

'

INSERT INTO @t1_94875234

      EXEC sp_msforeachdb @command1 = @sql

-- target for trace data

IF NOT EXISTS(SELECT [name] FROM SANDBOX_DEV.sys.tables WHERE [name] = 'spAuditTraceData') BEGIN

      CREATE TABLE SANDBOX_DEV.dbo.spAuditTraceData (

            database_id INT, text_data VARCHAR(MAX) ) END

ELSE BEGIN

      TRUNCATE TABLE SANDBOX_DEV.dbo.spAuditTraceData END

-- sample data, remove for real.  I included this for testing the SP.

INSERT INTO SANDBOX_DEV.dbo.spAuditTraceData

      SELECT 5, 'EXEC dbo.BuildPrincipalsMap'

      UNION ALL

      SELECT 5, 'EXEC dbo.showJobStats'

      UNION ALL

      SELECT 5, 'EXEC dbo.showJobStats'

      UNION ALL

      SELECT 5, 'EXEC dbo.RebuildIndexes'

      UNION ALL

      SELECT 6, 'EXEC dbo.sp_index_defrag' -- this shouldn't show in results, wrong DBID

      UNION ALL

      SELECT 5, 'EXEC dbo.whatalotofbollocks' -- nor should this, right DB but not a valid SP

-- compare trace data against procedure definition data, add a notch for every match, dependent on given DBID

DECLARE @currentDBID INT, @textdata VARCHAR(MAX), @procdata VARCHAR(MAX), @doesmatch BIT, @puid BIGINT

SET @currentDBID = ( SELECT d.[database_id] FROM sys.databases d WHERE d.[name] = @database_name )  -- put the current DB in here

DECLARE cur_ForEachCall CURSOR LOCAL FAST_FORWARD

FOR ( SELECT text_data FROM SANDBOX_DEV.dbo.spAuditTraceData WHERE database_id = @currentDBID )

OPEN cur_ForEachCall

FETCH NEXT FROM cur_ForEachCall INTO @textdata

WHILE @@FETCH_STATUS = 0

BEGIN

      DECLARE cur_ForEachProc CURSOR LOCAL FAST_FORWARD

      FOR ( SELECT [uid], concat_string FROM @t1_94875234 t INNER JOIN sys.databases d ON t.database_name = d.[name]

            WHERE d.[database_id] = @currentDBID )

      OPEN cur_ForEachProc

      FETCH NEXT FROM cur_ForEachProc INTO @puid, @procdata

      WHILE @@FETCH_STATUS = 0

      BEGIN

            SET @doesmatch = CASE   WHEN CHARINDEX(@procdata,@textdata,1) IS NOT NULL

                                                AND CHARINDEX(@procdata,@textdata,1) <> 0 THEN 1 ELSE 0 END

            IF    @doesmatch = 1

            BEGIN

                  UPDATE @t1_94875234 SET [count] = [count] + 1 WHERE [uid] = @puid

            END

            FETCH NEXT FROM cur_ForEachProc INTO @puid, @procdata

      END

      CLOSE cur_ForEachProc

      DEALLOCATE cur_ForEachProc

      FETCH NEXT FROM cur_ForEachCall INTO @textdata

END

CLOSE cur_ForEachCall

DEALLOCATE cur_ForEachCall

IF NOT EXISTS(SELECT [name] FROM SANDBOX_DEV.sys.tables WHERE [name] = 'deadSPs') BEGIN

      CREATE TABLE SANDBOX_DEV.dbo.deadSPs ( database_name VARCHAR(100), [schema_name] VARCHAR(100), proc_name VARCHAR(MAX) ) END

ELSE BEGIN

      IF @truncateExistingData = 1 BEGIN

            TRUNCATE TABLE SANDBOX_DEV.dbo.deadSPs END END

INSERT INTO SANDBOX_DEV.dbo.deadSPs

      SELECT database_name, [schema_name], [proc_name] FROM @t1_94875234 WHERE [count] = 0 AND database_name = @database_name

IF NOT EXISTS(SELECT [name] FROM SANDBOX_DEV.sys.tables WHERE [name] = 'aliveSPs') BEGIN

      CREATE TABLE SANDBOX_DEV.dbo.aliveSPs ( database_name VARCHAR(100), [schema_name] VARCHAR(100), proc_name VARCHAR(MAX), [count] BIGINT ) END

ELSE BEGIN

      IF @truncateExistingData = 1 BEGIN

            TRUNCATE TABLE SANDBOX_DEV.dbo.aliveSPs END END

INSERT INTO SANDBOX_DEV.dbo.aliveSPs

      SELECT database_name, [schema_name], [proc_name], [count] FROM @t1_94875234 WHERE [count] <> 0 AND database_name = @database_name

PRINT 'Comparison has finished.  Check dbo.deadSPs and dbo.aliveSPs for results.'

END

Step 8:  Using my sample data (6 ‘calls’ in my trace table) I get results like the following:

‘Comparison has finished.  Check dbo.deadSPs and dbo.aliveSPs for results.’

When we query the tables dbo.deadSPs and dbo.aliveSPs, we get (YMMV):

dbo.deadSPs

Dbo.aliveSPs

Now, you can see how easy it is to generate a list of stored procedures that have been in use in the last X minutes, days or hours.  You can use the dbo.deadSPs and dbo.aliveSPs with your own filters to generate your lists.

If you have found this useful / found problems with the scripts / want further clarification / fume at my shameless use of RBAR (delete as appropriate), please feel free to leave a comment below.

Until next time…

Del

Bug - Rebuilding temp indexes on-the-fly

Bug - Rebuilding temp indexes on-the-fly

Recently I was working on a query that used temporary tables (#this) extensively to shuffle data around in preparation for some large INSERT batches.  I didn't code up the query originally, but was looking for ways to make it more efficient.  After examining the way in which the temp tables were being built and populated, I decided to try adding an index, populating the temp tables then rebuilding the index to make the tables read-efficient.  When the SP got to the stage where these temp tables were SELECTed from to build the INSERT statement, table reads would then be replaced by a clustered index scan.
Here's a simplified example of part of the original code:

 SELECT a.col1, a.col2, b.col1, b.col2, b.col3, c.col1
 INTO #temp1
 FROM tableA a  INNER JOIN tableB b ON a.id = b.id
   INNER JOIN tableC c ON a.id = c.id
 WHERE a.dateField BETWEEN @startDate AND @endDate
 -- returns 500,000 rows.  @startDate and @endDate are passed-in parameters.
 -- There's a few of these builds into #tempX tables going on.

 INSERT INTO finalTable (valA, valB, valC, valD, valE, valF, valG)
  SELECT x.col1, x.col2, x.col3, y.col1, y.col2, z.col1, z.col2
  FROM #temp1 x  INNER JOIN #temp2 y ON x.col2 = y.col2
    LEFT JOIN #temp3 z ON y.col4 = z.col
 -- Example query that's a bit over-complex and ridiculous.
 -- Complicated in real life as the code is couched in dynamic SQL, passing in table names.
 -- And there are multiple JOINs and external dependencies i.e. JOINs on tables in linked servers.

To make a start on optimising this, I threw in some reindexing, since the execution plan showed table scans of epic proportions when doing the INSERT.  I put an index on the column with unique values, to create a clustered index (with PK).  I then created another non-clustered index INCLUDing the columns referenced by the SELECT:

 CREATE CLUSTERED INDEX ix_temp1 ON #temp1 (col1);
 CREATE NON-CLUSTERED INDEX ix_temp1_nc ON #temp1 (col1) INCLUDE (col2, col3);

No problems there.  Examining the execution plan again, the clustered index seek was still there but performance of the query (execution time) was still dire.
I realised I was creating the indexes on empty tables, before they were populated.  So the indexes were as useful as an ashtray on a motorbike:

I put this into the code after the population:

 ALTER INDEX ix_temp1 ON #temp1 REBUILD WITH (ONLINE=OFF);
 ALTER INDEX ix_temp1_nc ON #temp1 REBUILD WITH (ONLINE=OFF);

And here's the interesting bit:

 Msg 608, Level 16, State 1, Procedure sp_MyProcedure, Line 149 No catalog entry found for partition ID 72059596684984320 in database 2. The metadata is inconsistent. Run DBCC  CHECKDB to check for a metadata corruption.
Metadata corruption?  Wtf?  A quick run through of DBCC CHECKDB on the affected databases, and on TEMPDB, showed no corruption whatsoever.

So I looked into it, and here's what I found.  There's a thread on SQLServerCentral that Paul Randal and Gail Shaw both contributed to, discussing this very issue.  It seems that rebuilding indexes on the fly inside stored procedures on the version of SQL Server I'm using - 2005 - cannot be done.  The contributor to the original article posted this as a bug to Microsoft, and Paul Randal confirmed it to be a bug (he and his team wrote most of the indexing functionality in SQL 2005). For those interested, the thread is here -> http://www.sqlservercentral.com/Forums/Topic770808-149-1.aspx

Microsoft have stated that they do not intend to provide a hotfix for this issue, but it will be fixed in 2008 onwards.

The way in which I got around this was to use permanent tables and rebuild the indexes on these each time, instead of using TEMPDB.  The index changes are then written to the (permanent) datafiles rather than the temporary page allocations of TEMPDB. 

Additionally, TEMPDB doesn't get hit as much (TEMPDB SGAM contention is a real bottleneck in my system). 

For more info on indexing, there's a great series of technical articles here -> http://www.sqlservercentral.com/articles/Indexing/68439/


 '

 

DELETE vs. TRUNCATE with IDENTITY - A Note of Caution

A quick post about the differences between DELETE and TRUNCATE when dealing with IDENTITY columns, in response to a recent question from a colleague.

Let's suppose we have the following table:

 CREATE TABLE TestIdentityValues (
  uid  INT IDENTITY (1,1) PRIMARY KEY NOT NULL,
  data  VARCHAR(MAX) )

Now we populate the table with some sample data:

 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 1')
 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 2')
 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 3')

Now we SELECT the data out of the table.  As you'd expect, the IDENTITY column [uid] has values 1, 2 and 3.

 UID DATA
 --- ----
 1 Test # 1
 2 Test # 2
 3 Test # 3

Let's say we need to remove all the data from the table.  In real life, this could be because the data is a staging table, or tied to a temporal dataset, or is a temporary structure only.

 TRUNCATE TABLE TestIdentityValues;

Now let's re-populate the table with the next three test values. 

 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 4')
 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 5')
 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 6')

Here's the result set:

 UID DATA
 --- ----
 1 Test # 4
 2 Test # 5
 3 Test # 6

Notice what's happened?  The IDENTITY column has 'reset' itself.  When the TRUNCATE occurred, the records were removed from last to first.  Hence, with an empty table, the next available IDENTITY value is 1, since the order of removal was 3, 2, 1.

Now let's re-test this theory with DELETE.

 DELETE FROM TestIdentityValues;

The table is now empty. Let's populate it:

 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 7')
 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 8')
 INSERT INTO TestIdentityValues (data)
 VALUES ('Test # 9')

As the table is empty, we should get UID values 1, 2, 3, right?  Wrong.

 UID DATA
 --- ----
 4 Test # 7
 5 Test # 8
 6 Test # 9

The DELETE has deleted the existing records in-place, and doesn't 'reset' the IDENTITY values.  Instead, new, unused IDENTITY values are assigned.

Be careful when using IDENTITY.  If you are using a staging table, for example, that logs advertisement clicks on your website, or traffic, or some other high-volume statistic, and you use IDENTITY with a DELETE when the data has been processed, you may find yourself running out of values.  IDENTITY is limited to the maximum values of the datatype associated with it - INT or BIGINT.  The maximum value of an INT column is 2.1bn - sounds like a lot, but it's very little in this day and age.  Consider how many page hits Amazon gets in a month, perhaps.

However DELETE isn't always bad.  If you're also using the IDENTITY column as a FK on another table, you'll find that a TRUNCATE will ruin your constraints, if you even get it past the parser. 

Choose your method carefully.  Where possible, IDENTITY columns should not be directly INSERTed into (you can do it with explicit INSERT syntax and SET IDENTITY_INSERT ON).  Ideally, of course, you'd have a real PK and not a surrogate.

Visit my website:  http://www.derekcolley.co.uk/ for SQL Server database consultancy.

 

De-duplication and Normalisation - A Practical Example

De-duplication and Normalisation - A Practical Example

My post today is about getting a distinct set of values from a table where you also want to pull back other, unrelated column data which may or may not be distinct, and about normalising your data into a better table set to avoid this problem.
Let's say, for example, you have the following table structure:

CREATE TABLE dbo.library (
         LibraryID INT IDENTITY(100,5) IDENTITY PRIMARY KEY NOT NULL,
         ISBN VARCHAR(12),
         Publisher VARCHAR(50),
         BookTitle VARCHAR(100),
         Author VARCHAR(100) ,
         YearPublished INT )

Let's populate the table using Red Gate SQL Data Generator, 100,000 rows, all unique values except YearPublished.


Now we need to set the table up with some deliberate duplication.  If this was a real production DB you might find multiple books in the table with different LibraryID values.  To simulate this I will run the following query:

SELECT TOP 20 PERCENT * INTO #tempLibraryTable FROM dbo.library;
INSERT INTO dbo.library (ISBN, Publisher, BookTitle, Author, YearPublished)
SELECT ISBN, Publisher, BookTitle, Author, YearPublished
FROM #tempLibraryTable;
DROP TABLE #tempLibraryTable;

We now have the library table populated with 20% duplication.  This means 20% of books are entered twice, but with different LibraryIDs.


The question is how best we would select a distinct set of values from this list, based on just one column?  Or, in English, can we get a distinct list of book titles (i.e. devoid of duplicates) but also include information from columns with more than one value for our key column (duplicate LibraryIDs, for example)?  Because this is a one-to-many relationship, we will only be able to get the first LibraryID value for each duplicate book - any others must be discarded, otherwise the query becomes a list including duplicates, not excluding them.


At first glance we might think SELECT DISTINCT.  However SELECT DISTINCT is inadequate, since the DISTINCT keyword applies a distinct filter across the whole row, not just one column.  So, doing a SELECT DISTINCT on the dataset above returns all 120,000 rows since the LibraryID (unique identity int) is unique (distinct) in each case, distorting the 'uniqueness' of the row as a whole.


Here's my first solution, stripping away the LibraryID column to leave a heap, de-duplicating the heap, and re-inserting the data into the original table:

SET IDENTITY_INSERT dbo.Library ON
SELECT ISBN, Publisher, BookTitle, Author, YearPublished
INTO #tempHoldingTable
FROM dbo.Library
SELECT * INTO dbo.Library_Backup FROM dbo.Library
TRUNCATE TABLE dbo.Library
SET IDENTITY_INSERT dbo.Library OFF
INSERT INTO dbo.Library
SELECT DISTINCT *
FROM #tempHoldingTable

What's wrong with this solution?  Nothing, in that it achieves the aims required, however there are three concerns:


 1) Efficiency - This query writes out 120,000 rows to tempdb, then 100,000 rows back again.
 2) New LibraryIDs - the original LibraryIDs are not preserved, new IDENTITY values are assigned.
 3) Uniqueness on other columns - this solution will fail if other column data varies for one key, not just id.


Issue 1) is going to be a problem if this is a regular event, or the number of rows is large (millions), or tempdb is space-constrained or on an inefficient volume (RAID 5).  Issue 2) is more serious, since it's quite feasible that LibraryID is a foreign key for some other table.  Breaking this constraint and changing the key-value pair of ID/BookTitle would destroy referential integrity.  Issue 3) complicates matters if, e.g. we have multiple rows showing multiple YearPublished for one BookTitle, or multiple ISBNs.


What if we simply selected one row for each subset of duplicate rows? I.e. here is our row info:

LibraryID	ISBN	Publisher	BookTitle	Author	YearPublished
125	143223534	Little, Brown	The Complete Pie Guide	J. Hoffman	1999
160	143223534	Little, Brown	The Complete Pie Guide	J. Hoffman	1999
255	143223534	Little, Brown	The Complete Pie Guide	J. Hoffman	1999

We could do:

SELECT MIN(libraryID), lib.ISBN, lib.Publisher, lib.BookTitle, lib.Author, lib.YearPublished
FROM dbo.Library lib INNER JOIN (
SELECT BookTitle FROM dbo.Library ) AS books
ON lib.BookTitle = books.BookTitle
GROUP BY lib.ISBN, lib.Publisher, lib.BookTitle, lib.Author, lib.YearPublished

This is a bit better, although we are removing potentially valuable information if other columns than LibraryID contain varying data, we are achieving our aim of streamlining the data set.

CAUTION:  This is NOT a good idea if you need to keep all of the data!  For example, using the solution above will remove all duplicate records where the book title is repeated, taking the lowest LibraryID as the record to keep.  This is not what you want if you need to record a row for e.g. multiple imprints of the same book (multiple YearPublished, ISBN).

Another way, using a non-recursive CTE:

;WITH getBooks(BookTitle) AS
( SELECT BookTitle
 FROM dbo.Library
)
SELECT  MIN(libraryID), lib.ISBN, lib.Publisher, lib.BookTitle,
  lib.Author, lib.YearPublished
FROM  dbo.Library lib INNER JOIN getBooks books
ON  lib.BookTitle = books.BookTitle
GROUP BY lib.ISBN, lib.Publisher, lib.BookTitle, lib.Author, lib.YearPublished

This achieves the same result, 100,000 rows.  It's worth noting that you can substitute MIN with MAX if you wanted to get, for example, the latest entry with duplicate info.  You can also substitute out the key value (in this case, BookTitle) with any other value.  Instead of stripping out multiple book titles, you may wish to substitute out multiple ISBNs instead.  You can even substitute multiple values if you wanted to strip out all duplicate rows where the book title AND ISBN are duplicated.

Ideally of course we would normalise our data and in the process of doing so, remove duplicates (referential integrity and foreign key constraints simply wouldn't allow them).

The first step on the road to normalisation is to work out the requirements, and the relationships between the data.  I like to do this by thinking about the nature of the data itself, and not just the attributes of it (like length, datatype).

It is clear that in our example libraryID should be unique.  There should be one and only one ISBN per book, and each ISBN is associated with just one book and no more (one-to-one relationship).  There is only one publisher per book, but a publisher can have multiple books (one-to-many relationship).  A book can have one title only, but the same title might apply to multiple books (one-to-many).  There can be multiple authors for a book, and an author can have multiple books (many-to-many).  Finally, a book can be published in only one year (single edition - subsequent editions will vary on ISBN, perhaps publisher) and in one year, multiple books can be published (one-to-many relationship).

It is clear ISBN is a natural primary key, there being only one ISBN per book entity and one book entity per ISBN.  So let's bin the surrogate primary key LibraryID and use ISBN instead.  (For the purposes of this experiment, every book has an ISBN with no NULL values, and ISBNs are integer numbers - no leading zeroes).  By normalising a little, we can add additional information about each column for each book while actually reducing the amount of information in the central table.  Imagine we had this structure (included FK information for easy reference):

CREATE TABLE dbo.library (
ISBN   INT PRIMARY KEY NOT NULL,
PubID  INT FOREIGN KEY REFERENCES dbo.Publishers(PubID) NULL,
BookTitle VARCHAR(MAX) NOT NULL,
AuthorID INT FOREIGN KEY REFERENCES dbo.Authors(AuthorID) NOT NULL,
YearPublished INT NULL )
CREATE TABLE dbo.Publishers (
PubID  INT PRIMARY KEY NOT NULL,
PubName  VARCHAR(MAX) NOT NULL,
YearEstablished INT NULL,
StreetAddr VARCHAR(MAX) NULL,
logo  VARBINARY(MAX) NULL,
PhoneNumber VARCHAR(50) NULL )
CREATE TABLE dbo.Authors (
AuthorID INT PRIMARY KEY NOT NULL,
AuthorName VARCHAR(MAX) NOT NULL,
Gender  BIT NULL,
DateOfBirth DATETIME NULL )

However, we've immediately got a problem.  What if a book has many authors?  We cannot add in another row for the book with the same ISBN since our primary key constraint prohibits this.  If we list the author as an amalgamation of authors (John Smith and Eddy Jones) we break the first rule of first normal form (1NF).  One solution would be to have a table called 'AuthorGroups' which would list each known combination of authors, pivoting the author names into separate columns, for a maximum of, let's say, five authors:

CREATE TABLE dbo.AuthorGroups (
GroupID  INT PRIMARY KEY NOT NULL,
AuthorOne VARCHAR(100) NOT NULL,
AuthorTwo VARCHAR(100) NULL,
AuthorThree VARCHAR(100) NULL,
AuthorFour VARCHAR(100) NULL,
AuthorFive VARCHAR(100) NULL )

We then amend the dbo.Authors table:

ALTER TABLE dbo.Authors
ALTER COLUMN AuthorName VARCHAR(MAX) NULL
ALTER TABLE dbo.Authors
ADD IsGroup BIT NOT NULL,
ALTER TABLE dbo.Authors
ADD GroupID INT FOREIGN KEY REFERENCES dbo.AuthorGroups(GroupID) NULL

However we then have a lot of wasted NULL values, since in dbo.Authors every AuthorID that is not a singleton (i.e. a group) will have NULL values for AuthorName, Gender and DateOfBirth.  We are also unable to capture that latter information for groups of authors.  We also have duplication if authors are put into the AuthorGroups table where they exist in Authors, and potential duplication since the rows in AuthorGroups will be unique even if they have the same authors in a different column order.  Also, of course, we're limited to just five authors.  On a technical reference manual there are often many authors (SQL Server MVP Deep Dives Volume II, to take a random example, has 7 editors and 61 authors).
Let's approach it a different way.  We'll create the following table instead:

CREATE TABLE dbo.AuthorGroups (
RowID INT IDENTITY PRIMARY KEY NOT NULL,
GroupID INT NOT NULL,
AuthorID INT FOREIGN KEY REFERENCES dbo.Authors(AuthorID) NOT NULL )

And we'll amend dbo.Library by dropping and recreating it as:

CREATE TABLE dbo.library (
ISBN   INT PRIMARY KEY NOT NULL,
PubID  INT FOREIGN KEY REFERENCES dbo.Publishers(PubID) NULL,
BookTitle VARCHAR(MAX) NOT NULL,
AuthorID INT FOREIGN KEY REFERENCES dbo.AuthorGroups(GroupID) NOT NULL,
YearPublished INT NULL )

This is better, since the AuthorGroups table can contain singletons (one GroupID mapped to one AuthorID) for individuals, and have as many author entries as required for each GroupID, which is referenced in our central dbo.Library table.

In terms of migrating the data from our old single table to our new schema, some work is still required.  We must:

1) Identify instances where there are multiple ISBNs in the original dbo.library table and de-duplicate these rows.
2) Select every distinct publisher and put them into dbo.Publishers.
3) Select every distinct author, string-splitting concatenated values, and put them into dbo.Authors.
4) Identify every concatenate author value and create an entry in AuthorGroups for them, cross referencing dbo.Authors.
5) Migrate the bulk of the data into dbo.Library.

Let's take these tasks one at a time.  We can do 1), since this is just a rehash of our CTE above, using ISBN instead of BookTitle.  However let's change it slightly to have multiple conditions on the inner join so that we can avoid picking up rows with incomplete data.  We will need to change the CTE to pick up multiple columns which we are certain are not duplicates for the key ISBN value.  Below, we're using BookTitle for this, since it's a reasonable assumption that there will not be multiple titles for one ISBN nor multiple ISBNs for one title.  This will also prevent us saving rows which have an ISBN with NULL values for the rest of the columns (accidental data entry, perhaps, might cause this). 

Be aware that if you pick columns which do, in fact, have different values for the same key ISBN value, you'll get an error later when attempting to insert the ISBN data into the new, primary-key-constrained table.

;WITH getISBNs(ISBN, BookTitle) AS
( SELECT ISBN, BookTitle
FROM dbo.Library
)
SELECT  MIN(libraryID), lib.ISBN, lib.Publisher, lib.BookTitle,
lib.Author, lib.YearPublished
INTO   dbo.Library_replacement
FROM  dbo.Library lib INNER JOIN getISBNs isbns
ON  lib.ISBN = isbns.ISBN
AND  lib.BookTitle = isbns.BookTitle
GROUP BY lib.ISBN, lib.Publisher, lib.BookTitle, lib.Author, lib.YearPublished

Task 2) is easy too, with a simple SELECT DISTINCT.  However let's modify the Publishers table to have an IDENTITY column as PubID, so that the inserts are straightforward and we can guarantee a unique value.  Then we simply:

SELECT DISTINCT Publisher INTO dbo.Publishers FROM dbo.Library;

For Task 3), we'll again modify the table definition so that AuthorID is an IDENTITY column.  But we have to string split based on the comma delimiter.
 
The logic of the split is as follows -

For each 'author' value in each row returned by SELECT DISTINCT Author FROM dbo.Library:


Determine if it is a composite value (multiple authors).


 If YES -
   a) determine how many elements (authors) are in the composite value.
   b) insert one new row in dbo.Authors for each composite element

 If NO -
   a) Insert the author into the dbo.Authors table.


However, writing an efficient string splitter is rather tricky.  Here, I'm going to use the following tools - CHARINDEX, which searches a string or expression for another string or expression and returns the starting position if found; SUBSTRING, which returns part of a string given starting position and length, and LEN, which returns the number of characters in a given expression.  I'm also going to borrow heavily from Jeff Moden's string splitter code, available here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ which uses a numbers table to efficiently cleave strings by delimiter.

First, I'll extract the author information:

SELECT DISTINCT author INTO #allSingletonAuthors FROM dbo.Library
WHERE author NOT LIKE ('%,%');  -- this will build a list of all single authors.
SELECT DISTINCT author INTO #allMultipleAuthors FROM dbo.Library
WHERE author LIKE ('%,%'); -- this will build a list of all multiple authors.

Now let's use Jeff's string splitter function.  First, we have to define it:

CREATE FUNCTION dbo.DelimitedSplit8K
 --===== Define I/O parameters
         (@pString VARCHAR(8000), @pDelimiter CHAR(1))
 RETURNS TABLE WITH SCHEMABINDING AS
  RETURN
 --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
      -- enough to cover VARCHAR(8000)
   WITH E1(N) AS (
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                 ),                          --10E+1 or 10 rows
        E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
        E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
  cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                      -- for both a performance gain and prevention of accidental "overruns"
                  SELECT 0 UNION ALL
                  SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                 ),
 cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                  SELECT t.N+1
                    FROM cteTally t
                   WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
                 )
 --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
  SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
         Item       = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
    FROM cteStart s
 ;

Now let's call the function, passing in the appropriate value from #allMultipleAuthors:

INSERT INTO dbo.Authors
SELECT split.Item
FROM #allMultipleAuthors ama
CROSS APPLY dbo.DelimitedSplit8k(ama.author,',') split
ORDER BY split.Item ASC

Thanks to the string splitter, each of the delimited values will be placed into the dbo.Authors table.  Note of warning - if this concatenated field includes spaces, these spaces will also be inserted.  To fix this, trim the string before insertion.

Task 4) can be accomplished in much the same way.  dbo.AuthorGroups has the RowID surrogate primary key which is an IDENTITY column.  We'll call the string splitter again.  But this time we're going to add in the GroupID and AuthorID columns using the RANK and DENSE_RANK functions as follows:

SELECT
DENSE_RANK() OVER (ORDER BY ama.author) AS GroupID,
RANK() OVER (ORDER BY ama.author, split.Item) AS AuthorID,
ama.author, split.Item
FROM dbo.authors_temp ama
CROSS APPLY Dbo.DelimitedSplit8k(ama.author,',') split

This gives a result set that has a sequential GroupID without gaps corresponding to each individual multiple of authors in the original dbo.Library, and a sequential AuthorID without gaps corresponding to each individual author, with correlation between each multiple and each individual.
Putting this into some final code to insert the values:

SET IDENTITY_INSERT dbo.Authors ON
INSERT INTO dbo.Authors (authorID, AuthorName, Gender, DateOfBirth)
SELECT  RANK() OVER (ORDER BY ama.author, split.Item) AS AuthorID,
split.Item, NULL, NULL
FROM dbo.authors_temp ama CROSS APPLY dbo.DelimitedSplit8k(ama.author,',') split
SET IDENTITY_INSERT dbo.Authors OFF


INSERT INTO dbo.AuthorGroups
SELECT
DENSE_RANK() OVER (ORDER BY ama.author) AS GroupID,
RANK() OVER (ORDER BY ama.author, split.Item) AS AuthorID,
FROM dbo.authors_temp ama
CROSS APPLY Dbo.DelimitedSplit8k(ama.author,',') split

Let's dispense with the singleton authors by loading them into our dbo.Authors table too:

INSERT INTO dbo.Authors (AuthorName, Gender, DateOfBirth)
SELECT author, NULL, NULL FROM #allSingletonAuthors;
DROP TABLE #allSingletonAuthors;

Now at this point, we have duplicates in our Authors table because there are two data sources for the authors, the multiple rows and the singleton rows.  So we'll de-duplicate this data using exactly the same method as at the beginning of this article.  But once we're done, we'll have to update the AuthorGroups.AuthorID column to match the new, single author IDs.

;WITH theAuthors(author) AS
( SELECT AuthorName
 FROM dbo.Authors
)

SELECT  MAX(AuthorID), AuthorName, Gender, DateOfBirth
INTO  #tempAuthorInfo 
FROM  dbo.Authors au INNER JOIN theAuthors theau
ON  au.AuthorName = theau.AuthorName
GROUP BY au.AuthorID, au.AuthorName, au.Gender, au.DateOfBirth
TRUNCATE TABLE dbo.Authors;
INSERT INTO dbo.Authors
SELECT * FROM #tempAuthorInfo
UPDATE  dbo.AuthorGroups ag
SET  ag.AuthorID = au.AuthorID
FROM dbo.Authors au
WHERE ag.AuthorID = au.AuthorID

Task 5) is the remaining data to migrate.  If you'll recall, our original table structure looked like this:

CREATE TABLE dbo.library (
LibraryID INT IDENTITY(100,5) IDENTITY PRIMARY KEY NOT NULL,
ISBN VARCHAR(12),
Publisher VARCHAR(50),
BookTitle VARCHAR(100),
Author VARCHAR(100) ,
YearPublished INT )

We've populated our dbo.Publishers, dbo.Authors and dbo.AuthorGroups tables.  So now we have to populate our new main table, which looks like this:

CREATE TABLE dbo.library_new (
ISBN   INT PRIMARY KEY NOT NULL,
PubID  INT FOREIGN KEY REFERENCES dbo.Publishers(PubID) NULL,
BookTitle VARCHAR(MAX) NOT NULL,
AuthorID INT FOREIGN KEY REFERENCES dbo.AuthorGroups(GroupID) NOT NULL,
YearPublished INT NULL )

Let's do:

INSERT INTO dbo.library_new
SELECT old.ISBN, pub.PubName, old.BookTitle, auth.GroupID, old.YearPublished
FROM dbo.library old
INNER JOIN dbo.Publishers pub ON old.Publisher = pub.PubName
INNER JOIN dbo.Authors au ON au.AuthorName =  old.Author
INNER JOIN dbo.AuthorGroups auth ON auth.AuthorID = au.AuthorID
ORDER BY ISBN

This populates the data into the new schema.  At this point, for optimisation we might choose to create new non-clustered indexes (clustered indexes on all the primary keys already exist) and write our stored procedure interface for application use.  However the new schema is ready to go and we can archive and drop the old table.

An ERD of the new schema and the complete uninterrupted code of the example given in this article are available for download at http://www.derekcolley.co.uk/.

New Website Launched!

NEWS:  http://www.derekcolley.co.uk/ has been launched!  Providing SQL Server database consultancy and DBA expert support in Manchester, the North West and the UK, I'm pleased to provide specialist help for your bespoke problems.  Need to move from Access to SQL?  Upgrade from SQL Server 6/7 to 2012?  Need help with a complex set of schemas?  Contact me today for a free consultation and a friendly chat about your needs.

Having worked with a variety of Microsoft database software including Access, SQL Server 2000, 2005, 2008, 2008 R2 and now 2012 for over 10 years, I'm ideally placed to assist you.  Working as a full-time DBA in my own right, I continue to develop my skills in the real world and I'm available on an hourly rate or fixed-price basis to help you get your business back on track.

Contact me through http://www.derekcolley.co.uk/, directly at derek@derekcolley.co.uk or catch up with me on Twitter - #dcolleySQL or LinkedIn - http://uk.linkedin.com/pub/derek-colley/38/30a/aa7.