I was sitting at home and thinking about some of the different features of SQL 2008 and their interoperability. Thus I wanted to verify the compatability of SPARSE COLUMNS and Transparent Data Encryption (TDE). SPARSE columns shows great gains for NULL values and TDE allows you to encrypt your database; I wanted to verify just as with TDE and Data Compression you still get the space savings benefits. As you can see by the results below it does appear as though databases with TDE enabled to still benefit from SPARSE COLUMNS. (As a note Backup Compression does not show any benefits when utilized on a TDE enabled database.)
Below are the results (Tables within databases contained identical data):
NORMAL DB:
Table Without SPARSE COLUMNS = 39.063MB
Table With SPARSE COLUMNS = .219MB
TDE DB:
Table Without SPARSE COLUMNS = 39.063MB
Table With SPARSE COLUMNS = .219MB
