Programmability Enhancements (Database Engine)
Programmability enhancements in the Database Engine include new data storage features, new data types, new full-text search architecture, and numerous improvements and additions to Transact-SQL.
Data Storage
Compressed Storage of Tables and Indexes
SQL Server 2008 supports on-disk storage compression in both row and page format for tables, indexes, and indexed views. Compression of partitioned tables and indexes can be configured independently for each partition. For more information, see Creating Compressed Tables and Indexes.
FILESTREAM Storage
FILESTREAM storage enables SQL Server applications to store unstructured data, such as documents and images, on the file system. This enables client applications to use the rich streaming APIs and performance of the file system while maintaining transactional consistency between the unstructured data and corresponding structured data. For more information, see Designing and Implementing FILESTREAM Storage.
New Collations
SQL Server 2008 introduces new collations that are in full alignment with collations that Windows Server 2008 provides. These 80 new collations are denoted by *_100 version references. These collations provide users with the most up-to-date and linguistically accurate cultural sorting conventions. For more information, see Collation and Unicode Support and Working with Collations.
Partition Switching on Partitioned Tables and Indexes
Partitioning data enables you to manage and access subsets of your data quickly and efficiently while maintaining the integrity of the entire data collection. Now you can use partition switching to quickly and efficiently transfer subsets of your data by switching a partition from one table to another.
For information about partition switching concepts, and to see sample code that implements partition switching, see Transferring Data Efficiently by Using Partition Switching and Partition Switching When Indexed Views Are Defined.
Sparse Columns and Column Sets
Sparse columns are ordinary columns that have an optimized storage format for null values. Consider using sparse columns when at least 20 percent to 40 percent of the values in a column will be NULL. For more information, see Using Sparse Columns.
Tables that use sparse columns can designate a column set to 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. For more information, see Using Column Sets.
Spatial Data Storage, Methods, and Indexing
Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.
For more information, see Working with Spatial Data (Database Engine), geometry Data Type Method Reference, and geography Data Type Method Reference.
Spatial indexes improve the efficiency of certain set-oriented operations on spatial objects (spatial data). A spatial index reduces the number of objects on which relatively costly spatial operations need to be applied. For more information, see Working with Spatial Indexes (Database Engine).
Wide Tables
Wide tables are tables that contain one or more column sets. A wide table can contain up to 30000 columns, 1000 indexes, and 30000 statistics. For more information, see Special Table Types.
Data Types
Date and Time Data Types
SQL Server 2008 introduces four new date and time data types. These types enable applications to have separate types for date or time, increased year range, increased fractional second precision and time-zone offset support. For more information, see Using Date and Time Data.
hierarchyid Data Type
SQL Server 2008 introduces a new system-provided data type to encapsulate hierarchical relationships. Use hierarchyid as a data type to create tables with a hierarchical structure or to reference the hierarchical structure of data in another location. Use hierarchical methods to query and perform work with hierarchical data by using Transact-SQL.
Examples where the hierarchyid type makes it easier to store and query hierarchical data include the following:
An organizational structure
A file system
A set of tasks in a project
A taxonomy of language terms
A graph of links between Web pages
The related SqlHierarchyId CLR data type is available for client applications. For more information, see Using hierarchyid Data Types (Database Engine).
Spatial Data Types
SQL Server 2008 introduces two 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. For more information, see Working with Spatial Data (Database Engine).
User-Defined Table Type
The Database Engine introduces a new user-defined table type that supports representing table structures for use as parameters in stored procedures and functions, or in a batch or the body of a stored procedure or function. You can create unique constraints and primary keys on user-defined table types.
For more information, see User-Defined Table Types.
User Defined Types (UDT)
The maximum size of a UDT has been increased to 2147483647 bytes.
Full-Text Search
SQL Server 2008 Enterprise full-text search introduces a new full-text architecture. Full-text search is now a fully integrated feature of a database. As a result, full-text search has been significantly enhanced in the following areas:
Full-text catalogs are integrated into the database, rather than being stored in the file system, so moving a database now automatically moves the full-text catalogs.
SQL Server 2005 noise words have been replaced by stopwords. Configuration of stopwords is managed by a new set of stoplist objects. These objects are associated with the database and moved around with it. This maintains the stopword configuration intact during manageability operations such as backup and restore, detach and attach, and copying a database with the Copy Database Wizard. For more information, see Stopwords and Stoplists.
Query and indexing performance have improved dramatically in some specific scenarios as a result of integration with other important query components such as the query optimizer. For more information, see Performance Tuning and Optimization of Full-Text Indexes.
New tools help you understand the raw content of a full-text index and the behavior of a specific word breaker in a given query term or phrase. For more information, see sys.dm_fts_index_keywords (Transact-SQL), sys.dm_fts_index_keywords_by_document (Transact-SQL) and sys.dm_fts_parser (Transact-SQL). These and other new dynamic management views and stored procedures facilitate troubleshooting full-text search issues. For information about these troubleshooting resources, see Troubleshooting Full-Text Search.
A new family of word breakers improves word-breaking accuracy and extends the set of languages available for full-text search. For more information, see Word Breakers and Stemmers.
For more information, see Behavior Changes to Full-Text Search in SQL Server 2008, Deprecated Full-Text Search Features in SQL Server 2008, Breaking Changes to Full-Text Search in SQL Server 2008 and Full-Text Search Architecture.
Transact-SQL
Compatibility Level
ALTER DATABASE SET COMPATIBILITY_LEVEL replaces sp_dbcmptlevel for setting the database compatibility level. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
Compound Operators
Operators that perform an operation and set a variable to the result, for example SET @x += 2, are available. For more information, see Compound Operators (Transact-SQL).
CONVERT Function
The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values. For more information, see CAST and CONVERT (Transact-SQL).
Date and Time Functionality
SQL Server 2008 includes support for the ISO week-date system. For more information, see DATEPART (Transact-SQL).
GROUPING SETS
The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause. There is a new function, GROUPING_ID(), that returns more grouping-level information than the existing GROUPING() function. The non-ISO compliant WITH ROLLUP, WITH CUBE, and ALL syntax is deprecated. For more information, see Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS.
MERGE Statement
This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. The syntax allows you to join a data source with a target table or view, and then perform multiple actions based on the results of that join. For more information, see MERGE (Transact-SQL).
SQL Dependency Reporting
SQL Server 2008 introduces a new catalog view and system functions to provide consistent and reliable SQL dependency reporting. You can use sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities to report on cross-server, cross-database, and database SQL dependencies for both schema-bound and non-schema-bound objects. For more information, see Reporting SQL Dependencies.
Table-Valued Parameters
The Database Engine introduces a new parameter type that can reference user-defined table types. Table-valued parameters can send multiple rows of data to a SQL Server statement or routine (such as a stored procedure or function) without creating a temporary table. For more information, see Table-Valued Parameters (Database Engine).
Transact-SQL Row Constructors
Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement. For more information, see INSERT (Transact-SQL).