Script to Update Specific Stats
Produced: 05/02/2017 12:27:00
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.

However, wouldn’t it be great to be more proactive about the whole thing…

Continued...
Creating a Partitioned View
Produced: 18/09/2016 09:08:00
This is an alternative to native table Partitioning, and something I would potentially rather use instead whether I have Enterprise Edition or not.

Partitioned Views are also available in any edition, which is always a bonus.

Continued...
Test for Compression Savings
Produced: 26/06/2016 19:21:00
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.

Continued...
Script to Compress All Tables
Produced: 19/06/2016 19:19:00
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.

Continued...
Columnstore A Table
Produced: 12/06/2016 19:15:00
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.

Continued...
Rolling Back to a Database Snapshot
Produced: 05/06/2016 22:06:00
One of the best features of a database snapshot is the ability to roll back your changes and revert to the snapshot.

Therefore what you can do is take a snapshot, make a load of changes (whether testing new code, trying out something new etc) and then if it all goes pear shaped you can simply revert back to the snapshot and all will be well.

Continued...
Creating a Database Snapshot
Produced: 29/05/2016 22:04:00
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)

Continued...
Alternative to sp_rename
Produced: 10/04/2016 10:08:00
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.

Continued...
Lead and Lag Built In Functions
Produced: 03/04/2016 17:48:00
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.

Continued...
Obtaining the Max Values Across Columns
Produced: 27/03/2016 17:45:00
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.

Continued...
An Alternative to a Data Accessing Function
Produced: 20/03/2016 08:59:00
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.

Continued...
Passing a Table to a Stored Procedure
Produced: 13/03/2016 11:47:00
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.

Continued...
Datepart DW Giving Inconsistent Results
Produced: 31/01/2016 22:13:00
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.

Continued...
Shrink Log Without Breaking a Backup Chain
Produced: 24/01/2016 20:39:00
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.

Continued...
Database Data and Log File Sizes and Growth
Produced: 17/01/2016 20:51:00
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.

Continued...
Better Row Estimates with Table Variables
Produced: 06/12/2015 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)…

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

Continued...
Quick Case Statement Quiz
Produced: 29/11/2015 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.

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

Continued...
CTEs are Updateable
Produced: 22/11/2015 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.

To be honest it makes perfect logical sense that it works because a CTE is effectively just a nicely formatted subquery or derived table, but I’d never even thought to use it this way.

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...
Using CONCAT
Produced: 25/10/2015 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.

Continued...
Easily Calculating End of Month
Produced: 04/10/2015 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.

Luckily that’s now changed, but I found that a good few people didn’t seem to know that.

Continued...
Calculating Cumulative Totals
Produced: 20/09/2015 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.

Continued...
The Best Way to Obtain Percentages
Produced: 13/09/2015 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.

Continued...
Drop Failed For User
Produced: 06/09/2015 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:

“The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.”

Continued...
Quick Note About INSERT INTO vs SELECT INTO
Produced: 09/08/2015 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.

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...
Kill All User Processes
Produced: 12/07/2015 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.

Continued...
Access a Network Drive Using SQL Server
Produced: 14/06/2015 07:38:00
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.

Continued...
Grant Execute To All Procedures
Produced: 07/06/2015 07:34:00
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.

Continued...
Current State of Scheduled Jobs
Produced: 31/05/2015 07:22:00
This is another useful piece of code (well, I find it handy anyway) which I wrote to help populate a dashboard.

It’s very simple but, based on a quick internet search, fulfils a gap people seem to struggle with.

Continued...
Gradual Database Shrink
Produced: 24/05/2015 07:17:00
First things first… NEVER shrink your database unless you REALLY have to.

Now that’s out of the way, this is one of those occasions when I decided that a shrink was a bearable approach, however it wasn’t without issue and hence I found this funky piece of code.

Continued...
Vertical Partitioning Using sp_tableOption
Produced: 17/05/2015 09:48:00
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.

Continued...
Check SQL CPU Utilisation vs Other
Produced: 10/05/2015 09:46:00
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.

Effectively it shows not only the CPU being used by SQL Server but also the CPU consumed by other processes.

Continued...
Wait Stats Per Query
Produced: 12/04/2015 09:35:00
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?

Continued...
QuickTrace
Produced: 08/03/2015 18:46:00
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.

Continued...
How to obtain the size of a specific Index
Produced: 08/02/2015 09:31:00
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?

Continued...
Job History Timeout
Produced: 28/12/2014 09:37:00
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.

Well this is very similar except that there is so much history that all you see is “Timeout Expired”.

Continued...
Obtain Job Name from sp_who2
Produced: 21/12/2014 23:25:00
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.

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...
Is SQL Server Installed?
Produced: 02/11/2014 09:21:00
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.

Continued...
Merge Replication Commands in Pipeline
Produced: 21/09/2014 11:38:00
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?

Continued...
Obtain Start of Day from current_timestamp
Produced: 13/07/2014 17:07:00
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).

Continued...
Simple Cluster Failover Checker
Produced: 29/06/2014 16:40:00
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.

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...
Blocking Chain
Produced: 03/11/2013 16:04:00
There are numerous variants of sp_who2 kicking around the internet and most of them go to absurd levels of intricacy.

For my situation, all I wanted was something that would show you a blocking chain and would also translate the SQL Server Agent Job name into plain English. That was all.

Therefore I came up with the following.

Continued...
Date and Time Formats in SQL Server
Produced: 28/04/2013 09:27:00
There are numerous times you might want your dates returned in a different format and SQL Server will cater for this.

Running the following script will show the different date and time formats you have available…

Continued...