We have a set of databases at my office that use the load and swap method of loading data with minimal effect on the users. In case you haven’t heard of this before if you have a table that is modified during a batch process only (no user writes at all) then you can make two copies of the table load one, swap the two tables using sp_rename (example below) then load the alternate table. Yes you have to do your load twice, and yes it takes twice as long, uses twice as much IO, CPU etc. What it doesn’t do is perform any locks during the load on the primary table. The only lock taken is during the actual switch which takes very little time.

EXEC sp_rename 'PrimaryTable','tempTable'
EXEC sp_rename 'AltTable','PrimaryTable'
EXEC sp_rename 'tempTable','AltTable

One of the most important parts of this method though is making sure both PrimaryTable and AltTable are ABSOLUTELY identical in structure. The reasons for this should be fairly obvious given that they are going to be swapped back and forth on a regular basis. Basic structure isn’t all that hard. If you add a column to one table and not the other you are libel to run into some fairly obvious problems pretty quickly. Unfortunately indexes can be a bit harder to keep track of. There is a lot to an index and some fairly simple differences between two indexes can make a world of difference in performance.

I’ve been doing some index tuning on these databases recently and by chance noticed that there were some indexes missing on one of the tables. Which then brought up the question what other differences are there? And while there are lots of tools out there to compare all of the tables/indexes etc between two databases there aren’t any that I’ve seen that will compare two tables in the same database. So to fill my need I’ve written a quick and dirty (well somewhat) query to help me with the comparison.

