Well, not really without a transaction. But I was wondering what locking schemes are used when a query is run outside of an explicit (BEGIN / COMMIT) transaction. What impact does changes to the isolation level have?
The simple answer is its the same when compared to the locking examples I previously identified when running under [...]
While there are many types of locks in SQL Server 2005 issues generally revolve around Shared (read), Exclusive (write) and Update (read now to write later) locks on resources. And given that the majority of operations on a database are read operations, the taking and holding of shared locks is often the centre of attention.
An [...]
I’m trying to study for the next MS SQL Server 2005 exam and keep getting annoyed by practice questions that are wrong. The latest one to bother me was this statement in one of the answers:
“Both read committed snapshot isolation and snapshot isolation can produce phantom records.”
Wikipedia has a nice definition of the problem of [...]
In a previous post I spent some time looking at which ports are used by SQL Server 2005 including what port the Dedicated Administrator Connection uses. At the time it looked like it was possible to connect to the DAC for a server without the SQL Browser service running.
Since then as I’ve been studying [...]
Time to take a look at the performance of the queries I wrote when looking at the new TSQL clauses EXCEPT and INTERSECT in SQL Server 2005.
EXCEPT query performance
Lets start with query 3, the query that used EXCEPT and had the highest performance. Here’s the xml sqlplan, I’ve altered the original SQL slightly to [...]