Scenario:: bulk import the data into a temp table for staging purposes and then move it to the user database. The user database has CHECKSUM enabled, so the new page, when written to the disk, will have checksum computed. But guess what is missing? If the tempdb disk corrupts the pages in tempdb, SQL Server will have no way of knowing that the page was corrupted and it will go to user database without detection. Yes, when the page is subsequently read, depending upon what the corruption was, the SQL Server may detect it or may not. For example if a bit flip happened for the integer value, it will go undetected.
With CHECKSUM available on tempdb starting with SQL2008, you can finally close this window.  You can use the following command
For new installs of SQL Server 2008, all tempdbs will have CHECKSUM enabled by default. You can always disable it using ALTER DATABASE command but we don't recommend it. For databases upgraded to SQL Server 2008, you will need to explicitly enable CHECKSUM on the tempdb. We measured the performance impact of enabling CHECKSUM in tempdb and the impact is very low (<2% of CPU) which is similar to what you would expect in user database. Since the CHECKSUM is only computed when page is written to the disk, the added point is that there is no 'checkpoint' in tempdb, so a page in tempdb is written to disk ONLY under memory pressure. So you may not see as many CHECKSUM calculations in tempdb.