SQLServerFast

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

T-SQL Tuesday #136 – Float does NOT suck!

Hugo Kornelis
5 Comments
Another month has passed; time for another T-SQL Tuesday post. The monthly blog party is hosted by Brent Ozar (b|t) this time. And he wants us to write about our favourite (or least favourite) data type. I don’t really have a data type I like. I also don’t hate data types. The thing I do hate is when the wrong data type is used for the purpose. So instead of doing what Brent asks, I will write a short post about a very common misconception about a data type that triggers me whenever I read it. Which happens a lot.…
Read More

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

T-SQL Tuesday #135 – My tools to stay alive

Hugo Kornelis
1 Comment
It’s February, it’s Tuesday, and we all know what that means. Time for another T-SQL Tuesday post. Our host today is Mikey Bronowski (b|t), who asks us to write about tools we use to make our jobs easier. This looked familiar. And when I checked, I found out that I did, indeed, talk about tools before. That post was specifically about tools for work. I could of course now write about new tools I discovered, or tools for other areas in my work such as presenting or recording videos. But since Mikey doesn’t restrict it to work-related tools only, I…
Read More

T-SQL Tuesday #134 – Give me a break!

Hugo Kornelis
1 Comment
The January 2021 edition of T-SQL Tuesday is hosted by James McGillivray (b|t). His question is: if you’ve been working too hard, if you’ve been spending too much time in front of a screen, what do you do to wind down? The answer to that is, for me, three-pronged. Tier 1: Do other work I work on a very varied set of activities, and I have a lot of freedom on how I choose to distribute my time: paid customer work, sometimes for multiple customers. Preparing, rehearsing, and delivering live presentations. Research and writing for the Execution Plan Reference. Authoring…
Read More

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

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

Plansplaining, part 13. Foreign keys, part 2

Hugo Kornelis
2 Comments
Welcome to part thirteen of the plansplaining series. In this part I continue the exploration of how execution plans check foreign keys on data modification. In the previous part, we looked at foreign key checking when data is inserted. Inserts in the referencing (child) table can, by definition, not cause foreign key violations, so no checking is done in those cases. But for inserts in the referenced (parent) table, SQL Server needs to check that the inserted value does indeed exist. We saw how both the Nested Loops and the Merge Join operators have some specific optimizations to do that…
Read More

Plansplaining, part 12. Foreign keys, part 1

Hugo Kornelis
5 Comments
Welcome to part twelve of the plansplaining series, where I’ll discuss foreign keys in the context of execution plans. And just to be clear, this is not about how foreign keys and other constraints can be used to simplify execution plans and speed up query executions. That is documented in so many places that I trust you already know how important those constraints are. No, today we’ll look at the extra work that SQL Server needs to do to ensure that your changes don’t violate any of your foreign key constraints. Sample tables I will use a very simple scenario…
Read More

PASS virtual summit: thoughts, pre-con, and sessions

Hugo Kornelis
No Comments
As you probably already know by now, the 2020 edition of the PASS Summit will be held as a virtual event. Drawbacks That has drawbacks, of course. I’ve been to many PASS Summits in the past, and it’s always a great experience to be among so many fellow data professionals. To be surrounded by thousands of people that I know to have the same passion for data as I do, to face the same struggles in their daily work that I do, and to share my eagerness to absorb more information. The interactions in the hallway, the casual conversations during…
Read More

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