Data Access Differences in SQL Server 2005

Hello,

We have a large set of applications that we developed and build for use with Borland InterBase (5.x-7.x). We have moved/migrated off of Borland InterBase to MS SQL Server 2005. We are find some situations where InterBase was an more elegant solution and finding it difficult to find the right way to change our applications to achieve the same result.

In InterBase, our Database Transactions allowed our user to make changes to the DB, Tables, Rows, etc, without blocking reads to the same DB, Tables, Rows, etc. We are looking for a mode in SQL Server 2005, which allows use this same functionality. We have been to a few Microsoft Conference's evaluating SQL Server 2005, and we discussed this with some Microsoft Tech repepresentatives at these conference who told use there was an roughly equivalent mode that was going to be available in 2005. Unfortunately I'm not able to remember the details of this information, does anyone know what new 'DB Modes' or Transaction Options where added that might allow InterBase-like features.

Thanks,

Wow!

The SQL Community doesn't know what SQL Conneciton/Transaction modes existing in SQL Server and how/which have changed in 2k5? Odd, I guess I came to the wrong place for help. Thanks for reading.

George

|||Moved post from SQL Server Database Engine to SQL Server Data Access Forum.|||

SQL Server 2005 supports all four SQL-99 Isolation levels, and also introduces two additional ones. MSDN article describing such. The default behavior is ReadCommitted which is usually preferable as it prevents dirty reads. You seem to desire dirty reads, so what you want is ReadUncommitted.

If you are using .Net code to access the SQL Server instance, you set this property on your SqlTransaction object. Here's the MSDN article on SQLTransaction.IsolationLevel.

You can also modify the Isolation Level using SQL by issuing the SET TRANSACTION ISOLATION LEVEL statement. Described on MSDN here.

|||

A_Bomb wrote:

SQL Server 2005 supports all four SQL-99 Isolation levels, and also introduces two additional ones. MSDN article describing such. The default behavior is ReadCommitted which is usually preferable as it prevents dirty reads. You seem to desire dirty reads, so what you want is ReadUncommitted.

No... I desire behavoir clostest to that of InterBases's defaults. Which seems (from the articles you site) to be closest matched by SNAPSHOT, or READ_COMMITED W/READ_COMMITTED_SNAPSHOT ON. Ie... reads not creating locks and writes not blocking reads because of locks (with reads getting a transaction 'consistent' version of the data).

A_Bomb wrote:

If you are using .Net code to access the SQL Server instance, you set this property on your SqlTransaction object. Here's the MSDN article on SQLTransaction.IsolationLevel.

You can also modify the Isolation Level using SQL by issuing the SET TRANSACTION ISOLATION LEVEL statement. Described on MSDN here.

This is the MSDN Article which I found my answer.

Thanks.