Learn T-SQL With Erik: Don’t Be Slack With Data Types

Learn T-SQL With Erik: Don’t Be Slack With Data Types

---

Chapters

• 00:00:00 – Introduction to Data Type Mismatch Issues
• 00:02:45 – Using Date Functions with Incorrect Data Types
• 00:06:27 – Plan Shape Catastrophe Example
• 00:10:38 – Martin Smith’s VARCAR50 Demo
• 00:11:29 – Conclusion and Next Steps

Full Transcript

Erik, big deal darling here with Darling Data and today’s video we are going to talk about how you should not be slack with data types and by that I mean you should always very carefully match your data types. It can be important both for performance and logical correctness when you write your queries to do this. And we are going to look at some examples around date time and date time 2 and stuff like that.

So with that out of the way, if you like this material, it is available as a whole video course and there is a link down in the video description where you can pick it up today at this very second. It is just available to you for $100 off down below. There are also other helpful links there if you would like to engage with me in other ways.

You can hire me for consulting. You can become a supporting member of the channel for $4 to $10 a month. It is a heck of a way to say, here is a little tip jar.

Say, thanks Erik for all the free stuff. You can ask me office hours questions. Keep that gravy train rolling. And of course, I always do appreciate as the channel grows. So if you would not mind doing some level of liking, subscribing, and telling a friend, I would be momentarily grateful for you.

Not eternally. Just… Just a couple of seconds.

Hey, look, the number went up. That is cool. Back to work. If you would like a free SQL Server performance monitoring tool, I have got one. I have been working on it for, oh, I guess, six, seven months now.

So things are maturing pretty nicely. Certainly competitive with all the paid T-SQL, SQL Server monitoring tools out there in the world. And of course, the price tag on mine is way better.

So, you know, if you are curious, you can go download it and start testing it out. And of course, if you run into any issues, have any questions, have ideas that you would like to see in the monitoring tool, just throw them up on GitHub. And my robot companions and I will respond just as quickly as we can.

They do not sleep, but I do. But anyway, let us continue our voyage through the allergic environment. Let us continue our voyage through the allergic heat death hell of June.

And I have got the ACs blaring, absolutely blaring. That is why I am not shiny. All right.

So I have created an index here on the creation date column in the comments table in the Stack Overflow database. And we are going to look at the difference in performance when we are slack with data types versus when we are not. So this is the current sort of method that you would use to flatten dates.

And notice that it, like, so I have to do a little bit of extra work here because I am just using, like, passed in string values. If you were using, like, if you are writing strings, you know, you have to, you should be careful about making sure that your strings are unambiguous and formatted in a way that the SQL Server does not have, there is no guesswork about them. Make sure that we are using the style.

Of convert that we need. 112 for dates. I think it is 121 for date time, date time 2 and stuff like that.

So make sure that you are doing these things because, or sorry, 112. That 112, that 121. There we go.

For that. So we want to make sure that we are doing these things because they help SQL Server make the best possible choices. And they help you from running into weird issues with ambiguous data. If you ever have to internationalize your audience, you will find very quickly that dates become a very murky subject.

And I am not just talking about time zones, but we will talk about time zones later. You have got that to look forward to. Woohoo.

High five. Time zones. Nothing better. Yeah. But using this method of date flattening and converting specifically to date times, we get a nice, tidy, easy seek into our index on the comments table. And all is fairly well with this query.

Now, like I said in the last video, date trunk returns a dynamic data type. So if we do not convert this from what is obviously a date time 2 based on the number of milliseconds that we have here, SQL Server will return it as a date time 2. And when we start comparing date time 2s to date time columns, the performance does get a little bit worse here.

This isn’t like the end of the world. But notice that this plan does look a little funny. All right.

It is a constant scan. We have got a compute scalar. And then we go into a nested loops join this many times to go find the rows that we care about. This is because we are being slack with our data types. We lose that nice, tidy seek plan and we get this plan with all this extra stuff to it.

We are essentially creating a row set and joining that over and over again. That is not fun. That is not the kind of execution plan you want to see.

But if we are taking a look at the data types, we get a nice, tidy, easy plan. We are taking advantage of SQL Server 2022. Like brand-new SQL Server 2025. I am actually using 2025 at this point.

