How to Fire a Trigger on Demand
Produced: 29/12/2013 15:14:00
Now, first of all, let me say that I’ve rarely found a good reason for needing to bypass a trigger or only wanting to fire one under certain circumstances (I have done this myself, but only in a cross site dual distributor replication using service broker… therefore not exactly a common requirement!!!), however, it is possible to achieve and surprisingly simple to do.

Basically all you need is to use context in your code by setting the context_info of your query as it runs. This will then be maintained for that spid.

Continued...
SHRINK a data file? Just say NO!!!
Produced: 22/12/2013 14:47:00
DBCC SHRINKFILE, DBCC SHRINKDATABASE, and Auto-shrink… they’re all truly, truly evil and should not be allowed near any system… ever!!!

Now, to be fair there are times when shrinking a log file is useful and even necessary. I’ve written my fair share of bug riddled code which has caused the transaction log to spiral into oblivion and leave me with a 20GB data file and 500GB of logs. In that case… shrink away. It’s really quite handy. But data files… no… just no… again no… please just no!!!!!!

Continued...
PItfalls of the MERGE Statement and NULL Values
Produced: 15/12/2013 11:28:00
We all know and love the MERGE statement (if you don’t know it, then read this first), but it does have its quirks, especially when using null values and these can be a major problem if they go undetected as they can leave huge holes in your data if you’re not careful.

Continued...
The MERGE Statement
Produced: 08/12/2013 11:07:00
So you’re working with some data and maybe you have another table which has newer, updated data... how do we combine these two tables to create one, correct, dataset?

This sounds simple enough but when you consider that the newer dataset may contain new records, require old records to be deleted, and also need certain records just to be updated. Suddenly this sounds more complicated than first anticipated.

Continued...
Temp Tables vs Variable Tables
Produced: 01/12/2013 16:39:00
This is possibly one of the most contentious issues I’ve come across in SQL Server. If you look online then there are numerous people arguing numerous aspects of these two types of table and weighing the benefits and pitfalls of each. This includes where they’re stored, how they’re used, which is best etc.

Well I figured that it was time for me to give my own spin on the topic so I’ve spent some time poking around in SQL Server to see what I can find and deduce. So here it is… my musings over a topic that tends to upset all and sundry which therefore means I’m likely to do the same. I’ll try not to disappoint by including wild accusations, general stabs in the dark, and back it all up with flimsy evidence at best.

Continued...