Practical Applications of Extended Properties
Produced: 25/12/2016 12:01:00
So you might be wondering what use Extended Properties could have outside of the simplistic “this helps explain columns” approach that I mentioned?

Continued...
Transaction Log Tracking Inserts Updates and Deletes Over Time
Produced: 15/11/2015 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?

Continued...
Looking for Inserts Updates and Deletes in the Transaction Log
Produced: 08/11/2015 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.

Continued...
A Little Happiness in SSMS
Produced: 23/08/2015 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.

Simply it’s the lesser known ability (it’s VERY rare to find anyone who knows about this) of SSMS to natively produce shapes and graphs based on data you provide it.

Continued...
Using sp_executeSQL with Variables
Produced: 16/08/2015 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.

Generally people will start to concatenate all over the place, which tends to involve awkward conversions or casts and can start to look really messy and unreadable.

Continued...
THROW instead of RAISERROR
Produced: 02/08/2015 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.

This does a very similar job to RAISERROR but has a couple of advantages that I want to highlight.

Continued...
Using RAISERROR with NOWAIT
Produced: 26/07/2015 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.

Continued...
Quick Guide to RAISERROR
Produced: 19/07/2015 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.

Continued...
CTEs - Formatting Not Performance
Produced: 18/01/2015 19:13:00
I was reading a forum the other day in which someone was asking whether they would be better off changing their code to use CTEs for performance gains. I was surprised by the amount of responses by people thinking that CTEs are a performance tool.

Continued...
Random Number Generator
Produced: 07/12/2014 13:03:00
Surprisingly this is something that I come across quite often and it’s not the easiest thing to achieve in SQL Server in certain circumstances… a solid piece of code which generates a random number.

Continued...
Which Columns Are Being Updated
Produced: 15/06/2014 10:14:00
This came about as a result of a series of mass updates being applied to a replicated table. This series of updates was causing replication to lock up and become so far behind that the only acceptable course of action was a full rebuild. This is by far and away the course of action I least like taking, but in this example it was unavoidable.

Continued...
Copying User Access From Database to Database
Produced: 01/06/2014 16:59:00
This topic came about the other day when I was required to move some tables into a completely new database as part of a schema restructure and new project.

The issue was that I was that in order to maintain existing functionality I was using synonyms from the old database to the new, but this meant that permissions all required copying from the old database to the new otherwise access would fail.

Continued...
Don't Panic Your Code Isn't Lost
Produced: 25/05/2014 16:58:00
The other day I had a very stressed developer coming to see me… he had been in the middle of coding something when his PC pulled the ever inventive “Blue Screen of Death” trick on him.

He wouldn’t have been quite so bothered but he had been working on new piece of SQL, hadn’t been saving his work as he went along, and had not yet written it to disk as a procedure but was working in ad-hoc mode whilst he fine tuned it.

Continued...
Nicely Formatted HTML Email of SQL Table
Produced: 30/03/2014 16:49:00
Okay, in my last post I showed you how easy it is to include a table of results in an email, but by all accounts it was a little bit bland when produced. So in this post I’ll deviate a little from SQL Server to give you some HTML which will

Continued...
SQL Table of Results in an HTML email
Produced: 23/03/2014 16:44:00
To be honest I couldn’t think of a good title for this particular post so I’ve gone with the above. Basically what I’m referring to is that sometimes a simple text email isn’t enough and you want to include some output with it… therefore what you need is a table.

Creating a table in an email body is not the simplest of things and I’ve seen numerous people struggle to create one, ending up doing something incredibly complex with their output, or even trying external plugins.

Continued...
SQL Column to Comma Separated List
Produced: 16/03/2014 17:57:00
This is a handy little “trick” which I use all the time when I need to turn the column of a table into a comma separated list. It’s incredibly quick, has very little overhead, and it’s effective.

Continued...
sp_Merge
Produced: 09/03/2014 17:51:00
There are numerous times in which I, and many devs in the company, need to merge new data into existing tables and prior to the MERGE command this tended to be done in a procedure which created a “dev” table (table prefixed with “dev_”), executed multiple comparisons and joins, and then proceeded to perform an sp_rename to swap the dev table for the live one.

In comparisons, the MERGE statement out performed this proc by a country mile and therefore the recommendation was to move to this style of code. However, the code is quite long winded and people weren’t using it. Hence sp_merge.

Continued...
Different Query Results with row_number
Produced: 02/03/2014 18:30:00
Today I was asked a question that left me baffled for a good few minutes whilst trying to come up with an answer. Sadly my mistake all along was that I trusted a developer. Ah well, happens to everyone sometimes.

Basically the issue was that QA had noticed that there were inconsistencies with data between our staging and QA servers. Obviously this shouldn’t be the case and the developers tracked it down to a specific stored procedure… one server returned one set of results, the other server a different set. Therefore the issue ended at my doorstep.

Initially it was a simple case of running the proc on a variety of test servers… initially all gave the same result and I was left completely baffled as to the problem. But then, when re-running the query, suddenly my results changed.

Continued...
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...
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...
Single Value Variables
Produced: 21/07/2013 19:02:00
These are incredibly useful when writing complex T-SQL or stored procedures etc. as they are very versatile and simple to use. They are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.

Effectively they are storage in which you can place a value to be used and manipulated throughout your code and refer to it by the variable name.

Continued...
Table Value Variables
Produced: 14/07/2013 12:06:00
As with single value variables, these are very simple and powerful objects which are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.

However, in contrast to single value variables, these can hold multiple values and work in the same way as temporary and normal tables.

Continued...
PATINDEX
Produced: 12/05/2013 18:46:00
In terms of functionality, this is very similar to CHARINDEX in that it simply looks for a pattern within a string.

However, where it differs is that PATINDEX requires you to use wildcard characters as part of your search, something that CHARINDEX will not, but it is less powerful in some ways in that it does not allow you to specify a starting point and therefore will only ever find the first occurrence of a pattern in a string.

Continued...
REPLACE
Produced: 21/04/2013 09:44:00
This is a very common function when performing string manipulation… you simply pass in a character or string that you wish SQL to look for, the text in which to look, and what you want the character or string replacing with.



Continued...
DATEADD, DATEPART, and DATEDIFF
Produced: 31/03/2013 18:41:00
I've always found that no matter what I end up coding, I always have to manipulate a date or two. Whether it's finding last week's date, extracting the minute from a datetime, or calculating how long someone has taken between orders, it always comes down to manipulating dates.

Continued...