DECLARE @Table1 sysname;
DECLARE @Table2 sysname;
SET @Table1 = 'PrimaryTable';
SET @Table2 = 'AltTable';
WITH IndexesTb1 AS (SELECT *,
						STUFF((SELECT ', ' + sys.all_columns.name + CASE WHEN is_descending_key = 1 THEN ' - DESC' ELSE '' END
								FROM sys.index_columns
								JOIN sys.all_columns
									ON sys.index_columns.object_id = sys.all_columns.object_id
									AND sys.index_columns.column_id = sys.all_columns.column_id
								WHERE sys.index_columns.object_id = sys.indexes.object_id
								  AND sys.index_columns.index_id = sys.indexes.index_id
								  AND is_included_column = 0
								FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
							  , 1, 2, '') AS IndexKeys,
						STUFF((SELECT ', ' + sys.all_columns.name + CASE WHEN is_descending_key = 1 THEN ' - DESC' ELSE '' END
								FROM sys.index_columns
								JOIN sys.all_columns
									ON sys.index_columns.object_id = sys.all_columns.object_id
									AND sys.index_columns.column_id = sys.all_columns.column_id
								WHERE sys.index_columns.object_id = sys.indexes.object_id
								  AND sys.index_columns.index_id = sys.indexes.index_id
								  AND is_included_column = 1
								FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
							  , 1, 2, '') AS IncludedColumns 
					FROM sys.indexes 
					WHERE Object_Id = OBJECT_ID(@Table1) ),
	IndexesTb2 AS (SELECT *,
						STUFF((SELECT ', ' + sys.all_columns.name + CASE WHEN is_descending_key = 1 THEN ' - DESC' ELSE '' END
								FROM sys.index_columns
								JOIN sys.all_columns
									ON sys.index_columns.object_id = sys.all_columns.object_id
									AND sys.index_columns.column_id = sys.all_columns.column_id
								WHERE sys.index_columns.object_id = sys.indexes.object_id
								  AND sys.index_columns.index_id = sys.indexes.index_id
								  AND is_included_column = 0
								FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
							  , 1, 2, '') AS IndexKeys,
						STUFF((SELECT ', ' + sys.all_columns.name + CASE WHEN is_descending_key = 1 THEN ' - DESC' ELSE '' END
								FROM sys.index_columns
								JOIN sys.all_columns
									ON sys.index_columns.object_id = sys.all_columns.object_id
									AND sys.index_columns.column_id = sys.all_columns.column_id
								WHERE sys.index_columns.object_id = sys.indexes.object_id
								  AND sys.index_columns.index_id = sys.indexes.index_id
								  AND is_included_column = 1
								FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
							  , 1, 2, '') AS IncludedColumns 
					FROM sys.indexes 
					WHERE Object_Id = OBJECT_ID(@Table2) )
SELECT IndexesTb1.name AS IndexesTb1Name, 
	IndexesTb2.name AS IndexesTb2Name,
	ISNULL(IndexesTb1.type, IndexesTb2.type) AS type,
	ISNULL(IndexesTb1.type_desc, IndexesTb2.type_desc) AS type_desc,
	ISNULL(IndexesTb1.is_unique, IndexesTb2.is_unique) AS is_unique,
	ISNULL(IndexesTb1.data_space_id, IndexesTb2.data_space_id) AS data_space_id,
	ISNULL(IndexesTb1.ignore_dup_key, IndexesTb2.ignore_dup_key) AS ignore_dup_key,
	ISNULL(IndexesTb1.is_primary_key, IndexesTb2.is_primary_key) AS is_primary_key,
	ISNULL(IndexesTb1.is_unique_constraint, IndexesTb2.is_unique_constraint) AS is_unique_constraint,
	ISNULL(IndexesTb1.fill_factor, IndexesTb2.fill_factor) AS fill_factor,
	ISNULL(IndexesTb1.is_padded, IndexesTb2.is_padded) AS is_padded,
	ISNULL(IndexesTb1.is_disabled, IndexesTb2.is_disabled) AS is_disabled,
	ISNULL(IndexesTb1.is_hypothetical, IndexesTb2.is_hypothetical) AS is_hypothetical,
	ISNULL(IndexesTb1.allow_row_locks, IndexesTb2.allow_row_locks) AS allow_row_locks,
	ISNULL(IndexesTb1.allow_page_locks, IndexesTb2.allow_page_locks) AS allow_page_locks,
	ISNULL(IndexesTb1.has_filter, IndexesTb2.has_filter) AS has_filter,
	ISNULL(IndexesTb1.filter_definition, IndexesTb2.filter_definition) AS filter_definition,
	ISNULL(IndexesTb1.IndexKeys, IndexesTb2.IndexKeys) AS IndexKeys,
	ISNULL(IndexesTb1.IncludedColumns, IndexesTb2.IncludedColumns) AS IncludedColumns
FROM IndexesTb1
FULL JOIN IndexesTb2
	ON IndexesTb1.name = IndexesTb2.name
	  AND IndexesTb1.type = IndexesTb2.type
	  AND IndexesTb1.type_desc = IndexesTb2.type_desc
	  AND IndexesTb1.is_unique = IndexesTb2.is_unique
	  AND IndexesTb1.data_space_id = IndexesTb2.data_space_id
	  AND IndexesTb1.ignore_dup_key = IndexesTb2.ignore_dup_key
	  AND IndexesTb1.is_primary_key = IndexesTb2.is_primary_key
	  AND IndexesTb1.is_unique_constraint = IndexesTb2.is_unique_constraint
	  AND IndexesTb1.fill_factor = IndexesTb2.fill_factor
	  AND IndexesTb1.is_padded = IndexesTb2.is_padded
	  AND IndexesTb1.is_disabled = IndexesTb2.is_disabled
	  AND IndexesTb1.is_hypothetical = IndexesTb2.is_hypothetical
	  AND IndexesTb1.allow_row_locks = IndexesTb2.allow_row_locks
	  AND IndexesTb1.allow_page_locks = IndexesTb2.allow_page_locks
	  AND IndexesTb1.has_filter = IndexesTb2.has_filter
	  AND ISNULL(IndexesTb1.filter_definition,'') = ISNULL(IndexesTb2.filter_definition,'')
	  AND IndexesTb1.IndexKeys = IndexesTb2.IndexKeys
	  AND ISNULL(IndexesTb1.IncludedColumns,'') = ISNULL(IndexesTb2.IncludedColumns,'')
ORDER BY ISNULL(IndexesTb1.name, IndexesTb2.name), IndexesTb2.index_id