New Features in SQL Server 2008 R2

 

 

So what does SQL Server 2008 have to offer throughout SQL Server 2005? Subsequent is an overview of the new features provided in SQL Server 2008:



New Storage Features

SQL Server 2008 offers a set of new features to reduce storage requirements and improve performance. One of the new features is FILESTREAM storage. FILESTREAM storage is a property that can be applied to varchar(max) columns; it enables SQL Server apps to store unstructured data, such as documents and images, directly in the NTFS file system while still preserving the behavior of a DB column. The advantages of FILESTREAM storage are improved performance and increased size of BLOB data, expanding from the 2GB limit of image columns to the available space in the file system. For more info on using FILESTREAM storage. Other storage features introduced in SQL Server 2008 are sparse columns and column sets. Sparse columns are ordinary columns that have an optimized storage format for null values. If you use sparse columns, you can also define a column set on the table that will return all sparse columns in the table. A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output.

Row-level and page-level data compression also are introduced in SQL Server 2008. Data compression helps to reduce both storage and memory requirements as the data is compressed both on disk and when brought into the SQL Server data cache. Row-level compression isn't true data compression but implements a more efficient storage format for fixed-length data. Page-level compression is true data compression, using both column prefix and dictionary-based compression.



New Data Types

SQL Server 2008 introduces a handful of new data types. Two of the most welcome of these are the new DATE and TIME data types. These new data types allow you to store date-only and time-only values. In addition, SQL Server now supports the DATETIME2 and DATETIMEOFFSET data types. DATETIME2 is a variation of the DATETIME data type that supports datetime values from 0001-01-01 to 9999-12-31 23:59:59.999999. DATETIMEOFFSET supports UTC-based datetime values that are time zone aware. The new Hierarchyid data type is a common language runtime (CLR) user-defined type (UDT) that offers a mechanism for representing and storing a tree structure in a table in an efficient manner. This data type is useful for storing data that represents a parent child, tree-like structure such as an organizational structure or a graph of links between web pages.

Spatial data types are introduced in SQL Server 2008 as well. There are two new spatial data types: geometry and geography. The geometry data type supports planar, or Euclidean(flat-earth), data. The geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. These new data types support the storage and manipulation of spatial data objects such as linestrings, points, and polygons. SQL Server 2008 also introduces a new user-defined table type that can be used as parameters in stored procedures and functions, as well as for defining table variables in a batch or the body of a stored procedure or function.