In the SQL Server community, most examples of dynamic T-SQL code are plagued with a severe readability problem. They're hard to read because dynamic
T-SQL code isn't formatted, so you can't easily grasp the command and query structure. Concatenation operators and the necessary doubling of single
quotes obfuscate the dynamic query even more. And sometimes several parts of the query need to be generated from the same variable, which ends up
adding even more concatenations. Tracing the query to see what was generated isn't much help because the query isn't formatted.
The unreadability of dynamic T-SQL code discourages DBAs and developers from using it in large T-SQL solutions. Plus, taking advantage of free
solutions contributed by the SQL Server community is hampered because people can't easily grasp the code generated.
To make dynamic T-SQL code more readable, I've been using several techniques since the introduction of the nvarchar(max) data type in SQL Server 2005.
This data type is vital to my techniques because string size is no longer a limitation to query formatting, and text manipulation is as easy as with
other nvarchar() data types.
The CompareRows stored procedure in Listing 1 illustrates my techniques. It compares rows from two tables, from two queries, or from one table and one
query, then lists the nonmatching records. After I explain how this stored procedure works, I'll walk you through the techniques it uses to make the
dynamic T-SQL code easier to read.
How CompareRows Works
As the CompareRows stored procedure demonstrates, comparing rows can be done very efficiently using a UNION ALL statement to put together rows from a
source and a target, provided that you add the DataSetId column in which the values identify the source or target. A common table expression named
UnionOfDataSetsToCompare unites rows from the source and target, adding the DataSetId value. A GROUP BY clause is then applied on the columns being
compared and the HAVING clause compares minimum and maximum aggregates on the generated DataSetId column to detect which rows have no matching
counterpart in the source or target. The end result is a rowset of all rows that have no matching record, with the DataSetId column describing where
each row is from.
The stored procedure compares the data in the rows for the columns you specify. It has 11 parameters, most of which are optional because CompareRows is
flexible when it comes to specifying the source and target rows to compare. Rows can come from the same table or different tables, from the same
database or different databases, or from the same SQL Server instance or different instances. You can also filter out subsets of rows to compare by
including WHERE clauses that apply to the source or target table. Rows can even be generated by queries.
Here are the stored procedure's 11 named parameters:
-
@ColList -- Used to specify which columns to include in the comparison. The primary key columns must be included. The columns' names need to be
in a comma-separated list.
-
@SrcTab -- Used to specify the name of the source table. The schema must be included. (Add brackets if required.)
-
@TgtTab -- Used to specify the name of the target table if it isn't the same as the source table. The schema must be included.
-
@SrcQry -- Used to specify the source query. When used, @SrcTab is ignored and the source rows come from this query. The database and schema
must be included in the FROM clause.
-
@TgtQry -- Used to specify the target query. When used, @TgtTab is ignored and the target rows come from this query. The database and schema
must be included in the FROM clause.
-
@SrcWhereClause -- Used to provide the WHERE clause for the source rows if applicable.
-
@TgtWhereClause -- Used to provide the WHERE clause for the target rows if applicable.
-
@SrcDB -- Used to specify the source database if it isn't the current database.
-
@TgtDB -- Used to specify the target database if it isn't the same as the source database.
-
@SrcInstance -- Used to specify the linked source server if it isn't the local server.
-
@TgtInstance -- Used to specify the linked target server if it isn't the local server.
Listing 2 shows an example of how to use CompareRows to compare the Contacts table in the AdventureWorks database on one instance against the same
table in a restored copy of the AdventureWorks database on another instance. As you can see, there's no need to specify the target table because it has
the same name as the source table. The same applies for target database.
Listing 3 shows an example of how to use the stored procedure to compare the rows generated from two queries. You can download CompareRows by clicking the download link at the top of this page.