Useful Features in SQL Server 2008

Encryption at rest

Whole databases can be encrypted with a master key (which can be stored in an HSM), rather than encrypting by each column. No code changes are required, hence this feature is called Transparent Data Encryption. It gives true encryption of data at rest, included backup files.

Filtered indexes

An index can be created with a filter (i.e. index all rows where MyDateField is not null), thus making much more meaningful indexes &rarrow; improved performance.

Sparse columns support

There are times when you want to create say 20 columns, only a portion of which will be used at any given time (perhaps there are multiple applications using the table, and they each have a few unique needs). If you create all these columns, your table becomes a little unwieldy in many ways. You can use an XML column to store just the subset required, or in 2008 you can use the new sparse columns feature. This will improve indexing and manageability.

Performance Data Collector

The new Performance Data Collector stores query information in a data warehouse, making it easy to answer questions like "whose query was hogging the resources this morning, causing my job to get a timeout?"

Resource Governor

Allows the administrator to allocate resource slices to different users or classes of users; thus for instance you could specify that the core processing user would always get (if needed) a minimum of 40% of the CPU & RAM, and you could specify that the marketing users (once they have beefy report models to work with) never get more than 10% of the resources. For example.

Report Builder

The Reporting Services Report Builder has been entirely redesigned — now looks like an Office 2007 application. The table and matrix controls have been replaced with a far more flexible tablix control that allows you to do all kinds of cool things.

Integration Services

These upgrades are basically in the area of performance improvements, particularly with regard to the Lookup data flow task. Other improvements / new functionality: ADO.Net as default data source (rather than OLEDB); data profiling task; C# as new / default language for scripting tasks.

Transact SQL / Database Engine

Finally, we get to the heart of SQL Server: the structured query language. Supposedly this thing is much faster. There is some new syntax available: GROUPING SETS for enhanced BI-type queries; MERGE to perform inserts, updates, and deletes in one statement; the+= operator, and more. There's also the new Change Data Capture for enhanced auditability and better incremental bulk loads.

Multiple Insert Rows

SQL Server 2008 supports the option of inserting multiple records in one statement. Each row of data is followed by a comma until reaching the last row where the INSERT command is completed like normal.

INSERT INTO Customers
(CustID, CustName)
VALUES
('Cust1', 'Smith Company'),
('Cust2', 'Perform Company'),
('Cust3', 'Test Inc');

Other version of SQL Server require a separate statement to be executed for each record insert.