Export (0) Print
Expand All

New Features in SQL Server 2008 (ADO.NET)

SQL Server 2008 introduces new features and enhancements designed to increase the power and productivity of developers, architects, and administrators. Improvements include new Transact-SQL statements, data types, and management features. For the complete list of new features, see What's New (SQL Server 2008) in SQL Server 2008 Books Online. For Database Engine features, such as new Transact-SQL statements, management tools, and security enhancements, see What's New (Database Engine) in SQL Server 2008 Books Online.

Starting with the .NET Framework version 3.5 Service Pack (SP) 1, the .NET Framework Data Provider for SQL Server (System.Data.SqlClient) provides full support for all the new features of the SQL Server 2008 Database Engine. You must install the .NET Framework 3.5 SP1 (or later) to use these new features with SqlClient.

NoteNote:

No changes are required for applications built on top of ADO.NET 2.0 when upgrading a SQL Server database from version 2005 to version 2008. New features introduced in SQL Server 2008, such as FILESTREAM and table-valued parameters, will not be available.

The following topics are located in the ADO.NET documentation.

Date and Time Data in SQL Server 2008 (ADO.NET)

SQL Server 2008 introduces the following date and time data types.

  • date

  • time

  • datetime2

  • datetimeoffset

These new data types support a greater range of date and time values as well as time zone awareness. For the SQL Server documentation, see Using Date and Time Data in SQL Server 2008 Books Online.

FILESTREAM Data in SQL Server 2008 (ADO.NET)

SQL Server 2008 introduces the FILESTREAM storage attribute for binary data stored in a varbinary(max) column. This allows you to store the data on the local NTFS file system instead of in the database file. For the SQL Server documentation, see FILESTREAM Overview in SQL Server 2008 Books Online.

Table-Valued Parameters in SQL Server 2008 (ADO.NET)

Table-valued parameters are a new parameter type in SQL Server 2008 that gives you the ability to encapsulate rows of data in a client application and send it to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL. For the SQL Server documentation, see Table-Valued Parameters (Database Engine) in SQL Server 2008 Books Online.

Large UDTs in SQL Server 2008 (ADO.NET)

In SQL Server 2005, UDTs were restricted to a maximum size of 8 kilobytes. This restriction has been removed for UDTs that have a format of UserDefined. For the SQL Server documentation, see Working with CLR User-Defined Types in SQL Server 2008 Books Online.

The functionality available to a client application is dependent on the version of SQL Server and the compatibility level of the database. The Type System Version keywords in a SqlConnection.ConnectionString can be used to specify the client-side representation of SQL Server types. Explicitly setting the type system version that the client application was written for avoids potential problems that could cause an application to break if a different version of SQL Server is used. For example, UDT columns are represented as a byte[] array if you specify SQL Server 2000. If you specify SQL Server 2005, they are represented as managed types.

The available Type System Version values are described in the following table.

Value

Description

Latest

Uses the latest version that this client-server pair can handle. The version used will automatically move forward as the client and server components are upgraded. This is the default setting in ADO.NET.

SQL Server 2000

Uses the SQL Server 2000 type system.

SQL Server 2005

Uses the SQL Server 2005 type system.

SQL Server 2008

Uses the SQL Server 2008 type system. Datetime values are processed based on the type system version and the default language specified on the server.

For compatibility with features introduced in SQL Server 2008, you can explicitly supply the Type System Version in the connection string by using one of the following.

Type System Version= SQL Server 2008;
Type System Version=Latest;
NoteNote:

The type system version cannot be set for common language runtime (CLR) code executing in-process in SQL Server. For more information, see SQL Server Common Language Runtime Integration (ADO.NET).

You can also set the TypeSystemVersion property of a SqlConnectionStringBuilder when creating connection strings at run time. For more information, see Connection String Builders (ADO.NET).

Community Additions

ADD
Show:
© 2014 Microsoft