Microsoft's SQL Server 2008 delivers a number of new feature sets that aid IT professionals as well as enhance scalability, security, and productivity. Here are my 10 new favorite SQL 2008 features.
New data types. The DATE and TIME data types are two overdue new features in MS 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. MS 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.
Transaction-SQL IntelliSence and debugging. Two features that failed to make it in SQL 2005 but finally made it in 2008 are 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.
MS Office 2007 integration. SQL 2008’s enhanced integration with Office 2007 lets users create database enabled reports directly from MS 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.
Revamped SQL Server Reporting Services. SQL Server 2008 Reporting Services (SSRS) provides significantly improved reports rendering performance. In addition, SSRS’s Report Designer has been enhanced and Microsoft has improved charting components.
Filtering indexes. SQL 2008’s filtered index support lets you more efficiently index columns containing sparse data. The filtered indexes functionality lets SQL 2008 indexes ignore rows with no data and include only those rows in which are valid data values, which can improve access time for sparsely populated indexes by an order of magnitude.
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.
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.
Change data capture. This new feature can quickly show a measurable ROI for your SQL 2008 migration. 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.
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 standard database object naming conventions across your organization.
Resource Governor. Arguably the most important new enterprise feature in SQL Server is SQL Server 2008’s Resource Governor which 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.