SQL Server 2008 New Features

FILESTREAM:
Feature of SQL Server 2008, which allows storage and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.
Filestream updates are part of transactions insert, update etc.,
Filestream need not be backed up along with the database. You can do it either way.
There is separate log for Filestream transactions.
Dotnet 3.5 sp1 required for for system.data.sqltypes.sqlfilestream class
Database mirroring is not possible with filestream.

Sparse column:
New way to store NULL columns or data columns
When you declare column as SPARSE it will not take up disk space for NULL column.
space is reduced for NULL columns but cost is overhead for retrieving non-NULL values.
Some columns like ntext, image etc., can not be SPARSE columns
when the column is not null SPARSE takes more space, typically 4 bytes per column.
SQL Server management can suggest you with the space it takes with each datatype column. It gives the % of space with each data type.
On million rows, if you used it for right column it might save at least 10 MB.


Filtered Index:
Which row to include in the index and which row to drop out of indexing.
You can define the rows you want to index in where clause
Must be non clustered index
Created filtered statistics for filtered indexes.

Several new data types introduced in 2008
Till now datetime is one datatype.
now we have 4, date, time, datime2 and datimeoffset
Each date variable takes 3 bytes.
Time data type stored only time on 24 hour clock
fraction of seconds is 7, instead of 3 earlier.