Advanced T SQL

This is a useful script that will let you dynamically script a table… yes, I realise that SSMS offers a point and click function which does the same, but if you’re building a script that you wish to run over and over on a changing architecture then you can’t afford to spend your time scripting from SSMS and maintaining all changes to the scripts over time.

So you might be wondering what use Extended Properties could have outside of the simplistic “this helps explain columns” approach that I mentioned?

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?

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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 turn this:

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.

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.

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.

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.

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.

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?

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.

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.

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.

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.

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.