SQL Server Page Verification

You know how SQL server checks whether the page is good or corrupted?

There are two ways by which SQL Server finds the corrupted page:Torn Page Detection and Checksum.

Checksum is a new type of page verification introduced in SQL Server 2005 which offers better corruption checking than torn page detection.This is why when we create a new database in SQL Server 2005 or 2008 the checksum option is turned on by default.

What Checksum do?
For a database with checksum ON SQL Server computes the checksum of the page both when a page is written and when a page is read. The SQL Server computes the checksum based on the bit pattern on the page, stores it in the page header and then issues an I/O to write the page. When the SQL Server reads the page, it re-computes the checksum using the same logic and then compares it with the value available in the page header. If the checksum value matches then it is assumes the page did not get corrupted during the write-read cycle.Keep in mind that checksum is not 100% accurate and there are cases when corruption remains undetected even if you have checksum enabled.

How Torn Page Detection works?
Torn page detection on the other hand does a different type of page verification in which it writes a bit for every 512 byte of a page. This will help you in finding whether the page has been succesfully written or not but it cant tell you whether the data you have written is correct or not. We can use the same checksum for verifying backup.This will give added protection for backup.

How to enable page verification?
--CHECKSUM
ALTER DATABASE RKN set PAGE_VERIFY CHECKSUM

--TORN PAGE DETECTION
ALTER DATABASE RKN SET PAGE_VERIFY TORN_PAGE_DETECTION

You can also change the page verify option using GUI
DATABASE PROPERTIES-->OPTIONS-->PAGE VERIFY Here you can select the type of page verification you want.