3 Useful New Features of SQL Server 2008

Data Compression
SQL Server 2008 gives us the ability to compress data and save on disk space. Without getting into many specifics, data in SQL Server can be compressed at the page level. This means that when you compress a table, it actually does it a page at a time. I'd advise you to tread lightly with data compression. You should make sure you do your research and testing before implementing compression in your environment. That said, when disk space is at a premium, you can use this feature to pack more data onto the platters.

Backup Benefits
SQL Server 2008 provides backup compression. With backup compression, you have the savings of file size built right into the native backup.

To use backup compression, you just need to add the WITH COMPRESSION option to a BACKUP DATABASE statement as shown below:

BACKUP DATABASE Adventureworks2008 TO DISK = 'D:\Backup\AdventureWorks.bak'

Here's an example of the compression ratio: a backed up 965MB database without compression resulted in a 636MB backup file. Using compression, the same database produced a 147MB backup file.

Learn to Merge
The new MERGE statement in SQL Server 2008 obviates the need for "IF-THEN" logic to decide whether a row needs to be inserted, updated or deleted. MERGE allows you to take care of the logic and the modification all in one shot. What's more, you can compare an entire record set all at once instead of going row by row. Here's a quick example of using MERGE:

MERGE tbl_address AS current_addresses
USING
( SELECT customer_objid = ddress_label, addressline1, addressline2, city, region, country, zip code, is_deleted FROM @addresses)
AS source_addresses
(address_label, addressline1, addressline2, city, region, country, zipcode, is_deleted)
ON
( current_addresses.address_label = source_address es.address_label )
WHEN NOT MATCHED THEN INSERT
(address_label, addressline1, addressline2, city, region, country, zipcode)
VALUES
(source_addresses.address_label, source_addresses.addressline1, source_addresses.addressline2, source_addresses.city, source_addresses.region, source_addresses.country, source_addresses.zipcode)
WHEN MATCHED AND source_addresses.is_deleted = 1 THEN DELETE
WHEN MATCHED THEN UPDATE SET
address_label=source_addresses.address_label, addressline1=source_addresses.addressline1, addressline2=source_addresses.addressline2, city=source_addresses.city, region=source_address es.region, country=source_addresses.country,zip code=source_addresses.zipcode

The USING section defines the "new" data, in this case a table variable. The ON section defines the join between the new and existing data. Finally, you have a series of MATCHED statements that do things like insert WHEN NOT MATCHED, update WHEN MATCHED or delete WHEN MATCHED and some other value indicates delete.