I think it is finally enough cumulative updates in where I feel pretty safe running demos and everything on it. But if we run this, what we are going to hit is, of course, or rather if we run this, we will see we will go back to our nice, tidy seek plan because we are converting to a date time up here. Duh.

Date time. Good for us. And we can at least get back to the seek plan that we wanted before. So that is exactly what we want to see. Similar caution does need to be shown when assembling a date time or date time to from parts.

You have a variety of functions at your disposal to assemble a date from parts. You have date from parts, date time from parts, and date time to from parts. And if you are just throwing some strings into those, things can get rather perilous for your queries.

Just a couple of examples here. If I run these, this is the first one that is using date from parts. And we are back to this sort of weird plan with the constant scan compute scaleR and the nested loops join over to here.

I mean, it takes like 300 milliseconds, which again, this is not the end of the world. This is not supposed to show you a drastic performance change. But it is there to show you the plan shape and what you want to look out for in your queries when you want to get things right.

Notice down here, when we use date time from parts, we are back to our simple seek plan. We get a parallel plan from this, which is good given the number of rows that we are hitting. You can sometimes get parallelism with these, but the optimizer support for it is not so great.

But one thing that I want to show you is this plan, which is a real catastrophe. Right? We are going to, what I want to show you is what SQL Server is kind of doing when you mismatch data types badly, especially dates.

Right? So this is sort of the plan shape that I warned you about before, where you’ve got constant scan, compute scaleR, merge interval, and then a nested loops join to go find stuff. Right?

And this is because down in here, we created our table with a date time data type for the column. Right? We converted that column to a date. And then we asked where it was between a date and a date time 2, 7.

So SQL Server does have all sorts of stuff to do. If you open up the plan XML, this stuff doesn’t show up. This stuff doesn’t show up if you just look at the query plan.

But you’ll see stuff like this in query plans where SQL Server has to do extra work, get range through convert, get range with mismatched types. These are optimizer rules that SQL Server has built in. To try and help you or try to help queries that use mismatched data types do the right thing.

You can see where SQL Server is converting stuff and all that. So it’s extra effort for the optimizer to have to deal with your queries. This is a very interesting problem that my friend Martin Smith ran into with strings.

If you go to this link, you’ll be able to see the issue that Martin opened up here. Martin Smith, very smart fella. Incredible with SQL Server stuff.

One of my absolute heroes. And what he found was a very interesting problem where we have a VARCAR50 column collated like so. It is nullable.

And then what we would do is insert 20 null values into them. Get a count from the table. And then we would select another count from the table. And we would say where problem child equals this or problem child is null.

Now, this one is actually a little bit perilous because I verified this on SQL 22. But I said, I just started using SQL Server 2025. If it doesn’t repro here, good job, Microsoft.

If it does, you stink. Just kidding. You’re busy. You got a lot of fabric weaving to do. So let’s see if this thing still repros on SQL Server 2025. Let’s see.

It does. So we get so when we do a regular count from the table, we return the 20 rows, right? Because, I mean, just like you can see, I am very much limiting this to 20 rows going in, right?

Top 20. Generate series 1 through 20. When we do a count from the table, SQL Server correctly says there are 20 rows in there. But when we say where problem child equals, we have this Unicode string in there, right?

So we have an implicit conversion. Or it’s null. SQL Server counts 40 rows instead of 20 rows. Very interesting stuff.

And, of course, the execution plan looks just like some of the other execution. Well, I guess there’s an extra operator in there where it looks a lot like a lot of the other query plans I’ve shown you with this sort of weird constant scan concatenation top end merge interval situation over here. And then we have…

I should have highlighted the select so it stayed where it was supposed to be. And then over here we have our dynamic seek where SQL Server is doing an implicit conversion on our VARCHAR column. And it’s saying, is it this or is it null, right?

And, of course, we get back twice as many rows as actually exist in the table. So thank you, Martin Smith, for that wonderful demo. High five from wherever you are in the world.

I believe, according to his Stack Exchange profile, he lives in Rugby, England. Hopefully that’s not Dachshund. I never want to give away too much information. But thank you, Martin.

You are a wonderful, smart, brilliant human being. And I don’t know. I wish we got to spend more time together. You may probably don’t feel the same way. I understand. But I think you’re great.

Anyway, that’s enough for today. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you next week on Tuesday for Office Hours. All right.

Thank you.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.