| Read consistency in Oracle and SQL Server [message #472268] |
Thu, 19 August 2010 10:14  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I realize that this could be classed as a question that should be asked in a SQL Server forum, if so I'm sure a moderator will say so.
I have been told that a distinguishing factor between Oracle and SQL Server is that Oracle always follows the rules for read consistency, but SQL Server doesn't. For instance, if you want to update the salary of every employee who has salary less than his department's average so that it equals the average, then Oracle has no problem with read consistency for the correlated subquery, such as:
update emp e set sal=(select avg(a.sal) from emp a where a.deptno=e.deptno)
where sal < (select avg(a.sal) from emp a where a.deptno=e.deptno);
but if you run this in SQL Server, you may get different (and inaccurate) results depending on the order of the rows and the execution plan. Can anyone confirm this? On current or past versions of SQL Server?
Regards,
The SQL Server Nemesis
|
|
|
|
| Re: Read consistency in Oracle and SQL Server [message #472269 is a reply to message #472268] |
Thu, 19 August 2010 10:33  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It depends on the isolation level you choose for the query or session (in SQL Server).
In default mode, READ COMMITTED, SQL Server uses row shared locks to guarantee the "read committed" level, but the locks are taken at the moment the query execution reaches the row and not at the beginning of the statement execution.
But SQL Server also knows another READ COMMITTED level if you set the READ_COMMITTED_SNAPSHOT to ON, then you have the same result than Oracle read consistency (but implemented in a different way).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
