SQL Server 2008 – Backup Compression

The biggest benefit that I see with the new backup compression in SQL Server 2008 is that it improves the speed of the restore. The thing that is far more important that a database backup is the actual restore process, and with a compressed backup, this can speed up the restore time by as much as 40% which can make a huge difference with larger databases.

SQL Server 2008 – Sparse Columns

In SQL Server, an empty or a null column still would take up space in the database, so even if you added a column that was set to null values, there would still be space taken up in the database. Sparse Columns introduced in SQL Server 2008, are a new type of column configuration that truly take up zero space when they are null. One of the drawbacks is that the columns that aren’t null end up being slightly larger, so this should only be used when there is a column in the database that has a majority of the values set to null.