Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Splitting a Table Full of Strings into Columns
This 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…

Window Functions - Row Mode Performance
My 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?

Window Functions in Batch Mode
We 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:

Adaptive Query Joins
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.

Parameter Sniffing in SQL Server
This 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?

Temporal Tables – Editing Historical Data
We 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.

Temporal Tables – Notes and Gotchas
Here 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.

Querying Temporal Tables in SQL Server
Again, 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.

Creating a Temporal Table in SQL Server
Continuing 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.

1 of 30

SQL  World  CEO
Kevin  Urquhart

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.



© Copyright 2020 SQLTraining Ltd.