Check Data Consistency SQL Level (Database level and Table Level)

Question

Hi everyone, I currently partition my tables using copy of the data to empty table method.

The point is, I copied the whole data from one table to a new table with the exact same column, what should I do to make sure that all data between two tables is exactly the same?

*The tables are huge some tables reach 1TB

Answer 1

To check data consistency between two tables in SQL Server, you can perform a row count comparison and a checksum comparison. Here are the steps:

1. Perform a row count comparison: Count the number of rows in each table and compare them. If the counts are the same, it's a good indication that the data is consistent. To count the rows, you can use the following query: SELECT COUNT(*) FROM table_name;
2. Perform a checksum comparison: Calculate a checksum value for each table and compare them. A checksum is a numeric value that represents the contents of a table. If the checksum values are the same, it's a strong indication that the data is consistent. To calculate the checksum, you can use the following query: SELECT CHECKSUM_AGG(BINARY_CHECKSUM()) FROM table_name; Note that BINARY_CHECKSUM() calculates a checksum for all columns in the table, while CHECKSUM_AGG aggregates the checksum values for all rows.
3. Compare the results: Compare the row counts and checksum values for both tables. If they match, the data is consistent. If they don't match, there may be some data discrepancies between the two tables.

To perform these checks at the database level, you can use a script to loop through all the tables in the database and perform the row count and checksum comparisons. Here is an example script:

DECLARE @table_name nvarchar(128) DECLARE @sql nvarchar(max)

DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N'SELECT ''' + @table_name + ''', COUNT(), CHECKSUM_AGG(BINARY_CHECKSUM()) FROM ' + @table_name EXEC sp_executesql @sql

FETCH NEXT FROM table_cursor INTO @table_name END

CLOSE table_cursor DEALLOCATE table_cursor

This script will loop through all the base tables in the database, calculate the row counts and checksum values, and print the results to the console. You can then compare the results to identify any data inconsistencies.

Hope this helps!