MSSQL: Checking for Equality of Tables (Hints only, please)

[WWGD]

Hi all,
I am trying to find a method to determine if two tables T1, T2 (of any size) in MSSQL2012 are equal.
Please just give hints

My idea is to use the "Except" operator this way:

Select * from T1
Except
(Select * from T2)

And I should get an empty table with columns in both, but with no values entered.
Does this work? Any other hints?

 

[fresh_42]

Only unspecific thoughts, since MSSQL isn't the version I used very often:

Beside automatic PK do you have another one to specify single entries? I can't imagine this will work without 'where' clause plus 'not in'.
Maybe not only PK differ but also timestamps or the spelling of char entries. (Runtime might get a problem, too.)

 

[WWGD]

Never mind, Fresh - Prinz_42 of Bel_ Deutschair , I think a correlated subquery using >< should do. Do you agree?

 

[pbuk]

Hi all,
I am trying to find a method to determine if two tables T1, T2 (of any size) in MSSQL2012 are equal.
Please just give hints

My idea is to use the "Except" operator this way:

Select * from T1
Except
(Select * from T2)

And I should get an empty table with columns in both, but with no values entered.
Does this work? Any other hints?

Hints:

1. Look up the documentation and you will see that that is exactly what EXCEPT is for.
2. Do you think that you will always get the same results from

   SELECT * FROM t2 EXCEPT SELECT * FROM t1

3. You don't need the ()s.
4. Look at the documentation to see treatment of schema differences and edge cases.
5. You could also look at the command line utility tablediff.
6. Use capitals for reserved words.

Edited: forget to hint about asymmetry

 

[fresh_42]

Never mind, Fresh - Prinz_42 of Bel_ Deutschair , I think a correlated subquery using >< should do. Do you agree?

Depends on how you join them. If you're "lucky" you will select to each A.entry_m all B.entry_n with n≠m. What speaks against select A.entry where A.entry not in (select B.entry where A.entry = B.entry); beside runtime?

 

[Svein]

I am trying to find a method to determine if two tables T1, T2 (of any size) in MSSQL2012 are equal.

I tend to do it the hard way. Insert a logical field in T2, set them all to "false" and use a query to set the field to "true" in all matching records. The ones left out are the records where the field value is still "false".

 

[WWGD]

Hints:

1. Look up the documentation and you will see that that is exactly what EXCEPT is for.
2. Do you think that you will always get the same results from

   SELECT * FROM t2 EXCEPT SELECT * FROM t1

3. You don't need the ()s.
4. Look at the documentation to see treatment of schema differences and edge cases.
5. You could also look at the command line utility tablediff.
6. Use capitals for reserved words.

Edited: forget to hint about asymmetry

Thanks, Anchovy, but does your answer assume the tables are normalized? I am thinking I have the same n distinct records in each, but these n records may be
repeated a different number of times in each table, say record_1 appears 7 times in table 1, and appears 8 times in table 2 , etc. How do I account for this; should I select count for each record := record _i and then compare the counts for each?

 

[pbuk]

Thanks, Anchovy, but does your answer assume the tables are normalized? I am thinking I have the same n distinct records in each, but these n records may be
repeated a different number of times in each table, say record_1 appears 7 times in table 1, and appears 8 times in table 2 , etc. How do I account for this; should I select count for each record := record _i and then compare the counts for each?

Sorry for the delay, I haven't been around for a while - but then there was even more of a lag for your question!

It's not going to work with duplicate rows, but then you asked for hints not a complete solution! If there are (or could be) duplicates then yes you are going to need to come up with a different solution. Counting the occurrences for each distinct row in each table and compare the counts could work. Or sort both tables by the same complete ordering, add a sequence column and then use EXCEPT. Or write a script in your language of choice to iterate over sorted recordsets. Or use tablediff. It all depends on the context - why do you want to compare these tables, why might they be different and what do you want to do if they are?

 

[WWGD]

Sorry for the delay, I haven't been around for a while - but then there was even more of a lag for your question!

It's not going to work with duplicate rows, but then you asked for hints not a complete solution! If there are (or could be) duplicates then yes you are going to need to come up with a different solution. Counting the occurrences for each distinct row in each table and compare the counts could work. Or sort both tables by the same complete ordering, add a sequence column and then use EXCEPT. Or write a script in your language of choice to iterate over sorted recordsets. Or use tablediff. It all depends on the context - why do you want to compare these tables, why might they be different and what do you want to do if they are?

Thanks, it is a theoretical thing, just a challenge someone gave me to help me sharpen my SQL skills. I am allowed to get hints but not full answers.

 

[petrushkagoogol]

I think you can check UNION of both tables and find the rowcount which should equal the rowcount from table T1 as well as table T2.

 

[WWGD]

Thanks all, from my reading I think it may be a good idea to use rank or dense rank .

EDIT: I appreciate your suggestions and probably there are a few better than mine but this
is the one I am most familiar with at this point.

 

[petrushkagoogol]

Thanks, it is a theoretical thing, just a challenge someone gave me to help me sharpen my SQL skills. I am allowed to get hints but not full answers.

To first compare the structures of 2 tables you can use the system view information_schema.columns.