SQL Server Blog

I received a comment the other day on my previous post about never shrinking a database in which I was asked whether TRUNCATEONLY was safe to use as Books Online state that no data pages are moved.

In this episode of “Fun with Flags” we’ll be looking at TF 2453.



Having been recently playing with SQL Server 2014 I was intrigued to see the following in the list of fixes included for Cumulative Update 3 (note this is also included in SQL Server 2012 Service Pack 2)…



“FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014”. As I’m not a complete fan of table variables (they’re good, but for limited uses), I was intrigued.

Everyone uses the case statement… I see it all over the place and, to be honest, nearly everyone uses it completely correctly.



However, there are those instances in which I see it used incredibly badly so I thought I’d make it into a quick quiz. (Although it’s not really long enough to be a quiz, and it contains answers… therefore more just me showing examples really).

Now to all those people saying “well, duh”, this clearly isn’t a post for you, but this is genuinely something I didn’t realize until I saw it the other day and went to try it out for myself.

Have you ever wanted something which will reliably keep you informed as to the number or Inserts, Updates or Deletes against table in your system? Maybe to keep an eye on the volume of modifications happening in a replicated environment?



If you put some effort in then this is something that can be done via the Transaction Log.

This will form the basis of my next post but it needed covering separately in the first place… that being how you can see Inserts, Updates, and Deletes within the transaction log, and how you can tell which table they were performed against.

Over the past few months I’ve been working with a lot of XML and stored procedures and it’s been a different experience, and not something I’d always refer to as “fun”.



But I have learnt a couple of things along the way which I’ve found interesting… mostly involving XML Documents and the Optimizer.

This is another new function in SQL Server 2012 which is incredibly useful and I’ve been using everywhere I can in place of the previous ISNULL or COALESCE functions.



Basically this function, as you would guess, concatenates strings into one large string. Nothing too exciting there because we have the ability to do this using +… but where this comes into a league of its own is that CONCAT caters for NULLS… something that we would otherwise struggle to do.

This follows on directly from the previous post in regard to ensuring that you use UNIQUE if possible on an index as I’ve also found that not having UNIQUE can make quite a hefty difference when using UNION ALL.