In a previous article I demonstrated how use of READUNCOMMITTED (or the NOLOCK hint) can result in erroneous data, due to page splits. Examples abound of the more common issue with this isolation level, which occur when reading a row that is being updated in another transaction and then rolled back. These so-called ‘dirty reads’ …
Generally, when somebody posts a query using the NOLOCK hint, there are a variety of warnings about dirty reads, phantom reads and double-reading (due to page splits). The dirty read example is common and tends to be the main example used to demonstrate what to watch out for with NOLOCK, but I’ve never seen an …
Or rather, on a Test or Development server instance. If you need to do this to a Live server, then you have larger issues. Most sites have servers for Developers and DBAs to work their dark magic, as well as various test servers for the next level of testing. These servers do, of course, have …
Having been bitten by the delights of a Foreign Key(FK) with NOCHECK I thought it time to do a little digging. I’ve always been aware of the perils of this option but have encountered a table with this feature and I was caught out by it. I’m also aware of the impact it can have …
In a previous article I showed a basic example of Partition Switching, using animal names (http://wp.me/p3Vxvi-2u). While not a realistic example for everyday use it did get the principles across. However, certainly in my experience, partitioning is normally dependent upon a number – usually a date. In this article I’ll create a partition process that …
Actually, that isn’t entirely fair. I don’t have to eat sprouts, which is just as well. However, I do have to deal with Replication – certainly in my current job. Whereas my refusal to eat the Little Green Balls of Death won’t result in a divorce (because my wife, as wonderful as she is, does …
Want to delete a couple of records quickly from a large table; well DELETE is more than capable (indexing issues aside). Want to delete a couple of million from a large table quickly? Now it can get a little more interesting. Partitioning is a very useful tool for moving large amounts of data very quickly. …
I’m not a big fan of XML via T-SQL – far too long-winded a monster for my liking. However, there is no denying the usefulness of it and the fact that on occasions it simply doesn’t matter if you like an aspect of SQL Server, you may simply have to learn to use it. If …
In a previous posting I described a simple example of Pivot – a much used feature which I personally have problems recalling the syntax of (hence the article in the first place). It’s lesser known relative in UnPivot, which is designed to do the opposite – it transforms columns of data into rows. In the …
A couple of questions recently in SQL Server Central have related to Snapshot Backups and reminded me of an article I wrote a few years ago about such things for the Developers in my office. So, using that as a basis for this article I’m going to explain the basics of Snapshot Backups. In the …
Notes on SQL 