CODEWITHSUNDEEP
sql-serverdatabasedatabase-designdatabase-integrity
ichbinblau
ichbinblau

Reputation: 4819

How to ensure the data integrity using SQL Server?

I have a table with billions of rows in SQL Server. I want to ensure that the data is read-only (immutable) and always will not changed after being inserted into the database.

My current thought is to use SHA3 hash algorithm to make a hash chain. 

HASH(current_row) = HASH(HASH(previous_row) + current_row) # as the hash content of the current row

The solution above has the following shortcomings:

  1. It may take too long if there are billions of rows as every record hash is related to the hash of the previous record.

  2. It does not allow compute in parallel.

Considering the performance, what is the most practical way to make sure data integrity?

Upvotes: 0

Views: 587

Answers (1)

cherouvim
cherouvim

Reputation: 31903

Data integrity means something else. If you need to make sure that some users will not be updating and deleting anything, then do not provide them with the update and delete grant.

Whether reading will be performant is unrelated to the grants. You'll need to know your fetch scenarios and setup correct indexes. If that is not enough you may need other more advanced techniques (sharding, only reading from replicated slaves etc)

Upvotes: 2

Related Questions