SteveStedman

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.

SQL Server 2008 introduces a new feature called Filtered Index. A filtered index is a way of adding a WHERE clause to the index definition to get faster access to a subset of the data. For instance if you have a huge table where you are typically only accessing a small part of the table, you can add a filtered index for that small part and save time by having a smaller (filtered index) to access the data that you are looking for.

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.

New to SQL Server 2008 is the MERGE operator or statement. MERGE is a TSQL statement that combines INSERT, UPDATE, and DELETE actions based on conditional logic. If you have ever written code that checks to see if a row exists, then performs an insert if it does, or does and update if it does exist, then you could benefit from using the MERGE statement. The check, update, or insert are all done as an atomic set based operation, which can be much more efficient than several commands.