In a previous article, I took a look at some cool new features included in SQL Server 2008. Recently, the RTM (Release to Manufacturing) version of SQL Server has been released which means that it is time to look at some additional new features that have been included in the product.
As the name may suggest, Filestream enables the storage of unstructured data such as documents and videos. This feature integrates with the NTFS files system by using the nvarchar(max) data type to store data on the file system. This feature is great for when you're currently storing documents inside of your SQL Server system that are larger than 1 MB and fast read access is important.
Sparse columns are very similar to other types of database columns, except that they are specialized and optimized for null values. If you have a table that holds a lot of NULL values, first consider your table design. If your design is solid or cannot be altered, then your table may be a candidate to take advantage of sparse columns. The rule from Microsoft is to consider using Sparse columns when 20-40 percent of the values in a column will be NULL.
New Date & Time Data Types
Prior to SQL Server 2008, there was no way to native data type to store time related data. Time data was only available as part of the datetime (or smalldatetime) data types. With SQL Server 2008, TIME is a separate data type, able to hold time values such as '23:59:59.9999999'. Along with the new TIME data type, there are additional data types such as datetime2, which holds additional nanosecond data and the datetimeoffset data type, which can hold datetime data that is timezone aware.
Spatial Data Types
Two new data types are included in SQL Server 2008; geometry and geography. These data types hold information regarding the physical location and shape of geometric objects, which allow for applications to be built that are geographically aware.
Reporting dependencies has never been consistent or reliable in SQL Server. The difficultly is that when objects are added, modified or dropped, special actions must be taken by the database engine to ensure that the dependency chain is correct. In 2008, new dynamic management views are included keep track of these dependencies so that reliable reporting can be done one these objects.
Advanced Auditing Features
SQL Server 2008 includes several new features that facilitate the auditing of data. These features include a new Auditing feature that allows you to easily create customized audits of database engine events. Change Data Capture, while not auditing specific, makes data changes typically used for loading a data warehouse easily available in an easily used format. SQL Server 2008 also includes a Data Collector feature that allows the DBA to gather and compare data that is gathered from several different sources.
Policy Based Administration
This is one of my favorite features of SQL Server 2008. This feature allows a database administrator (DBA) to easily and uniformly set policies across servers that ensure system rules are met. These policies can be rules such as simplifying administration tasks, preventing unauthorized system changes, making sure code compliance is met, ensuring best practices are met.
SQL Server Management Studio 2008 includes Intelli-sense which is a familiar feature in other Visual Studio products. This feature presents a popup box when typing SQL statements to give options to choose columns from tables that are involved in your queries.
Central Management Servers
This feature allows the database administrator (DBA) to administer multiple SQL Servers easily and efficiently. These servers allow for multi-server queries and for policy based management policies to be executed against multiple servers at the same time.
This form of syntactic sugar is present in many other programming languages such as C++ or C#. Consider the following TSQL statement.
SET @x = @x + 1
Using compound operators, the above statement can be rewritten in the following manner:
SET @x += 1
This statement, while not groundbreaking, takes TSQL a step forward in the evolution of programming languages.
As data becomes more and more sensitive, the ability to keep the data secret becomes more important. SQL Server 2008 includes the ability to encrypt data stored in the database transparently, which keeps you from having to alter applications to take advantage of the feature. This feature prevents unauthorized users from reviewing the data even if they have the backup files.
More to Come
Today I took a look at some great additional features included in SQL Server 2008. In the following weeks I will take a more in-depth look at each of these new features so that when you do decide to upgrade your database system to SQL Server 2008 you are equipped to hit the ground running in implementing these new features.
Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.