What happens if I can't rebuild indexes after setting PAGE_VERIFY to CHECKSUM?

Question

I've come across a production SQL Server 2008 box that has quite a few databases with PAGE_VERIFY option set to NONE. I'm going to set the PAGE_VERIFY option to CHECKSUM. If I understand correctly, I must rebuild the existing indexes for CHECKSUM to affect them. Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well.

1. What will happen to the existing indexes if I can't rebuild them right away?
2. Are the existing indexes still usable after setting CHECKSUM without a rebuild?

Thanks!

Answer 1

"Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well." -- if you rebuild a clustered index, the non-clustered indexes won't be rebuild, please see Paul's blog post -- http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/ -- even though it says 2005, it's true for any version, as far as I know. 1. Nothing will happen 2. Will be usable

Also can read in Paul's blog, Myth a Day -- http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1730-page-checksums/

As you and Jon, added -- there are 2 ways (from Paul's blog): 1. There is no process, background or otherwise, that can put a page checksum on each page. This is a major bummer (technical term :-) as it means you must perform index rebuilds or other size-of-data operations to actually put a page checksum on the pages. 2. A page checksum is only put on a page when it is read into memory, changed, and then written back out to disk.