Compare to huge tables

[andreis]

Hi, got a puzzling question during an interview - how to find 2 rows that are different in 2 otherwise identical tables with 2 millions records each, no indexes, no primary keys. Since the tables are huge, SELECT * FROM t1 EXCEPT SELECT * FROM t2 won't work because it'll take too long to run. Any ideas? Thanks.

 

[r937]

did you get the job?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[andreis]

not until I know the answer

 

[gmmastros]

I'd probably use the binary_checksum function. Of course, tables with millions of rows and no keys or indexes is just begging for trouble.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andreis]

thanks, George, this is a great hint, although, for whatever reason, I still have a feeling this is not the most elegant solution I was expected to present...