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
上一页 [1] [2] [3] [4] |
