SQLServerFast

Plansplaining

Plansplaining part 23 / T-SQL Tuesday 168 – Window functions

Hugo Kornelis
8 Comments
I skipped the last two T-SQL Tuesdays. But this month, I will participate again. The monthly blog party is once more hosted by Steve Jones. His chosen topic is: Window Functions. In his invitation, Steve specifically asks for examples where Window functions provided a neat solution to a real world problem. Well, sorry Steve, but I am not going to do that. But your invitation did inspire to me write about the execution plans for these window functions. And there is, in fact, so much to write about it, that this is just the first part. So that makes this…

Plansplaining, part 22. SQL Graph (part 3)

Hugo Kornelis
No Comments
Welcome to part twenty-two of the plansplaining series, where we will continue our look at execution plans for graph queries. We started this series by investigating the hidden columns in the internal structure of graph tables. In the second part, we looked at how those hidden columns are used in the execution plan for a relatively basic graph query. Time to up our game and tackle the SHORTEST_PATH function, introduced in SQL Server 2019, that can be used to make SQL Server search a graph iteratively or recursively to find the shortest possible path from one node to another. Sample…

Plansplaining, part 21. SQL Graph (part 2)

Hugo Kornelis
2 Comments
Welcome to part twenty-one of the plansplaining series, where we will continue our look at execution plans for graph queries. In the previous post, we looked at the internal structure of node and edge tables, and discovered that they have a few hidden columns. Now let’s look how those columns are used in graph queries. Sample data As mentioned in my previous post, I’m using sample tables and data from Microsoft’s documentation for these posts. You can find the script to create and populate the tables here, or in my first post on SQL Graph. Simple query Let’s start with…

Plansplaining, part 20. SQL Graph (part 1)

Hugo Kornelis
2 Comments
Welcome to part twenty of the plansplaining series. It has been a long time since I last wrote a plansplaining post, partly because of my health, but also for a large part because I was out of ideas. But recently I decided to dig a bit deeper into a feature that was released in SQL Server 2017 and that I had so far not played with: SQL Graph. SQL Graph is the name for a set of features, introduced in SQL Server 2017 and extended in SQL Server 2019, that bring graph database functionality into SQL Server. See here for…

Plansplaining, part 19. Temporal tables (part 4)

Hugo Kornelis
2 Comments
Welcome to part nineteen of the plansplaining series, where I will finally wrap up the discussion on temporal tables. In the first three parts, we looked at execution plans for modifying data in a temporal table, and for basic and advanced temporal queries. In this last part, we’ll shift away for execution plans (so the plansplaining tag is perhaps a bit misleading) and look at what happens if you join data from multiple temporal tables. Sample data I once more use the same tables as in all previous post, but this time I want a bit more control over the…

Plansplaining, part 18. Temporal tables (part 3)

Hugo Kornelis
1 Comment
Welcome to part eighteen of the plansplaining series. Like the previous posts, this one too focuses on temporal tables and their effect on the execution plan. After looking at data modifications in temporal tables and at querying with a most basic temporal form of temporal query, let’s look at the more advanced variations for temporal querying. We’re still looking at getting data from a single query only in this post. We’ll look at joins in the next post. Sample tables For the code samples in this post, I keep building on the demo tables and sample data from the previous…

Plansplaining, part 17. Temporal tables (part 2)

Hugo Kornelis
4 Comments
Welcome to part seventeen of the plansplaining series, where I will continue my look at how temporal tables affect execution plans. The previous post focused on data modifications in temporal tables. Now let’s see what happens if we use temporal logic to our basic retrieval queries. This post focuses on the basics only; do keep in mind that as soon as you want to join data from multiple temporal queries you will run into some specific issues that you need to be aware of; this will be the subject for a later post. Sample tables For the code samples in…

Plansplaining, part 16. Temporal tables (part 1)

Hugo Kornelis
5 Comments
Welcome to part sixteen of the plansplaining series. The first of a few posts about how temporal tables affect execution plans. In this post, I’ll build on the last four posts on data modifications, building on the more generic discussion of data modification in the previous four posts. Later posts will look at data retrieval and some specific scenarios. If you have never heard of temporal tables, I suggest reading Louis Davidson’s five part series about this feature. It seems some of the formatting has been lost in time, but the content is still good and valid. Part 1 Part…

Plansplaining, part 15. Foreign keys, part 4

Hugo Kornelis
4 Comments
Welcome to part fifteen of the plansplaining series. In the three previous parts I looked at the operators and properties in an execution plan that check a modification doesn’t violate foreign key constraints. That part is done. But I’m not done with foreign keys yet. We normally expect foreign keys to throw an error on violations. But that’s actually only the default option: they can also be set to be self-correcting. This is done using the ON UPDATE and ON DELETE clauses, which provide the user with several choices on how to handle child data that would become orphaned, and…

Plansplaining, part 14. Foreign keys, part 3

Hugo Kornelis
3 Comments
Welcome to part fourteen of the plansplaining series, where I wrap up the mini-series on how simple foreign keys have huge effects on execution plans for data modifications. We already looked at inserting data in the referencing (child) table, and at deleting data from the referenced (parent) table as well as updates in the child table. We did not and will not look at deleting from the child table or inserting in the parent table: those operations can by default never violate the foreign key constraint, so no additional logic is needed. So that means there is only one thing…

Hugo Kornelis

Married, father of 2 children.

Awarded 13x MVP (2006-2016; 2019-now), in the areas SQL Server and Data Platform.

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Archives

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close