Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How can I compare these two sets?

Author  Topic 

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2014-10-28 : 09:54:01
How can I compare these two sets using all their attributes, and ensure they are identical ?

--1st set
SELECT
TABLE_DATA.name AS TableName
, INDEX_DATA.name AS IndexName
, INDEX_DATA.type_desc AS IndexType
, STUFF(( SELECT ', ' + COLUMN_DATA_KEY_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
FOR
XML PATH('')
), 1, 2, '') AS ColumnLists
, STUFF(( SELECT ', ' + COLUMN_DATA_INC_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id
AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
FOR
XML PATH('')
), 1, 2, '') AS IncludedColumns
FROM sys.indexes INDEX_DATA
INNER JOIN sys.tables TABLE_DATA
ON TABLE_DATA.object_id = INDEX_DATA.object_id
INNER JOIN sys.schemas SCHEMA_DATA
ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
WHERE TABLE_DATA.is_ms_shipped = 0
AND INDEX_DATA.type_desc IN ( 'NONCLUSTERED', 'CLUSTERED' ) -- Avoiding heaps
AND INDEX_DATA.name='IndexABC'
AND TABLE_DATA.name='MyTable'

--2nd set
SELECT TableName, IndexName, IndexType, ColumnLists, IncludedColumns
FROM DROPPED
WHERE IndexName='IndexABC'
AND TableName='MyTable'


I am trying to avoid UNION due performance issues and a collation error.

Any suggestion? I though about a composite join, but 1st set has two complex columns generated from subqueries. I guess I need to put that on a temporary table 1st if I want to use a composite join?

Thanks in advance,

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 09:58:04
Using set algebra, if A-B = B-A = the empty set, the sets are equal. In SQL we have to consider the number of rows, since SQL tables are multisets or bags.


select count(*) from A -- should be the same as below
select count(*) from B -- should be the same as above
select * from A except select * from B -- should return no rows
select * from B except select * from A -- should return no rows
Go to Top of Page

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2014-10-28 : 10:19:04
quote:
Originally posted by gbritton

Using set algebra, if A-B = B-A = the empty set, the sets are equal. In SQL we have to consider the number of rows, since SQL tables are multisets or bags.


select count(*) from A -- should be the same as below
select count(*) from B -- should be the same as above
select * from A except select * from B -- should return no rows
select * from B except select * from A -- should return no rows




Thanks, but that does not solve my problem.

Maybe I was not clear enough. What would be the exact T-SQL query for above specific data sets?

EXCEPT and UNION are not working due differences in collation. See below:


Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the EXCEPT operation.


Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 10:38:26
OK -- then you'll have to compare the rows column by column. You'll have to choose a collation for the comparisons (e.g. where a = b collate ...).
Go to Top of Page

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2014-10-28 : 10:42:26
Fixed!

It was actually easier that I though.

I basically put the complex set on a temporary table and then I compared against that temp table, instead of the initial set. This also fixes the collation discrepancy.
Go to Top of Page
   

- Advertisement -