SQL Server Blog

Having Policy Management set up is great… being able to schedule a job to start up, check your systems, and report anything that doesn’t fit your ideal is fantastic. But what about those policy breaches which cannot be automatically rolled back (On Change: Prevent is not an option) but for which you want to be immediately notified?

This was a new feature added to SQL Server 2008 and it’s just superb. I honestly don’t think it gets the appreciation that it deserves as it seems to be rarely seen or used despite the control it can give you over your instances.

This is a common “problem” that I see within SQL Server and was asked about just yesterday, but 99% of the times, and with this particular instance as well, it’s actually not a problem at all just a missing setting within the SQL Server Agent.

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.

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!!!!!!

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.

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.

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.

This is something that I never thought to blog about as I figured it was a common practice. I’ve no idea where I got that idea from as I’d never spoken to anyone about it, just used it within a couple of environments.



Anyway, having spoken to a couple of MCMs about this it turns out my “common practice” is actually incredibly rare and not many people know about it. Therefore, presuming this to be true, I figured I should blog about it.