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

Filed under: Index, Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication Tagged: code language, indexes, language sql, microsoft sql server, sql statements, T-SQL