SQL Optimisation Tip

There is a saying 'There are many ways to skin a cat' this is rarely as true as it is with optimising SQL.

However, I have a tip for you for SQL Server. You can specify that you wish to do a 'Dirty Read'. This means that you want the select to go ahead without worrying if there are locks on the table / row because of inserts, updates, and deletes. So if a row that fits your criteria is in the process of being deleted it will still end up in your results.

So obviously this is not a solution for every query but it can really relieve some pressure if you do not mind the draw backs.

SELECT *
FROM myTable WITH (NOLOCK)

It will also work when you do joins but you must include the WITH (NOLOCK) command after every table declaration.

SELECT *
FROM myTable a WITH (NOLOCK)
INNER JOIN myOtherTable b WITH (NOLOCK)
    ON a.ID = b.ID

By Simon Baynes