checksum table

• MySQL

(8.4.0)

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 datetime(3) );

insert into tab1 values(1.01, 'あ', '1234567890', now(3) );
insert into tab1 values(1.02, ',', '', now(3) );
insert into tab1 values(1.03, 'X', NULL, now(3) );

select * from tab1;

checksum table tab1;

• Oracle

(23c)

https://qiita.com/plusultra/items/a4ece226df8e9e099c4e

drop table tab1 purge;
create table tab1(col1 number(10,2), col2 varchar2(3), col3 varchar2(10), col4 timestamp(3) );

alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF3';

insert into tab1 values(1.01, 'あ', '1234567890', systimestamp);
insert into tab1 values(1.02, ',', '', systimestamp);
insert into tab1 values(1.03, 'X', NULL, systimestamp);

commit;
select * from tab1;

SELECT CHECKSUM(col1),CHECKSUM(col2),CHECKSUM(col3),CHECKSUM(col4) FROM tab1;

• PostgreSQL

(16)

https://qiita.com/noborus/items/7f277f8c44afdf4328af

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 timestamp(3) );

insert into tab1 values(1.01, 'あ', '1234567890', clock_timestamp() );
insert into tab1 values(1.02, ',', '', clock_timestamp() );
insert into tab1 values(1.03, 'X', NULL, clock_timestamp() );

select * from tab1;

SELECT CAST(tab1.* AS text)  FROM tab1;

SELECT md5( CAST(tab1.* AS text) ) FROM tab1;

SELECT 'x'||md5( CAST(tab1.* AS text) ) FROM tab1;

SELECT ( 'x'||md5( CAST(tab1.* AS text) ) )::bit(64)::bigint FROM tab1;

SELECT sum( ( 'x'||md5( CAST(tab1.* AS text) ) )::bit(64)::bigint ) FROM tab1;

• SQL Server

(2022)

drop table tab1;
create table tab1(col1 numeric(10,2) , col2 nvarchar(1), col3 varchar(10), col4 datetime2(3) );

insert into tab1 values(1.01, 'あ', '1234567890', getdate() );
insert into tab1 values(1.02, ',', '', getdate() );
insert into tab1 values(1.03, 'X', NULL, getdate() );

select * from tab1;

select checksum(*) from tab1;

select sum( cast( checksum(*) as bigint) ) from tab1;