ANSI SQL specification: • READ UNCOMMITED • READ COMMITTED • REPEATABLE READ • SERIALIZABLE SQL Server supports all four isolation levels, and did so even prior to SQL Server 2005. Versioning databases typically support only READ COMMITTED and SERIALIZABLE. READ COMMITTED implements statement-level versioning and SERIALIZABLE implements transaction-level versioning in versioning databases. READ COMMITTED is the default behavior for almost all databases, whether locking or versioning is used. Statement-level versioning is enabled and it is the default behavior by setting database options on a per database basis. When statement versioning is enabled, specifying IsolationLevel.ReadCommitted or IsolationLevel.ReadUncommitted uses this behavior. To support transaction-level isolation, SQL Server 2005 defines a new isolation level IsolationLevel.Snapshot. SqlClient (and only SqlClient) supports this isolation level. This isolation level was required because you can turn on statement-level or transaction-level versioning separately and IsolationLevel.Serializable is already used by SQL Server to correspond to locking behavior. DataTypes - UDTs, the XML data type, and "MAX" BLOBs and CLOBs SQL Server 2005 adds support for user-defined types, a native XML data type and better large data support. Large data support is improved by using the Transact-SQL types VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX). User-defined types and a native XML type are defined by the SQL:1999 and SQL:2003 specifications. To use these data types with SqlClient, new classes in the System.Data.SqlTypes namespace are defined (SqlUdt and SqlXml), support is added to the SqlDbTypes enumeration and IDataReader.GetValue was enhanced to support returning UDTs as .NET Object types, and support returning XML as a .NET String. These new SQL Server 2005 types are supported in DataReaders returned from SQL SELECT statements and as Parameters using SqlParameter. A special class, SqlMetaData, can return information about extended properties of these new data types, such as the XML schema collection that a strongly typed XML column adheres to, or the database name of a UDT. You can use these types from the client directly, in generic code, and also in the DataSet. Finally you can perform partial updates on the "MAX" data types from the client, which required using special SQL functions before ADO.NET 2.0. There will be future articles on this site that delve into the details. Conclusion Whew! That''''s a lot of features, almost too many to keep track of. To help you from drowning in the new sea of functionality, I conclude with a chart of each new feature and which database, provider, and version you must have to make it work. I currently only have information on the four providers that are part of ADO.NET, but other provider vendors will likely join in shortly. In future articles, I hope to expand the chart. New Feature Availability All Providers SQL Server 7/2000 SQL Server 2005 Provider Factories X X X Runs w/Partial Trust X X X Server Enumeration X X X Connection String Builder X X X Metadata Schemas X X X Batch Update Support X X X Provider-Specific Types X X X Conflict Detection X X X Tracing Support X X X Pooling Enhancements SqlClient and OracleClient X X MARS X SqlNoticicationRequest X SqlDependency X IsolationLevel.Snapshot X Asynch Commands X X X Client Failover X Bulk Import X X X Password Change API X Statistics X X X New Datatypes X Promotable Tx X X AttachDbFileName X X Bob Beauchemin is an instructor, course author, and database curriculum course liaison for DevelopMentor. He has over twenty-five years of experience as an architect, programmer, and administrator for data-centric distributed systems. He''''s written articles on ADO.NET, OLE DB and SQL Server for Microsoft Systems Journal and SQL Server Magazine and others, and is the author of the books, A First Look at SQL Server 2005 for Developers and Essential ADO.NET