Welcome to our SQL Server blog
This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.
Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.
Splitting a Table Full of Strings into ColumnsThis was a fun afternoon I spent getting this working nicely. I don’t know if there are better ways of doing this, but if there are then the internet isn’t the place to look because if you type in string split you’ll get a whole bunch of pages about splitting a string… but just an individual string.
What I wanted was to be able to take a table full of delimited strings and make them into columns so that I could insert them into a table. So here’s what happened…
Read More >>
Window Functions - Row Mode PerformanceMy last post was on the wonders of Window Functions in Batch mode (which is awesome – if you’ve not read the post then go… now…). This post will focus on any of us who don’t happen to have the latest and greatest (at time of writing) and aren’t able to force batch mode through our queries.
So, what’s the big deal? Window Functions are just Window Functions? Yes?
Read More >>
Window Functions in Batch ModeWe all love Window Functions. Where would we be without ROW_NUMBER and RANK? But these can sometimes be performance killers as they loop through our data performing sorts and scans as they go.
Now, in SQL Server 2016+, there is a Batch Mode version of Window Functions and the improvement is huge.
So let’s have a look with a couple of quick examples:
Read More >>
This is a new feature added into SQL Server 2017 to try and alleviate the pain of an age old problem… parameter sniffing.
For anyone who doesn’t know what this is, I have outlined the problem with a couple of examples in the previous post, therefore have a quick read through… but if you’re already aware of the problem then read on…
First things first, there’s a HUGE caveat I need to mention here… in SQL Server 2017 an Adaptive Query Join will ONLY occur if the query is in BATCH mode. This means that a Columnstore MUST be present somewhere in the query.
Read More >>
Parameter Sniffing in SQL ServerThis is something I was sure I had written about before, but it turns out I haven’t. I’m also aware that there’s a lot of information out there on the topic, but I wanted to post about Adaptive Query Joins and this is a significant precursor, which is why I’m putting this blog out first.
So… parameter sniffing… what is it?
Read More >>
Temporal Tables – Editing Historical DataWe all know that Temporal Tables don’t allow us to edit data in the Historical table. This is for all manner of incredibly sensible reasons (auditing etc) and therefore shouldn’t be breached.
However, there are also times when we very much need to correct some data historically and need it reflected accurately in the Historical table of our Temporal setup. Luckily this can be done.
Read More >>
Temporal Tables – Notes and GotchasHere we’ll go through a few of the quirks of Temporal Tables, both things that I’ve been asked most frequently, and also some of the known considerations when looking to use these in your environments.
Most of these are minor and won’t cause you any issues, but there may be the odd one which means you can’t use them at all in your setup. Either way they’re good to know.
Read More >>
Querying Temporal Tables in SQL ServerAgain, following on from my last post, we’ll be looking at Temporal Tables a little more. Last time we simply created one and entered a row of data whereas this time we’ll be looking to query them a little more and see what syntax is required to do so.
This, again, isn’t too complicated when you know the quirks and the syntax isn’t hard once you’ve used it a couple of times.
Read More >>
Creating a Temporal Table in SQL ServerContinuing from the previous post, which was a brief introduction to Temporal Tables, we’ll now move on to creating one and seeing what special syntax is involved in that process.
To be honest this tends to look a little confusing when you first see it, but it very quickly makes sense and you can start using it right away.
Read More >>