SQL Server 2008 Best Features

SQL Server 2008, Microsoft’s data platform of the future, includes a host of new features that IT professionals need to know about. Some of SQL Server 2008’s new features enhance its scalability, security, and productivity. Other features are designed to improve database administration and provide better data accessibility for end users. Let’s explore ten of my favorite SQL Server 2008 features.

10. New data types—The DATE and TIME data types are two of the most overdue new features in SQL Server 2008. You can use the DATE and TIME data types to split your date and time information into separate and distinct columns in your database. SQL Server 2008 also includes a new FILESTREAM data type for large object data, as well as new GEOGRAPHY and GEOMETRY spatial data types for mapping applications.

9. T-SQL IntelliSense and debugging—Two features that were originally supposed to be in SQL Server 2005 but didn’t make it into that release are support for T-SQL IntelliSense and debugging from within Query Editor. SQL Server 2008’s IntelliSense provides T-SQL syntax checking and database object prompting. The integrated debugging feature lets you set breakpoints; single step through T-SQL code; and view the Locals, Call Stack, and Quick Watch windows.

8. Microsoft Office 2007 integration—SQL Server 2008’s enhanced integration with Office 2007 lets users create database- enabled reports directly from Microsoft Word 2007 or Excel 2007. These reports can then be published and shared with other users via Microsoft Office SharePoint Server (MOSS) 2007 or Windows SharePoint Services.

7. Revamped SQL Server Reporting Services—SQL Server 2008 Reporting Services (SSRS) provides significantly improved report rendering performance. In addition, SSRS’s Report Designer has been enhanced and Microsoft has provided improved charting components.

6. Filtered indexes—SQL Server 2008’s filtered index support lets you more efficiently index columns containing sparse data. The filtered indexes functionality lets SQL Server 2008 indexes ignore rows with no data and include only those rows in which there are valid data values, which can improve access time for sparsely populated indexes by an order of magnitude.

5. Transparent data encryption—Although previous versions of SQL Server have offered cell-level encryption, using it required special application coding. SQL Server 2008’s transparent data encryption (TDE) lets you encrypt one or more databases without having to make changes to the application code.

4. Database and backup compression—SQL Server 2008 supports two levels of lossless data compression: table-level and database-level. Although there’s some additional CPU overhead when you use compression, the reduced I/O can speed up queries and reduce backup times.

3. Change data capture—This feature can quickly show a measurable ROI for your SQL Server 2008 migrations. Many data warehousing and distributed data scenarios require custom coding to capture daily changes and send those changes to one or more target systems. Change data capture (CDC) can automatically capture all database changes, letting you keep your distributed systems up-to-date without having to perform custom coding.

2. Policy-Based Management—Policy-Based Management lets DBAs create server and database policies that facilitate the central management of multiple remote SQL Server systems and enforce the use of corporate standards. For example, DBAs could create policies that enforce the use of standard database object naming conventions across your organization.

1. Resource Governor—Arguably the most important new feature in SQL Server from an enterprise standpoint, SQL Server 2008’s Resource Governor lets DBAs control server resource utilization for different types of workloads. For example, the Resource Governor can prevent poorly constructed user-created queries from adversely affecting the overall performance of the server by limiting the CPU and memory resources allocated to those queries.