Shrinking a Database with TRUNCATEONLY
Produced: 24/06/2016 06:51:00
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.

Better Row Estimates with Table Variables
Produced: 17/06/2016 12:54:00
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)…

Quick Case Statement Quiz
Produced: 10/06/2016 11:44:00
Everyone uses the case statement… I see it all over the place and, to be honest, nearly everyone uses it completely correctly.

CTEs are Updateable
Produced: 03/06/2016 11:31:00
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.

Transaction Log Tracking Inserts Updates and Deletes Over Time
Produced: 27/05/2016 11:21:00
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?

Looking for Inserts Updates and Deletes in the Transaction Log
Produced: 20/05/2016 10:56:00
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.

Execution Plans and XML Documents
Produced: 13/05/2016 09:49:00
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”.

Using CONCAT
Produced: 06/05/2016 09:43:00
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.

Unique Index with Union All
Produced: 29/04/2016 09:36:00
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.

Create UNIQUE Clustered Index
Produced: 22/04/2016 10:55:00
This is a bug bear of mine but something which I see all over the place in people’s code… if you’re going to create a clustered index on a table (including temp tables) and the key you’re using is unique, then let SQL Server know. You’ll be surprised how much difference it makes.

Easily Calculating End of Month
Produced: 15/04/2016 16:54:00
Something I’ve had to do a lot recently is to calculate financials which involved using an end of month date. This has always been a pain to calculate in SQL Server. There are a lot of ways of doing it, but all are annoying.

Revealing Predicates in Execution Plans (TF 9130)
Produced: 08/04/2016 21:21:00
In this episode of Fun with Flags I'm going to discuss trace flag 9130. I found this flag when watching a video on performance tuning by Joe Sack… it’s something that I never knew existed, but that I’ve actually now used a few times because it turned out to be surprisingly handy.

Calculating Cumulative Totals
Produced: 01/04/2016 14:55:00
Following on from my last post really, this was another aspect of the MI I was helping out with which benefitted from the use of some more modern code (SQL 2012 or above) in order to make code both clearer and more concise. It is also a lot faster and less strain on the server using the newer method.

The Best Way to Obtain Percentages
Produced: 25/03/2016 14:17:00
Again, this is something that I decided to write about because I see code in a lot of places used in a lot of different ways and within most MI there is always a need to obtain a percentage figure across a dataset and, for some reason, no-one seems to use SUM with OVER but rather they choose an older, longer method.

Drop Failed For User
Produced: 18/03/2016 12:11:00
This was an error that I hadn’t seen in a while and therefore I thought I would mention it. The error normally seen here is around the user owning a schema and so it cannot be dropped, but this was different:

A Little Happiness in SSMS
Produced: 04/03/2016 06:52:00
This is basically an introduction into something I want to play with in some upcoming posts, but to start with I thought I would introduce the topic with a little bit of happiness.

Using sp_executeSQL with Variables
Produced: 26/02/2016 05:29:00
When looking through other people’s code I tend to find that people struggle with dynamic SQL and how to write it in the best way. Specifically when it comes to using variables within the dynamic string.

Quick Note About INSERT INTO vs SELECT INTO
Produced: 19/02/2016 05:14:00
The debate about INSERT INTO vs SELECT INTO can be quite long and get people quite heated and therefore I’m not going to go into the entire thing right now, but what I did want to mention was something that I encountered the other day and which could be worth considering next time you’re deciding which approach to take.

THROW instead of RAISERROR
Produced: 12/02/2016 05:09:00
I’ve done a couple of posts about RAISERROR, but if you’re using SQL Server 2012 and above then there’s a new syntax also available which I think is a little better. That syntax is THROW.

Using RAISERROR with NOWAIT
Produced: 05/02/2016 05:07:00
Now we’ve used RAISERROR (as per my previous post) we need to address a specific issue which RAISERROR has and which we need to overcome in order to make this more useful. That would be that issue that SQL Server doesn’t show messages until the very end of execution. This will fix that issue.

Quick Guide to RAISERROR
Produced: 29/01/2016 05:04:00
Have you ever written code and you want custom error messages to be returned as you go? Or to stop execution where YOU define an error has occurred even when it’s not a real error? You can use raiserror to achieve this. This is simple syntax and quite powerful.

Kill All User Processes
Produced: 22/01/2016 05:01:00
This is a quick and dirty bit of code which I’ve used on occasion when trying to put a database which is in single user mode back into multi user mode but which is accessed so frequently that all you receive is an error message stating you are the deadlock victim.

Alerting For Replication Latency Using Tracer Tokens
Produced: 15/01/2016 07:53:00
This is a direct follow up to my previous post on Tracer Tokens and Replication Latency.

This post will include some simple code to check for excessive latency in your replication and, if appropriate, send out an email informing you of the problem.

Gathering Tracer Token Data
Produced: 08/01/2016 07:50:00
After my last post surrounding Tracer Tokens I was told by a friend that it was a little harsh to simply end saying that you can just join to a few system tables and make something meaningful. Therefore I’ve given in and this post will show you how you can use tracer token and system data to your advantage.

Tracer Tokens
Produced: 01/01/2016 07:42:00
If you’re using Transactional Replication then these can be invaluable to you. These are incredibly lightweight, easy to use, and they will help you with all manner of reporting and troubleshooting within your replicated environment.