April 18, 2021 at 4:10 am
SELECT * FROM Tablename1 WHERE CHECKSUM(*)
NOT IN (SELECT CHECKSUM(*) FROM TableName2)
2. and there is page checksum and database backup checksum, and what does it do while page checksum and database backup checksum?
thanks!
April 18, 2021 at 3:47 pm
For the above to work, TableName1 and TableName2 would need to have have the same structure.
Since the structure is the same, why not use
SELECT * FROM TableName1
EXCEPT
SELECT * FROM TableName2
April 18, 2021 at 5:15 pm
- Could you please me how checksum work in the SQL statement ? below is an example.
SELECT * FROM Tablename1 WHERE CHECKSUM(*)
NOT IN (SELECT CHECKSUM(*) FROM TableName2)
2. and there is page checksum and database backup checksum, and what does it do while page checksum and database backup checksum?
thanks!
There is no way that I'd use CHECKSUM() for any such comparison. The risk of collisions is just too high and you can verify that even Microsoft says so by doing a search for CHECKSUM in SQL Server (which you should have done already but probably didn't do or you'd have already know this). The document also makes a suggestion as to a better alternative.
Remember also that as small as the possibilities of a collision get with the larger HASHBYTES algorithms, there is no guarantee of there not being collisions.
What that means is that if you have an inequality, then they're guaranteed to be different. If you have an equality, then they are NOT guaranteed to be the same and you would need a secondary check for such supposed equal values.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2021 at 2:59 am
thank you Jeff Moden!
I want to the theory of checksum while using it as comparison condition.
April 19, 2021 at 7:13 pm
Here's a bit of theory: https://www.online-tech-tips.com/cool-websites/what-is-checksum/
HASHBYTES can use SHA1.
This is a quick check, but not necessarily an absolute one.
April 20, 2021 at 2:12 pm
EXCEPT is great. But to be clear, it doesn't require that the tables have identical structures, it compares the results of two queries. You can select a subset of columns, combine columns from joined tables, and even do datatype conversions (like int to varchar) in the queries. Also, you can make the EXCEPT run much faster if you have a common key to join on for your second query.
declare @Tbl1 table (RowID int primary key, Color varchar(30), ColorCode varchar(5))
declare @Tbl2 table (RowID int primary key, Color varchar(30), ColorNumber int)
insert into @Tbl1
values (1,'Red','30'), (2,'Blue','35'), (3,'Yellow', '40'), (4,'Paisley','ABC')
insert into @Tbl2
values (1,'Red',30), (2,'Blue',35), (3,'Yellow', 45) -- No match because 45 != 40
select RowID, Color, ColorCode
from @tbl1
except
select t1.RowID, t2.Color, convert(varchar(5), t2.ColorNumber)
from @Tbl1 t1
join @Tbl2 t2 on t1.RowID = t2.RowID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 21, 2021 at 2:09 am
EXCEPT is great. But to be clear, it doesn't require that the tables have identical structures, it compares the results of two queries. You can select a subset of columns, combine columns from joined tables, and even do datatype conversions (like int to varchar) in the queries. Also, you can make the EXCEPT run much faster if you have a common key to join on for your second query.
declare @Tbl1 table (RowID int primary key, Color varchar(30), ColorCode varchar(5))
declare @Tbl2 table (RowID int primary key, Color varchar(30), ColorNumber int)
insert into @Tbl1
values (1,'Red','30'), (2,'Blue','35'), (3,'Yellow', '40'), (4,'Paisley','ABC')
insert into @Tbl2
values (1,'Red',30), (2,'Blue',35), (3,'Yellow', 45) -- No match because 45 != 40
select RowID, Color, ColorCode
from @tbl1
except
select t1.RowID, t2.Color, convert(varchar(5), t2.ColorNumber)
from @Tbl1 t1
join @Tbl2 t2 on t1.RowID = t2.RowID
Thank you for your kind help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply