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?

Read More >>
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:

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

Read More >>
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?

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

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

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

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

Read More >>
What Are Temporal Tables in SQL Server?
Temporal Tables (also known as system-versioned temporal tables) are essentially tables which return data based on a specific point in time.

These are ideal for use with slowly changing dimensions in data warehouses, or simply if you wish to easily and simply keep a queryable history of your data for audit or reporting purposes.

As this is a new (well, since SQL 2016) technology, there is a new syntax to remember. Luckily it’s not that complicated as it’s been built into the standard CREATE TABLE statement.

Read More >>
1 of 30

Your SQL Trainer
Kevin Urquhart

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

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron