useful code

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.

This is a very common thing to need to do and will put together the pieces of my last two posts into one large post in order to achieve the desired outcome.

Cleary this is a direct follow-up to my last post in which I showed how to merge a partition… this is the exact opposite in which we’ll split a partition into 2.

In my previous post I was stating that we need to be more intelligent with our stats updates rather than hitting a whole table with a sledgehammer and potentially suffering poor performance as a result.

This is an alternative to native table Partitioning, and something I would potentially rather use instead whether I have Enterprise Edition or not.

This is actually an addendum to my last post as I referred to using SQL compression and provided a script in which you could compress a set of tables.


However, the problem is knowing which tables you should compress?

This is a simple script that’s held on my website mostly for reference as it simply takes any table and then compresses it with PAGE level compression.

At some point I will explain the nuances of the Columnstore index, how they work, when and where you should use them, and what they’re for… but in the mean time I have recently had situations in which I have upgraded to SQL 2014 and have needed to Columnstore a table.

One of the best features of a database snapshot is the ability to roll back your changes and revert to the snapshot.

This is pretty simple really, but it’s useful code and therefore I wanted it on my website mainly for my reference on those days in which I can’t remember the syntax and want a simple copy and paste option. We all have our forgetful moments after all :o)

There are a few flaws with the sp_rename method when switching tables or even simply renaming them… note that these aren’t bugs, they’re simply annoyances encountered which can otherwise be avoided.

These aren’t new functions as they were brought in for SQL 2012, but I was using them the other day in a SQL 2014 environment and the person I was talking to at the time had never heard of them, so I thought I’d make mention of them here in case other people hadn’t heard of them either.

In some code I was writing recently I needed to perform this action which, having had a quick look on the internet, seems to be a topic that annoy people as it’s not something natively built in to SQL Server and to obtain it yourself can be a pain.

Once again I’m going to refer to previous posts here as this is actually an extension of my previous two posts.


This was something I came across recently when working on a tuning project… the original code was very slow and this was quickly traced to a good old function.

This is a direct follow on from my previous post really as one of the most common uses for the Table Type is that you can use it (in SQL Server 2008 onwards) to pass a table as a parameter into a Stored Procedure. This is incredibly useful as one of the things that I see most often asked on the internet is how to pass tabular data to a stored procedure.

I would imagine that most people tend to write “set nocount on” at the start of most code blocks and procedures as standard these days but, having been caught out with this recently, I have found a new “set” command that I’ve started to add to my code as well.

This is something I see asked all over the internet and it’s also something for which there are a LOT of incorrect responses, dodgy solutions, and poor advice (including on the MSDN website). Therefore as I’ve had to resize a few transaction logs recently I thought I’d share the code and process I’ve used.

This isn’t going to anything in depth around the subject such as what you should or shouldn’t have as your standard settings etc. but instead it’s actually just a handy piece of code I knocked together which I happen to use quite a lot, especially when in a new company, just to see how SQL Server is set up.

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)…

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

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?

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

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.

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.

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.

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:

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.

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.

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.

This is a handy hint as to how to access a network drive from within SQL Server. It just happened to be something that I was using in some code and was asked what the code was doing.

I’ve been in numerous situations now in which people require security in their SQL Server (obviously) but find that the provided db_dataReader and db_dataWriter aren’t quite good enough. They require execute rights on all procedures, but find this is hard to set up.

This is another useful piece of code (well, I find it handy anyway) which I wrote to help populate a dashboard.

First things first… NEVER shrink your database unless you REALLY have to.

This was an incredibly cool thing that I learnt the other week whilst attending a SQLSkills course. I have previously made mention of Vertical Partioning and how useful it can be, BUT it does have the downside of re-architecting… this can change that.

This is a simple piece of code pinched from the Microsoft Performance Dashboard, but it’s useful and needs highlighting on its own as I tend to use it extensively.

This was something that I have wanted to be able to do for a while. I like having wait stats to look at (I’ll do a blog about those at some point - this is for people who already know what they are)… but until Extended Events came about these were only available, to my knowledge, at the server level. This was great, but what if you wanted to know what your specific query was doing?

This was something I created due to being fed up with constantly having to set up Profiler, having it “forget” my custom traces, and also wanting the data in SSMS so that I could query it if necessary.

We know that sp_spaceUsed is a great way to obtain a few simple figures about a table such as row count, table size, and index size… but these are all cumulative. Therefore what happens if you want to know the specific size of an individual index?

This is actually an extension to my previous No Job History Showing post in which I pointed out how easy it is to end up without any job history in your SQL Server Agent and therefore not be able to diagnose problems.

This is a personal pet hate of mine and of many people I’ve spoken to… we have all these lovely tools to obtain a list of what’s happening on our SQL Server but invariably sp_who, sp_who2, and sysprocesses all find it highly entertaining to provide you with a seemingly encrypted result instead of a job name.

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 due to incredibly bizarre request I received which happened to throw me a little. Basically I was told by a business that they actually had no idea how many SQL Servers they had or on which machines they could be running. They asked if I knew a way to find them.

Ever been in one of those situations in which you see endless blocking whilst replication makes new generations and you’re left wondering if there’s a specific table causing it or whether it’s just generic replication volume which is causing your problem?

This is genuinely one of the things I’m most asked, and by all manner of people from DBAs to Devs because it’s never as simple as it looks and that is how to obtain the start of day from a current_timestamp (or any other datetime value).

Obviously I would be shocked if you don’t already have something in place which will let you know that your SQL Server Cluster has failed over to another node. But I always like to have my own backups to any other automated alerting in order to keep me informed.

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.

There are numerous variants of sp_who2 kicking around the internet and most of them go to absurd levels of intricacy.

There are numerous times you might want your dates returned in a different format and SQL Server will cater for this.