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 [...]
I’m working my way through the new TSQL operators in SQL Server 2005 and today its EXCEPT and INTERSECT.
Like the docs say, EXCEPT returns a list of distinct rows from the left hand result set that are not present in the right hand result set. While INTERSECT returns the distinct values that are present in [...]