Blog | Page 2 of 193 | Darling Data

Get AI-Ready With Erik: Helping Vectors Rank Better With Keyword Boosts

Posted on February 25, 2026February 23, 2026 by Erik Darling

Get AI-Ready With Erik: Helping Vectors Rank Better With Keyword Boosts

Video Summary

In this video, I delve into strategies to enhance vector search relevance in semantic search engines like Vector, focusing on SQL Server scenarios. I explore how keyword searches and full-text indexes can complement each other, discussing their strengths and limitations. By demonstrating practical techniques such as adding specific keywords to the query conditions and using boosts based on domain knowledge, I show how to refine search results to prioritize conceptual relevance over popularity or frequency. This approach is particularly useful when you need exact terms but also want the search engine to understand the context better, ensuring that highly relevant content isn’t missed due to semantic gaps.

Full Transcript

Erik Darling here with Darling Data. You know, trying to get my AI ready on. I’m gonna make sure that I’m prepared for the world falling apart around us and all that other good stuff. So today’s video is going to be about some things you can do that might help vectors rank things a bit better, right? Because, you know, like we talked about in the last video, vector might miss some stuff, right? It’s not, didn’t, we were looking for a SQL Server 2008 R2 when we got back a lot of SQL Server 2008. And then we were like, I want to find stuff about connection strings, but not any framework. And Vector was like, hey, any framework, you said those words, here you go. Uh, I got, I hear that right? Yeah. Ah, well, there you go. It’s some entity framework for you. Um, so semantic search is there to find conceptually related content, but it has a, it has a bad habit of missing exact terms. Uh, keyword search, like, you know, we should, we, I show you, I had to unfortunately show you some full text index stuff, which I feel dirty about still, but I’ll get over it when you buy something. Uh, keyword search finds exact terms, but may not rank by conceptual relevance, uh, as well. Right. Cause like, you know, we can, we can put in explicit words and we can find explicit words.

Which is great for stuff like, you know, error messages or like, you know, product versions and things like that. But you know, less so for like, ah, I think I want a sandwich. What kind of sandwich do I want? I don’t know. Who has sandwiches, right? Best sandwich. Right. So like, you know, stuff that, you know, you might, might have a harder time. Anyway. Um, so this is, what I’m going to show you is useful when a specific term must appear in results, but you want results ordered by conceptualization. Conceptual relevance, maybe rather than like popularity or something. Right. Cause you know, in the, in the stack overflow database, we have a score column, right? And like, you know, we could certainly like, you know, like, like factor score into things, which we will talk about.

But, uh, typically we, we, you know, if we’re, we’re spending all this time and effort and energy into getting our vectors, right. We, we, we would probably want to, uh, you know, involve them at least to some degree. Right. What’s the point of all this damn data. Don’t use it. Uh, so what we’re going to do is try to find stuff about SQL injection prevention, but we’re going to, we’re going to like give a secret. We’re going to give our, our search a little bit of help, right? Because we want to make sure that the title of the post is at least, you know, has SQL injection in it. Right.

So this is another form of keyword search kind of, um, it’s just an alternate to full text indexes. You can, you can throw some stuff like this in, I guess, in the where clause to help filter stuff out. There’s also another, um, uh, there’s also another, uh, technique that I’m going to show you down below. And this will at least get us to the point where, you know, all of the post titles have SQL injection in them. And then it’s sort of up to us to figure out like, okay, well, I mean like, you know, distance wise, right? Like, like all of these things do pretty okay.

But the scores are all over the place. We’ve got a zero, a 10, a 2700, right? But like, like, we’re not seeing, we’re not seeing stuff with like, uh, and working at questions too. Right. So like, like two things that we’re not doing here that like if in real life we would probably want to be doing is one, making sure that these questions have answers. Right. Cause like the question doesn’t have any answers. How do you like, it might just be a similar question if you’re doing like, you know, like content deduplication or whatever.

But if you want to like find related content that might have helpful things in it, aside from that, we would probably want to make sure that they had some answers. We also want, might want to factor a score into it somehow because score at least, you know, would help dictate like, Hey, this is a good question. Like, or at least this is a highly upvoted question. Good or not is, uh, come on. It’s stack overflow. Right.

Like something, something’s got really hot. A lot of upvotes, like more upvotes than I will ever get in my life. But, uh, you look at it and you’re like, you link to the document. You rat. Anyway, another, another option we have, right. Another way that we could potentially, uh, make that, make this all better somehow would be to add a boost based on the presence of certain words in, uh, in another title. Right. So, uh, if we, we, we, we, we, the way that we would do that is we could, I mean, we’re like, this is just to show you what the boost actually turns out being.

So if we’re like, if we’re off looking for like how to optimize database performance, you know, conceptually, we’re going to find stuff that’s pretty close to that. But like, we, we might, you know, kind of like, you know, know our data or like, you know, have some idea about like, like allow people to enter in some like, uh, like, uh, some like, uh, what do you call them? Uh, like backup search words or something. We’re like, Oh, like, yeah, I’m really interested in indexes. Right. Yeah. That’s a good one.

So we could add in some, some like booster stuff like that. And then we could adjust, uh, what the vector distance is based on that boost. And what we’ll end up with is a query that looks a bit like this, man. I’m actually going to highlight the whole thing. Cause the last thing I want in one of these videos is like an error. And then you’re like, uh, then I have to like go vector search the error message and figure to ask AI how to fix it.

And AI is like, Oh, well, it looks like maybe you forgot to highlight where you declared a variable. And then you want to be like, man, AI, you’re so good. You’re so smart. How’d you? God. Anyway, uh, what we have here is a few columns. Oh, we’ve got more than a few, but we have a few columns that we would care about, uh, the contents of for this exercise.

So we have the normal vector distance thing in here, right? And you know, these numbers are all just wonderful, right? They are all floating point numbers, I know, decimals. Nailed it. Uh, but then now then we have our boost column and all of these are, I mean, we don’t, we don’t have any, we didn’t hit on index or like maybe index got screened out past the top 15. Like maybe index didn’t help like the index one didn’t help. Cause like what we did up here, uh, was say if, you know, um, uh, minus case when title, like indexes, remember we have to, we want, we want to get these numbers lower.

Right. So we’re subtracting. This is not boosting by adding onto the vector distance. Cause that would make it further apart or make, make the things seem further apart in the numbers. Uh, we’re doing this by, um, subtracting the, like add relevance to certain keywords. So if the title is like index or the title is like performance or the title is like slow, then we would like have these multipliers, uh, get subtracted so that we could see more stuff.

So, um, like everything that we found at least because we, we matched conceptually, but the vector search very close to performance, everything was either a three or zero, right? So like, like a lot of these are three, three, three, and then a lot of them are zero, zero, uh, zero, zero and stuff. But you can see how the, the semantic distance got adjusted by the, uh, by the boost, right?

So like the one that started at 1077 got adjusted down to triple sevens, right? Uh, the one that started at 845 got boosted by nothing and stayed at 845. Uh, and then the one at 1358 got boosted by 0.03 and got boosted and it got adjusted to 1058.

So this is just another way of like helping like the vector distance stuff, like get some more, um, like contextual, like, you know, words in, in play and like adjust the score a little bit based on like certain things that are very closely rated. Like you might know about like domain knowledge stuff. Uh, but you know, the, the vector similarity search is just like, I, I, I got these floating points over here and I got these floating points over here and I got to figure out how close these floating points are.

It’s not thinking about like, you know, additional, like, like, Oh, well, if I had this other floating point, if I thought about this other floating point, I, I could, I could really, I could really get some better searches in here or some better search results in here. So this is just another way of doing that anyway. It’s a lot of stuff to think about with this, isn’t there?

Well, maybe someday, right? I don’t, I don’t know if anyone’s on SQL Server 2025. Yeah, they did.

They did just release a cumulative update one with a shockingly low number of, uh, fixes in it. Unfortunately, there were also no, uh, no, it was not graduation day for any of our preview features. So we’re still, still stuck there.

Anyway, that’s probably good for this one. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I hope that you will use this, this wonderful, fabulous coupon code, uh, up, up, up here to buy the entire course. And, uh, I don’t know, maybe pay rent or a electric bill or phone bill or something.

I got all sorts of stuff I gotta do. All right. All right.

Anyway, thank you for watching. I’ll see you tomorrow.

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.

Get AI-Ready With Erik: Full Text Keyword Search In SQL Server

Posted on February 24, 2026February 23, 2026 by Erik Darling

Get AI-Ready With Erik: Full Text Keyword Search In SQL Server

Video Summary

In this video, I dive into the world of vector search in SQL Server 2025 and its limitations. Starting off with a discussion on why vector search might not be ideal for precise queries like searching for specific versions of SQL Server (e.g., 2008 R2), I highlight how these searches can return irrelevant results due to the nature of vector matching. Moving forward, I explore other challenges such as understanding negations and how certain terms are interpreted by vector search algorithms. To address some of these issues, I propose using full text search—a tool that better understands logical operators like “not”—as a complementary method for enhancing vector search results. While full text search isn’t without its drawbacks, it offers a practical solution to refine searches in SQL Server 2025, making the overall process more robust and accurate.

Full Transcript

Erik Darling here with Darling Data, and we are going to get back into our SQL Server 2025 AI Preparedness Tour. This is, of course, in support of my new album, Get AI Ready with Erik. If you are interested in purchasing this album, not perching on this album, that would be uncomfortable for everyone. You can do that at the link up there. The blue thing, it starts with HTTPS. I realize there’s some other blue things down there, but there’s definitely only just one blue thing way at the top. That link is also down in the video description where you can very conveniently click on it and change your life. So, we’ve talked about a bunch of vector-y stuff, so we’re going to continue on with that. And the thing that we have to, you know, first go through, and I mean, I’m going to say something unpopular at the end of this. I know you might be shocked by that, but I’m going to say something unpopular at the end of this one. But up front, vector search is not good for some things. It lacks precision, one might say. So, if, you know, let’s say you care desperately about SQL Server 2008 R2 for some reason, I don’t know, it’s just, it makes for a good example.

But, and you vector search for it. R2 isn’t like a big, significant, vector-y thing, right? Like, the vector sounds like, oh, R2, D2, right? So, it treats 2008 and 2008 R2, because like SQL Server 2008, that is like, kabow! But R2 is like, not so much. So, let’s run this query against our database. And we didn’t, like, normally, the first thing I do before I run a query is make sure execution plans are turned on, but that doesn’t matter here. So, that’s good for us, right? We don’t need to worry so much about that. We can just run our query and not care about the execution plan, at least not yet anyway.

But if we look at the results here, right? And using my lovely SQL Server SSMS 22 result zooming, look what we get back here. Like, sure, the top result is SQL Server 2008 R2, but with a distance of zero, I’m pretty sure that was the post title that we searched for, right? Because we didn’t filter out the exact search. Well, I don’t know. Actually, no. Yeah, no, actually, that won’t matter here. Good.

Because sometimes with these, you know, you have to, like, if it came from an original post, you should screen out the original post because you don’t want to match your, like, the original post to, like, the, like, candidate posts. But that doesn’t matter for this one. So, we got a bunch of, but if you look at this column, there are a lot of no’s in here, right? There’s, like, five in a row and then a sixth one. What is that? Six in a row? One, two, three, four. It looks like five, six, and then seven, eight, nine, ten.

Yeah. So, yeah, like, six out of the ten results that we got back were not 2008 R2. They were just SQL Server 2008. And that does not bode terribly. That may feel like it doesn’t bode terribly well for vector search. But the thing you need to understand is that, like, vector search is not meant to be, like, the end-all, be-all replacement search for everything else.

There are certain things that you might have to do to bolster searches like this. And we’ll talk about an unpopular option for that at the end here. Another thing that vector searches do not have a very good understanding of is the word not.

Which, I don’t know, if you’ve ever talked to an LLM, like, I don’t know, those things seem to do okay with stuff here. Like, I am not interested in buying a Lamborghini. I am interested in buying a different kind of sports car.

Like, I don’t know, I’m pretty sure they would figure out. But, you know, they’re probably using a whole different set of, I don’t know, maybe smarter, better things inside of them. They can understand not, right?

But if we were to search something like this, right, and we were to say connection string not related to, like, not entity framework. I want a different connection string. I want, like, a Python connection string or something, right?

You should probably say that. But this, again, just makes it for a pretty good example. Where, if we run this query and we look at what we get back, well, we get problems with entity. I mean, this one says EF4.

So, I mean, it doesn’t exactly match to identity. But it’s still that. This one doesn’t have entity in it. But, you know, I don’t know that that’s going to help. These ones are all entities.

And, you know, I mean, this one is still ADO.net. Maybe that’s close enough to entity framework. But at least in my mind, they’re basically the same thing, which is untrue. But, you know, in my mind, I’m just like, you do that stuff?

Cool. I don’t, I don’t, I don’t. You need database help. You should hire me for that. But you can see that a lot of stuff in here does come back as entity framework related. And this is probably another good example of computers maybe not understanding strings too well.

Right? Because this one is EF4. And we were just searching for entity. But I’ll be damned if I’m going to list out all the EFs in this query to search for. So, you know, it’s close enough.

I can still see stuff got weird in there. So the unpopular thing and the thing that you might need if you are going to do vector search in SQL Server is one of my least favorite things in SQL Server.

Both from a, you know, from a performance perspective, certainly. But at least, you know, like, like I used to do a lot of work on a database product called Relativity. It’s a e-discovery product.

I used to be a DBA at a shop that had them. And for a long time, like they’re like early, much earlier. I’m sure it’s much better now. Like way earlier on, they were using full text search for like document search stuff. Because it’s all like lawyers searching through cases, like trying to sue people and find documents and stuff like that.

So there’s a lot of full text search in that. And my, like, my head goes to terrible places when I think about full text search. Because there was like terabytes and terabytes of data in those things.

But, you know, full text search, for what it’s worth, does understand not. Because you can say not contains entity. And you can say contains connection string when you want to search for stuff.

And boy, howdy, does entity framework not show up in this one, right? So like at least, at least it understands not contains, right? We can say not contains and we can skip the entity framework stuff, right?

Or at least entity, right? Or whatever we did. Entity. Yeah, entity. So we are able to get rid of entity using full text search. But there is, of course, a lot of, you know, sort of backroom pain involved with full text search.

Despite its, I guess, advanced understanding of not. So, you know, you could choose to go this path, you know, which kind of sucks, right? It’s maybe not the greatest thing in the world.

However, it could be used to bolster vector search in some places. And, you know, I guess your alternative, if you’re like, you know, full text search, full text that, you could always go back to the bad old days of, you know, double wildcard text searching and say, and title not like percent entity percent. With, of course, the correct unicode thing in front of the string if it is unicode.

Because one should always mind their data types. All right. Well, that hurt to say.

Anyway, that’s about good here. If you’re looking at the top there, you see that I have four more SSMS tabs open. So we have four more tabs of things to talk about. Well, three more tabs of things to talk about at least this week.

And then we will start afresh next week with office hours, getting AI ready and all sorts of other things. All right. Anyway, that’s probably about good here.

Thank you so much for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will be even more ready for AI in SQL Server 2025. Yo desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk desk

Video Summary

Full Transcript

Right? Because this one is EF4. And we were just searching for entity. But I’ll be damned if I’m going to list out all the EFs in this query to search for. So, you know, it’s close enough.

I can still see stuff got weird in there. So the unpopular thing and the thing that you might need if you are going to do vector search in SQL Server is one of my least favorite things in SQL Server.

Both from a, you know, from a performance perspective, certainly. But at least, you know, like, like I used to do a lot of work on a database product called Relativity. It’s a e-discovery product.

But, you know, full text search, for what it’s worth, does understand not. Because you can say not contains entity. And you can say contains connection string when you want to search for stuff.

Despite its, I guess, advanced understanding of not. So, you know, you could choose to go this path, you know, which kind of sucks, right? It’s maybe not the greatest thing in the world.

Because one should always mind their data types. All right. Well, that hurt to say.

And then we will start afresh next week with office hours, getting AI ready and all sorts of other things. All right. Anyway, that’s probably about good here.

Going Further

SQL Server Performance Office Hours Episode 49

Posted on February 23, 2026February 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 49

Query Store shows this plan has the lowest average duration, so it must be the best plan. Should we force it?

One of my queries uses an eager index spool operator that’s costing 45% of the query. I understand it’s building a temporary index, but I can’t figure out what’s triggering it – there’s no Halloween protection needed and no self-joins. The query runs fine if I break it into temp tables. Is there a way to see what the optimizer is trying to protect against, or how to prevent the spool without rewriting?

Let’s say your friend was hired as the sql developer/DBA for a very mature application. This application uses a model where each client has their own database, but each database has the same schema. Most of these databases are hosted in Azure SQLDB. However, many top-paying clients host their own server/on-prem sql and refuse to upgrade past 2014/2016. Your friend notices a lot of transformations done in procedures are way quicker using some of the features/functions in newer versions. Would your friend be an idiot for creating two separate variables, one with the sql statement for the old versions, and one for the new, then executing the appropriate one based on the SERVERPROPERTY(‘Edition’) value? How would you handle this? Also, will you be my friend? Thanks, from [hopefully] your new friend.

Postgres and Oracle have skip scans. Why Doesn’t SQL Server?

I am interested in your AI course but my company probably won’t be on SQL Server 2025 for a while. How relevant will this content be in a year or two? The AI world seems to change rapidly.

To ask your questions, head over here.

Video Summary

In this video, I dive into some interesting questions submitted by viewers during my Office Hours session. I address topics ranging from query store optimization to handling different database environments and SQL Server versions. Whether you’re curious about forcing a plan that shows as having the lowest average duration or dealing with eager index pool operators in your queries, there’s something for everyone. Additionally, I explore strategies for managing code deployments across multiple server editions, suggesting approaches like using environment variables or parent-child store procedures to handle different SQL Server versions without complicating your existing codebase too much. The video also touches on the absence of skip scans in SQL Server compared to other database systems and why this feature might not be as critical due to how SQL Server optimizes query plans. As always, I encourage viewers to engage by submitting their own questions or becoming a supporting member to help keep the content relevant and useful for all SQL enthusiasts out there!

Full Transcript

Erik Darling here with Darling Data, and we are in the midst of a Monday. But I suppose that’s good news for you, because that means you get to experience yet another Office Hours. I think we are coming real close to closing in on episode 50, which I will do absolutely nothing to commemorate.

But anyway, let’s talk through our stuff. I will answer five user-submitted questions to my Office Hours link. Now, if you look down in the video description, you will find all sorts of helpful links.

And not to spend too long here, but I recently spent some time redoing my consulting offerings, so there are some new ones in there for folks who might need different sorts of engagements than the typical blocks of hours that I was selling in the past.

Those are still available, but I do have some new stuff if you want to check that out. I’ve also added a lot of training and redone some of the training offerings in that you can now buy them as subscription products at the monthly or yearly rate. So if you are on the fence about spending some chunk of money all at once, you can now spend a smaller chunk of money, either on a monthly or yearly basis.

You can also use the links below to become a supporting member of the channel. If you just like all the stuff that I talk about, but you want to spend like, I don’t know, four bucks a month just to buy me half a New York espresso here, you can do that. There’s also a very helpful link down the bottom if you want to ask me office hours questions for one of these things.

So if you have a SQL Server question and you’re like, boy, it would be cool if Erik Darling answered that on YouTube, you can submit one and have that happen. And as always, please do the old like, subscribe, tell a friend, all that stuff. This training page is going to get fuller and fuller, at least until things start happening.

I will be at DataTune in Nashville. So all of the, I’ll just get out of the way. I have pre-cons at all of these.

I will be teaching my advanced T-SQL material at all of these. That’s DataTune, Nashville, March 6th and 7th. Data Saturday, Chicago, March 13th and 14th.

SQL Day, Poland, May 11th and 13th. And Data Saturday, Croatia, March 6th and 7th. I should probably fix the alignment on those a little bit, but you know what? I kind of like them staggered some.

I think it gives it a dynamic vibe. It’s like, whew. It’s just misaligned. That’s my fault. Anyway, we’re trudging through January here in fabulous New York City. So we’ll get there someday.

Drunken marshmallows. Data marshmallows. Anyway, let’s answer some of these questions. Here we go.

All right. Query store shows this plan has the lowest average duration. So it must be the best plan. Should we force it? Well, you know, that’s an interesting question because I would, I would be tempted to try it out and see how it goes. So should you force it?

Um, you know, depends on a few things. I’ll talk through that in a moment. But, you know, you don’t need my permission to try stuff. Right.

You can always, you know, like right click and force it. And if things go well, you can just be like, hey, I tuned a query. And if things don’t go well, you can always enforce it. Right.

But, uh, so, you know, like in the, in the, in the, in, it could totally work out in your favor. Right. It could, it could absolutely just like, like cool. It’s done. But you know, like you might need to be a little bit careful because you might need to investigate some other query metrics, uh, around that. Uh, you know, like, um, you could, you could look in, you know, I would, I would prefer if you used my store procedure, SP quickie store for this type of analysis.

But if you look up the, you know, cause you know, searching by query ID and query stores can’t be done. Right. You just can’t do it.

Uh, so if you use SP quickie store and you search for that query ID and you see all the different plans, then what you might find is some differing metrics between them. Uh, there might be something around CPU and duration. Well, I mean, obviously duration is different cause you said this one has the lowest, but you may find some other stuff to be interesting.

Like some other, some of the other plans may be parallel, uh, and some of the other plans, uh, may produce, uh, way more rows than the other. There is a row count column and query store, which can be very helpful for this. So, uh, it could be that your query has the lowest duration for a very small number of rows, like, I don’t know, 10 or a hundred or a thousand and other, uh, other times in this query runs, uh, the number of rows that it deals with is much higher.

Uh, in which case that low duration query, which might just be like some, you know, tiny little serial plan that, you know, this, you know, got a couple of nested loops joins and whatever, and just finds it’s, you know, thousand rows and moves on. Well, there’s a reason it’s the lowest duration, right? Cause it was the least like physical effort in the database.

You might find that other, other times in that query ran, there were much more rows involved. So what you could do is a couple things. Um, I have another store procedure at my GitHub repo that is code.erikdarling.com. And it is called SP query repro builder.

And what you can do is it’s almost has like the same plumbing as SP quickie store, but it has a different purpose. And the purpose is to give you a runnable query based on query store stuff. So like, you know, what, what I like, like the nice thing about query store is that it will, um, you know, uh, give you an execution plan and the query that produced that plan.

And there’s all sorts of stuff in that query plan that can help you reproduce the query. Like if your query is using, uh, you know, uh, formal parameters from your store procedure, and you know, you’re not using like recompile hints or temp tables or local variables, then it’ll set up that query to run with all of the appropriate settings. That your query ran with when it hit the database.

Uh, and you know, you will, what you will find is an executable version of the query with all of the different parameter values that were used for it. So you could experiment a bit with that and see if caching a plan for that low duration thing, and then running other, uh, you know, parameter values for that query maintains the same low duration. So that, that is the approach that I would take, right?

Cause that, that, that might make life easy. Like I said, though, there are some limitations to things. Like if you’re, if your query involves a temp table or a table variable, I can’t tell you what rows ended up in there. I can’t even tell you what the definition of it is.

Um, if you’re using local variables, those disappear with the, um, the values for those disappear in cached plans. And if you’re using recompile hints, then I will do my best to pull literal values out of the query plan that were used, but you will have to figure out which, um, which parameters they belong to. Cause I do not know that once you have recompiled, I, I lose that.

I lose that sense and sensibility. So that’s, that’s, that’s the approach I would take there. All right. Uh, one of my queries uses an eager index pool operator.

That’s costing 45% of the query. I understand it’s building a temporary index, but I can’t figure out what’s triggering it. There’s no Halloween protection needed and no self joins.

The query runs fine. If I break it into temp tables, is there a way to see what the optimizer is trying to protect against or how to prevent the spool without rewriting? Oh, well, it’s an eager index pool, which means one of two things.

And neither of them are Halloween protection. Uh, it means that now you usually tablespools are what get used for Halloween protection. And that’s of course to prevent, uh, you know, like sort of infinite loop modifications to a, to a, to a, to a query, uh, when a modification query runs.

But, uh, what, what you would want to do is try, try one of two things with your query, whichever, whichever table, uh, the eager index pool is being built off of the first thing you try. The first thing you do is add a force seek hint, uh, force seek hint to your query and see if there is an actual seekable index on that table. If you get an error, uh, then there isn’t one.

If, uh, you get an error, what you need to do is look at the eager index pool, because there will be some properties of the eager index pool, which will help you create a permanent index on the table. Um, so that SQL Server does not need to build an index every single time that your query runs. So just to make sure we covered that, uh, the, the eager index pool is not for Halloween protection.

It is, uh, strictly a performance optimization. Usually, well, actually it is not usually it’s on the inner side of a nested loops join. Uh, other things you could try doing if you felt like it, um, say option like hash join, probably, um, that might be one way to get around it, but I can’t guarantee that will be faster.

Um, usually the two things that you do is see a SQL Server, like missing an opportunity to use an index that I have with the force seek hint. If it’s not, then creating a permanent index will get usually get rid of the eager index pool. But sometimes you might find that you still need the force seek hint because the optimizer kind of a dumb, dumb some points.

All right. Let’s see what we got here. This is a long one.

Good Lord. Uh, let’s say your friend was hired as the SQL developer DBA for a very mature application. Uh, all right. Hot, mature applications want you to develop for them or something.

Uh, this application uses a model where each client has their own database, but each database has the same schema. Wild. Never heard of that before.

Uh, most of these databases are hosted and hosted in Azure SQL DB. God help you. I wish I had some holy water to throw on you. However, many top paying clients host their own server on prem, uh, own server on prem SQL and refuse to upgrade past 2014, 2016. Your friend notices a lot of transformations done in procedures are way quicker using some of the features function, the newer versions.

Would your friend be an idiot for creating two separate variables, one with the SQL statement for the old versions and one for new, then executing the appropriate one based on server property addition value? How would you handle this? Also, will you be my friend?

Thanks from hopefully your new friend. Well, I don’t know. You, you, you, you might talk too much to be my friend. I, I can be honest with you. It’s a, it’s a lot of words. The beginning and end of that are very far apart. I’m exhausted now.

Um, so I, I, I don’t know if I would handle this like in the code, like at execution time. Uh, I would, I would probably be more, um, in favor of like, like this being a code deployment strategy. Like, you know, you’re, you’re rolling out code and you’re like, oh, you’re on, you’re an Azure SQL DB where you have access to new stuff, but it’s still Azure SQL DB.

So it sucks. Uh, then, then you would, then you would roll out the, like the specific code for that. Um, I would, I would probably not want to make this a like runtime decision in every like store procedure or query that’s executing.

Um, like, like hopefully you’re using store procedures where, you know, you can like, make, you know, like change your deployments. Uh, process to, uh, roll out like a different code base to, uh, a different, uh, different, like, you know, version edition of SQL Server. But, um, I, I don’t think I would want to do this in the store procedure.

Cause you’re just thinking like very, very long complex store procedures, depending on how long and complex your store procedures are currently. I would be a little scared of that. Um, you know, even with dynamic SQL, I would be, I would be a little frightened of that.

Um, you know, if branches and query plans have, uh, very, very strange side effects, unless you, uh, bury things. So I suppose one way of handling that would be to change if like, if you, if you just wanted to straight deploy all the code, no matter what, one way of handling that would be to change the model to, um, have, have like a, like a, like a parent child store procedure thing. And then be like, Hey, if like, you know, like run this parent store procedure, like take all the parameters that we would take from, uh, like whatever the call is.

And then like execute a child store procedure based on like environment variables. That would be one way of handling it where, you know, you don’t have to tinker with the actual deployment process too much. And you still get sort of like the runtime effect of things, but without like making your store procedures insanely long and complex and dealing with like the, you know, the sort of like a lot of the repercussions of like if branching and store procedures, that would be, that would be another way of handling it.

But, uh, like I said, I wouldn’t, I wouldn’t want to, I wouldn’t want to be like, I wouldn’t want like giant if branches in the store procedure telling you, you know, dictating which, which path to follow for like a bunch of queries. Like that, that’s not a good time. So one of the, one of those other two things, either deployment specific code, uh, based on environment variables or a parent child caller situation with your store procedure, where everyone gets all the code, but only like one store procedure, one like version of the store procedure will run based on those environment variables.

All right. Uh, Postgres and Oracle have skip scans. Come on.

Postgres just got skip scans. Don’t act like, don’t act all highfalutin with me, pal. Um, Postgres and Oracle have skip scans. Why doesn’t SQL Server? I don’t know.

They haven’t put it in there. Maybe they will someday. Uh, skip scans are cool though. And, uh, I, I, I do, I do sometimes wish SQL Server had them because like what, like what a skip scan means is like you would have to like worry a whole lot less about like a lot of different, like nonclustered index permutations for things. So like, let’s say you have, uh, like, I don’t know, let’s just use like, you can either like do like the posts or the votes table in, in the stack overflow database where in the post table, you have a post type ID.

It’s a like highly non unique. Um, you know, cause it’s like just post types. It’s like eight of them and like, like most of them are one and two, which are questions and answers.

And like in the votes table, you have vote type ID. And like most of them are one, two or three, which is like marked as answer up, vote down, vote. And so like, like, like if you had an index that leads with either one of those, but then you have like another, like another column in the table, I think probably let’s just make it easy.

Let’s say it’s on the post table and you have an index on like post type ID and score. And your query is like, you know, like, like just like where score is, um, like score equals 1000, right? Just to throw an example out there.

If you don’t have an index that leads with score, you’re looking at having to scan, um, scan an index in order to, uh, like figure out like, like what, what score is 1000. And like the typical B tree index, that means like you’re scanning the entire thing, right? So like your index leads with vote type ID.

So your index is ordered by vote type ID. And then within like, you know, all the post type ID ones, like score is ordered in ascending order from like, you know, let’s just say zero to one. Like a five kabillion, right?

Uh, what a skip scan allows you to do is like, essentially it gives you like a, like, like a, almost like a double seek thing where like, you’ll break the index into chunks. So like the first thing you would do, it would go like, let’s just say like you would replace it with like post type ID equals one. And then you find all the thousands for post type ID equals one.

And then instead of like, like just scanning and scanning, like then the next thing you would do, you would skip and then you would jump to like post type ID equals two. Then you would find all the scores, score equals a thousand for post type ID two. And then like three and four and five until you finish and get to eight.

So, so like, you just kind of like chunk and index up and you just almost like a, like, like, like seek. And then many like seek to like what your actual predicate is. And then like another one.

And then you, it’s almost like if you did like a union all right. So like one way to, one way to think about that is like, like, like instead of saying like score equals a thousand, it would be like we’re post type ID equals one and score equals a thousand or post type ID equals two and score equals a thousand. It’s almost like you just union all of those queries together.

So skip scans would be awesome to have, right? Like they’re like pretty cool. Cause right now you have to say you can write some like weird recursive CTE queries to like emulate them, but it would be great if like we just had them, right? It’d be a very powerful thing for, especially people who like a lot of reasons why like so many over index tables and SQL servers.

Cause we don’t have nice things like that. All right. Uh, all right.

You can be my friend. Look at you with your nice pithy answers that allow me to talk about things. I can sell. I like you. I am interested in your AI course, but my company probably won’t be on SQL Server 2025 for a while. How relevant will this content be in a year or two?

The AI world seems to change rapidly. You are right. The, the greater AI world changes very quick. Every week there is a new most powerful reasoning thinking thing yet that like just blows the doors off everything that has happened before and is revolutionizing the AI world. Microsoft SQL Server ain’t that right?

Microsoft SQL Server moves at a glacial pace because I mean, you know, it’s never been terribly fast moving, but you know, let’s, let’s not pretend that, uh, you know, fabric isn’t training every valuable resource and dumping it into a useless cesspool. But anyway, um, the stuff that I cover in my AI course, the only thing that would like, like change in the material is certain preview features may become generally available. So like vector indexes, like vector indexes, like, like, like, like a couple other things that stuff might go from G from, from preview to generally available.

And I will update the course content is that stuff changes. But the course content itself is focused largely on, you know, just like the best way to approach, um, you know, vector stuff in SQL, certain SQL Server, how it works and how it works is not going to change that much. And I do cover all the preview features in there.

So like, it’s not like you’re just gonna like, you’re just gonna be like preview features. I don’t know. Like, like I do talk through all of the preview features in there, like, you know, like how they work, what they do, like vector indexes, vector search, um, you know, float 16 for what it’s worth. So like, I do talk through all that stuff and I will again, update the material when it, you know, changes, if it changes, because it’s not guaranteed that anything will go from preview to generally available.

But, um, you know, like, like, like really it’s, it’s, it’s a lot of just like foundational material, but like, like, like how it works, like what, what you should care about the best way to do things. Like there’s a lot of stuff in there. Um, it doesn’t make a difference if the wider AI world is changing.

It’s how you use it in the context of SQL Server will not change. So, you could buy it now and watch it and be prepared for a year or two. You could buy it now and in a year or two, watch it.

It doesn’t matter. Right? Uh, just buy it. Buy it. Arrgh. Come on. Rent is due. Buy the course.

All right. That’s probably good for me here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you buy the course because rent is due. I will see you in tomorrow’s video where actually, you know what, I’m going to go, I’m going to be talking more about the new AI course. So I don’t know.

It’s one thing leads to another, doesn’t it? One thing always just drags right into another Monday to Tuesday to Wednesday to Thursday to Friday. Then you die.

All right. Thank you for watching.

Going Further

Emulating GETDATE() on Azure SQL Database

Posted on February 23, 2026March 1, 2026 by Joe Obbish

GETDATE() always uses the UTC time zone on Azure SQL Database which can be a compatibility issue for applications that assume that GETDATE() is using a different time zone. Developers may wish to replace GETDATE() with code that continues to use their expected time zone to avoid UTC time reforms. For example, I’ve seen application code with over 50,000 references to the GETDATE function. Replacing all of those calls with GETUTCDATE() would be a herculean effort.

FAST 1

I don’t know how good Erik’s SEO is, but here’s a simple replacement if you don’t care about performance and just need something that gives you the previous behavior of GETDATE():

CREATE OR ALTER FUNCTION dbo.[GetDateNew]()
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
    RETURN SYSDATETIMEOFFSET() AT TIME ZONE N'Central Standard Time';
END;
GO

Keep reading if you’d like to do better than that.

AT TIME ZONE At Risk

AT TIME ZONE was a great addition to SQL Server 2016 that allowed for the retirement of many well-meaning but poorly implemented attempts to do time zone conversions in T-SQL. However, it was not without its own problems:

The CPU cost per execution was surprisingly high, but this was addressed in SQL Server 2022 and in Azure SQL Database.
AT TIME ZONE makes scalar UDFs ineligible for inlining.
AT TIME ZONE usage results in an “unknown” cardinality estimate.

You can see the cardinality estimate issue in action by populating a simple table with about 6 million rows.

DROP TABLE IF EXISTS dbo.Level100;

CREATE TABLE dbo.Level100 (
    TenantID INT NOT NULL,
    InsertTime DATETIME NOT NULL
);

INSERT INTO dbo.Level100 (TenantID, InsertTime)
SELECT q.RN % 4, DATEADD(MINUTE, -1 * RN, GETDATE())
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

CREATE INDEX TenantID_InsertTime ON dbo.Level100 (TenantID) INCLUDE (InsertTime);

CREATE INDEX InsertTime_TenantID ON dbo.Level100 (InsertTime) INCLUDE (TenantID);

The first query uses GETDATE():

SELECT COUNT(*)
FROM dbo.Level100 l
WHERE l.TenantID = 2
AND l.InsertTime > DATEADD(MINUTE, -10, GETDATE())
OPTION (MAXDOP 1);

The filter against TenantID is expected to return 25% of the rows in the table and the filter against InsertTime is expected to return less than 0.001% of the rows. As expected, the query optimizer chooses to filter against the InsertTime_TenantID index and the query finishes instantly:

a66 good

The second query uses AT TIME ZONE:

SELECT COUNT(*)
FROM dbo.Level100 l
WHERE l.TenantID = 2
AND l.InsertTime > DATEADD(MINUTE, -10, CAST(SYSDATETIMEOFFSET() AT TIME ZONE N'Central Standard Time' AS DATETIME))
OPTION (MAXDOP 1);

Once again, the filter against TenantID is expected to return 25% of the rows in the table. However, the presence of AT TIME ZONE results in an unknown inequality estimate against InsertTime which is 30%. The query optimizer makes a different choice and goes with the TenantID_InsertTime index because 25% < 30%:

a66 bad

Meme Preparation

It seems that we need to return to the bad old days before we had AT TIME ZONE available in SQL Server. One advantage that we have is that the function only needs to do the time zone conversion for the current moment in time. Central time is six hours behind UTC at the time of publication, so we could construct a function like this:

CREATE OR ALTER FUNCTION dbo.[GetDateCT](@PassInGetDateUTC DATETIME)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEADD(HOUR, -6, @PassInGetDateUTC);
END;
GO

The input parameter is there to make the function eligible for inlining because GETDATE() and other similar functions prevent inlining. Returning to the same query as before:

SELECT COUNT(*)
FROM dbo.Level100 l
WHERE l.TenantID = 2
AND l.InsertTime > DATEADD(MINUTE, -10, dbo.[GetDateCT](GETUTCDATE()))
OPTION (MAXDOP 1);

We can see that the new function results in a reasonable cardinality estimate and the query optimizer makes the correct index choice:

a66 also good

Of course, this function definition will need to be updated a few times per year to deal with daylight savings time changes. It isn’t too difficult to design a process to automatically perform these updates, but honestly I expect that the hassle of doing something will exceed the tolerance of most companies. However, this option is available to you if you need to preserve the usually superior cardinality estimation behavior enjoyed with GETDATE().

How to Convert Time Zones in SQL Server?

a66 maymay

Parsing and Arsing

Any solution you pick, other than a naked reference to SYSDATETIMEOFFSET() along with AT TIME ZONE, may result in parsing issues in your code. For example, some parts of certain queries do not allow for subqueries, so a subquery replacement against a table-valued function will not work as a direct replacement. Even the simplest scalar UDF reference can result in code that fails to parse, such as the following:

CREATE TABLE #t (
    StupidColumn DATETIME dbo.[GetDateNew]()
);

Fortunately, most of these issues are likely rare in practice and should be straightforward to address.

Keep in mind that you also need to fix GETDATE() references in areas such as default column values, constraints, computed columns, default column tables for table types, and so on. I would personally use a naked reference to SYSDATETIMEOFFSET() along with AT TIME ZONE for these areas as I see no benefit in using a scalar UDF.

Summary of Issues for Various Solutions

I was going to put some introduction text here, but whatever, you can figure out the point of this section on your own.

Inline table-valued function approach

Most T-SQL compatibility issues
Very verbose
Cardinality issues with AT TIME ZONE

Naked SYSDATETIMEOFFSET() AT TIME ZONE N’Central Standard Time’ replacement

Very verbose
Cardinality issues with AT TIME ZONE

Simple scalar UDF with AT TIME ZONE:

UDF is not eligible for inlining
UDF will return different values as the query executes which probably isn’t what you want

Non-static scalar UDF:

Function definition must occasionally be refreshed
Somewhat verbose
Some types of queries, such as those with CTEs, will not allow any UDF to be inlined

Final Thoughts

Messy no-win situations like this highlight the importance of picking the right cloud platform for your application. Azure SQL Managed Instance and Azure VMs avoid this problem entirely. Thanks for reading!

Get AI-Ready With Erik: A Little About Cross Table Searches

Posted on February 20, 2026January 23, 2026 by Erik Darling

Get AI-Ready With Erik: A Little About Cross Table Searches

Video Summary

In this video, I delve into the exciting world of AI preparedness and explore a practical application: finding related content in real-time. Whether you’re building advanced chatbots using SQL Server’s vector capabilities or managing a Q&A site like StackOverflow, being able to suggest potential duplicates before submission can save time and effort for both users and moderators. I demonstrate how vector distance functions can help identify near-duplicates by embedding query vectors and comparing them against existing content, providing a likelihood score that filters out irrelevant results. While the performance isn’t always optimal due to row-by-row comparisons, this method offers significant improvements over keyword searches, especially in understanding the intent behind user queries. By combining similarity search with keyword or full-text search strategies, we can achieve more accurate and comprehensive results, ensuring that users get the most relevant information as quickly as possible.

Full Transcript

Erik Darling, Darling Data, continuing. Are you ready for some AI? AI preparedness, football preparedness, all very important things for us to prepare for. Today we’re going to talk a little bit about sort of like the idea of finding related content. So, you know, for you, whether you are building the world’s greatest chatbot based on SQL Server’s vector capabilities, or you just want to have someone like, you know, I don’t know, let’s say you run a Q&A site like StackOverflow, or I don’t know, let’s just say someone asks a question or something and you want to show them some potential duplicates before they submit thing, submit something. If you wanted to do this as they’re typing, you know, you would probably need to use like keyword or full text search to accomplish that because like, like generating and embedding as someone like, you know, types a letter and like, I spelled that wrong, delete, delete, delete, delete. It would be sloppy and messy. So we don’t want to necessarily do that. But, you know, let’s say, you know, someone’s like submits a question, but like, like you have like a, like a, like a middle screen, right? Like you, you preserve all their work, but then you also, you’re also like, hey, these other things look pretty close to what you’re doing.

What you were looking for, like, I think, I think maybe, maybe might be on the right track here. Maybe you should check some of these resources out before you, you know, send in your new thing and set a bunch of people running around trying to find answers for you and like, wait a minute, that reminds me of this thing. You know, you could maybe, you know, do a slightly better job. Right? Maybe save them some time, save yourself some time, save the world some time, right? So we can’t save the world some time. What are, what do we have computers for? So what we’ll do here is sort of expand on just saying, hey, vector distance, you know, whatever. Now we can, you know, get, maybe get a little feedback about what we’re doing. And we’ll have a new column in our select list that’ll show the likeliness of the query being a near duplicate. So we’re going to use this sort of, I mean, it’s, I’m going to say it’s, it’s arbitrary in that, like, like it makes sense for what I’m doing here and the data I’m working with here.

In your real life workload, query, embedding life, you might choose a different number to decide how likely a duplicate, likely a duplicate something might be. And then we are also going to start filtering on this. Now, I’m going to be honest with you.

This, this is, if you are coming to this from a DBA perspective, from like a performance-ish tuning perspective, which is again, why I started looking at all this stuff. Cause I was like, Hey, I bet this is going to need some performance tuning work. This does not get pushed anywhere that might be useful.

This is not like a SARGA-able predicate. SQL Server has to, again, compare this row by row, right? You have to take each row, feed it into the vector distance function, compare the table embedding to the local variable, which again, like from a, from a like performance cardinality perspective, the local variable doesn’t make a difference here, right?

Like you can throw a recompile on this. It’s not going to help anything. SQL Server is not going to be like, Oh, now I know that’s not a, you know, density vector guess. I’m going to give you this other, I’m going to give you like a good cardinality estimate based on the histogram.

You don’t get that. So like, don’t, don’t get tied up on that. But of course the vector distance, distance function has to compare again, sort of like in a row by row, uh, row by row form. Like, you know, like put this embedding in compared to this embedding.

Is that less than 0.30? Uh, we’ll find out when it runs, right? So like it, like the performance is not going to be awesome for that. Again, this is where sort of like the vector search or like vector index stuff comes in, vector indexing comes in.

But those still being in preview and Microsoft releasing a woefully incomplete implementation of all this, uh, at RTM and calling it AI ready. Eh, wrong. Eh, boy.

What do we do with ourselves? But anyway, let’s run this query. And we get some results back. And this was not terribly, this was not terribly slow because, you know, we don’t have a lot of terribly, we don’t have a lot, a lot of data in here. And I have a pretty decent VM backing this thing up.

But, uh, what’s, what’s interesting, right, is, um, so we have like this, this filter here, right? And this is where our predicate ends up for vector distance, right? Say less than 2.99 E minus 0, 1, right?

So, um, like we scan the whole post table, we bring all million rows out of that. We compare, we do the filtering of the vector distance function in there, right? You can see vector distance, cosine, et cetera, in this filter operator.

Again, because it’s not struggle. This is why things like pre-filtering, like anything, anything you can, we could push. I mean, there’s nothing we can really push to the post embeddings table. But, you know, like for the post table itself, there’s like some pre-filtering we could do there to at least, you know, get some of the rows out.

But, you know, sometimes this ends, like if this were like a regular nested loops join, sometimes the vector distance function ends up at the nested loops join. We get an adaptive join here. So, you know, all that does a thing.

Anyway, I just really wanted to show you that it wasn’t a sargable predicate. We couldn’t like scan the post table with the vector distance function there filtering out rows when we touch the table. But, you know, the results that we get back are at least filtered distance wise to things that would make sense for us.

And the titles that we get back are pretty close to, you know, how do I speed up my SQL queries? But, you know, this is like kind of a funny thing because it’s like how do I speed up my SQL queries? My SQL queries or my SQL queries, right?

Because my SQL is a database, but my SQL, it could be anything, right? SQL could be in SQL Server, Postgres, Oracle, DB2, Snowflake, you name it, right? SQL is in like everything.

Is SQL the original AI? It’s like, no, it’s SQL. But, yeah. So, like a bunch of these results are like, you know, like my SQL, my SQL. But, like, not necessarily like, but we weren’t necessarily searching for anything specific to my SQL, the database. We were just thinking about my, like, SQL that I own.

So, this is like one of those things that similarity in vector search can miss and why you might need to think about combining some alternate search strategies to sort of give you the best of, like, all worlds when you’re searching. Because, like, not only do you need to worry, like, think about similarity, you need to think about the things that similarity search might miss in some cases.

Like, certain contextual things that might make, like, might be really important where you need to sort of bring keyword search or some other, like, keyword search to find, like, more exact stuff. Or you might need to start adding in, like, you know, some sort of waiting to make sure that the quality of the search result is okay, too. So, it’s like, you know, like, all of this stuff is, you know, like, about speeding up SQL queries, right?

There’s all sorts of things in here that might not even show up. Like, I think this is a great example where, you know, the search phrase was, how do I speed up my SQL queries? If you were to take that and just apply, like, pure, like, keyword or full-text search semantics to this query, you probably wouldn’t see SQL queries taking too long.

But, like, the similarity search figures out, oh, like, how do I speed up my SQL queries is pretty close to SQL queries taking too long, right? Like, so that sort of, like, intent of the words, that’s what doesn’t come out in, like, a lot of other search forms that vector search or similarity search really does bring out. Right?

So, it can also be useful. Let’s say that, like, you know, let’s say you’re on a Q&A site where people ask questions, like Stack Overflow. And you might have, like, swarms of completely unpaid slave labor moderators in there who, you know, have to deal with, like, cues of things like duplicate question flags and all sorts of other stuff. But this could be really useful for them to help them find duplicate questions.

Because, again, like, you know, like, reviewing the content of a thing beyond, like, just, like, keyword search stuff. Like, if you were looking, like, how do I speed up my SQL queries? You might find an exact duplicate of, like, the question body in, like, you know, SQL queries taking too long that wasn’t obvious exactly from the title.

So, like, if you’re, like, if you have any sort of, like, moderation thing happening, like we do in, like, or they do in Stack Overflow. I don’t work for Stack Overflow. But, like, you might have something like this where, like, moderators have to go look at stuff and you might want to help moderators find, like, oh, yeah, that is a duplicate of that.

Like, oh, okay. Oh, well, you know, someone flagged it as a duplicate of this thing, but it’s really a duplicate of this thing. So what we’ll do is we’ll take, I have a source post and a query vector.

And we are going to find our source post here. And I’m going to show you what exactly what we’re searching for. And then, like, just a way to screen for potential duplicates using a very similar search pattern to what we did before.

All right. So let’s go pretend that we are unpaid moderators and we are looking to try to find how to undo the most recent commits in Git and what we get back. Now, are these great?

I mean, are these, like, awesome across the board? No. But, you know, again, the similarity stuff can miss some certain things.

So, like, this does have, like, undo and get and commit. And, but this one, like, it has the word recent in it. Right.

So it’s, like, how to undo the most recent commits in Git. But this one thing that similarity search misses is negation. So, like, not. Like, like, like, I want this to, like, not be, like, like, saying, like, I wanted to undo the most recent. But the another one saying that isn’t the most recent.

Similarity search will miss stuff like that. Right. So, like, how to undo the most recent commits in Git. The most, like, the most similar thing we found is Git undo a commit that isn’t the most recent. But similarity search kind of barfs on that.

This one is a lot closer. I mean, if you actually, like, read, like, undo last commit. Like, how to undo the most recent commits. Like, that second one is actually, you know, like, like, in the literal sense, closer. Even though from, like, textual representation with float numbers.

This one, this one seems closer. How to get back to the most recent version in Git. Maybe not particularly there.

Right. Undo a particular commit in Git that’s been pushed to remote repos. I don’t know about that one. How can I undo the list, the last Git add commit. Yeah, that one, that one, that one’s probably closer there, too. So, like, similarity search is not perfect.

But there’s, like, a lot of stuff that similarity search might miss or might, you know, not catch in a perfect way that, you know, a human eyeball doing unpaid moderation on your, you know, your private equity website might, just might catch and understand a little bit better. Anyway, thank you for watching. Hope you enjoyed yourselves.

I hope you learned something. Again, the full version of all this content is available for purchase with the AI Ready coupon. The link for this is fully expressed down in the video description. But if you head over to training.erikdarling.com, you can buy the Get AI Ready with Erik course for $100 off.

It’s a great deal. Because then you’ll be AI Ready far before anyone else is. Except me.

I was AI Ready before you. That’s how I got to do all this. All right. Thank you for watching.

Going Further

Get AI-Ready With Erik: A Little About Finding Related Content

Posted on February 19, 2026January 23, 2026 by Erik Darling

Get AI-Ready With Erik: A Little About Finding Related Content

Video Summary

In this video, I delve into user search handling in AI systems, specifically focusing on how database administrators can manage and utilize user queries effectively. By leveraging embeddings generated by application layers, we explore a workflow where user searches are converted into vectors and stored in a searchable table. This allows for efficient similarity searches using vector distance functions to provide relevant results based on the user’s intent rather than relying on traditional keyword matching. I also discuss practical considerations such as ensuring unique search phrases through hashing, handling embedding data from application layers, and optimizing query performance with minimal impact on cardinality estimation.

Full Transcript

Erik Darling here with Darling Data, and we are going to continue our AI preparedness and talk a little bit about dealing with user searches. This is, of course, all brief snippets of the content that I have available in my Get AI Ready with Erik course. You can get it from training.erikdarling.com. Right now, it’s a hundred bucks off with the coupon code AIREADY. The link that you see is a good way to get it. And the link that you see above you floating in SQL Server Management Studio is down in the video description for your tender loving clicking. So we’ve got that going for us. Now, so far, what we’ve done is used existing embeddings as query vectors. Right? We said, hey, is any of this other stuff similar to this thing? Which, you know, can be useful. Right? That can be a useful thing to measure. It can be useful to pre-generate embeddings and say, is this thing similar to this other thing. But, you might have users in your database. Right? Shocking. Crazy, I know. Where we, the general EBA public may need to deal with these users. And these users may have their own specific questions to ask. So what we’re going to look at here is a workflow where the application does the embedding for us, right? So a user will type in a search query into some form in the front end. And then we’re going to pretend that like a service in the application layer generated an embedding based on that user search query and stuck it in the database. And now it’s our job to take that user search query and, you know, see if there’s anything similar to it.

Right? You know, developers love their app layers. So we’re going to app players. Whatever. But this video will focus mostly on like, you know, how you as a DBA might take those search phrases, stick them in a table and then do a search using them with the vector distance function. So you likely want a table that looks something like this, right? And you may, I don’t know, like, like, if I had to like really design this thing and not just have like a simple demo table, I’d probably want like a user ID or something else where I could like uniquely identify the user and their search queries. So when I go and get whatever someone sent in, I could, you know, find the right thing and all that. But for us, this is good enough right now where we’ll have the, you know, the general phrase that they sent in their search text. We’re going to hash that phrase so that we can make sure that we only have unique stuff in there. We don’t want, if someone has already asked the same question, then we would want to say, hey, we already have something. We already have that. Like, we won’t even have cache search results somewhere in our app player. And we might be able to use that instead.

We will have the embedding that was generated again in the app player. I’ve prefilled this table with the embeddings. We’re pretending that the app player is doing some magic and inserting into this table. And all that other good stuff. So if we look at what is currently in our search phrases table, these are all searches that users have submitted, right? If we zoom in on this, how do I make SQL queries faster? JavaScript array message. What is a foreign key? Python file handling. There’s a lot of stuff in here that we could probably use to, I don’t know.

These are all questions that people cared about. Loop without cursor SQL. How dare you? Cursors are great. So we’re going to start with this. Hey, you did it. I’m going to start with something here. Just to make life a little bit simpler, we will declare a query vector local variable and we’ll assign an embedding to that local variable. Normally, the local variable thing would be a bit icky. All right. If you’re a skilled SQL, skilled T-SQL practitioner, perhaps you’ve watched my other videos.

Perhaps we have, you have bought my prior trainings. Perhaps you have attended some of my prior trainings in person. You will know that local variables can often have deleterious effects on cardinality estimation. But in this case, it doesn’t matter because it is not an aware clause. It is just being fed into this vector distance function.

And it has no effect on cardinality estimation or anything else. So we don’t have to worry too much about that there. You could throw a recompile hint on this if you wanted. It would change absolutely nothing for you. But what we’re going to do is we’re going to ask, we’re going to look up the phrase, how do I make my SQL queries faster? We’re going to assign the embedding that was generated in the app layer for that search query. And then we’re going to find the top 10 queries, top 10 posts that might be similar to that. All right. So again, we are using the vector distance function. We are using the cosine equationatory things.

And here we are comparing the embedding in the post embeddings table to the query vector. And then we are ordering by the distance alias in ascending order so that we get back the most relevant results first. And we could think about other ways of doing this, right? Like we might like make maybe score would be a valid thing to also bring up, like maybe it’ll be like a little bit more high quality content or something. But for now, we’re just going to stick to just finding purely what is the most similar.

And if we look over here, we get some rather low scoring things back, right? So these might be the most similar, like, like I was saying, these might be the most similar things, but they might not be the most high quality things. We don’t even have a guarantee that like this thing has an answer, right? Because we’re just comparing titles.

We’re just saying, is the title of this thing similar to the title of any other things? What other titles is this title similar to? And these titles are quite similar, aren’t they? Right? Like how optimize SQL query, make it faster. How can I speed up the SQL query twice? Two different IDs with the same damn post title.

Speed up the SQL query to my SQL. Well, stop using my SQL. Don’t be stupid. SQL query is taking too long, right? So like all this stuff, pretty similar. And this is like the kind of, I mean, what I think is the kind of neat thing about vector search is that you can, you can search based on like what a user meant, what a user’s intent was.

And for DBAs, I mean, like think of all the, think of all the times you’ve yelled at developers for throwing in like, like wild card string searches, like, like where title, like percent something percent or title and title, like percent something percent. Like think of all the times you’ve screamed at them for this and like how you can now just go to be like, well, now we don’t have to do that anymore.

Now we can just take this magical embedding and compare it to this other magical embedding. And we can figure out how similar those embeddings are without having to do a bunch of wild cards, wild card string searches. It’s kind of a nice thing for DBAs because you no longer have this like, like, well, how are the developers going to figure out if this thing is like this or this or this or this or this or this or this or this or this without like, like just ruining your life, right?

And giving you, making steam come out your ears. It’s not a good feeling. Trust me. I’ve had steam go in. I’ve had steam come out. It’s not.

It’s cleansing, but it’s painful. Let’s, let’s try another one using something similar, right? We’re going to take this query vector for what is a foreign key.

What is a foreign key? Why would I stick it in a lock if I know it’s not going to work? So it’s from a foreign country and foreign countries all have far different locking mechanisms than American locks do.

But if we run this query, right? Well, now we’re going to do the same thing where we’re going to, you know, get our search phrase. We are going to plug that into the vector distance function.

We are going to compare the, the, the embedding for that, for our, what is a foreign key search phrase to other things in the post table. Right? And we’re going to get back some pretty similar stuff here.

Right? Right. What exactly is a foreign key? Foreign keys, foreign keys. What do they do for me? How can foreign keys benefit me?

Right. And then, but like all this stuff is generally about foreign keys. And this is without having to do like, again, like the like percent foreign key percent, you know, inevitably having to worry about case sensitivity or something like that. But we, we, we get a bunch of stuff back about foreign keys.

Now, again, we have not established like a, like a high quality benchmark for this. Right? Because the scores for this are all over the place.

11, 1, 7, 15, minus 1, 0, 2. Right? There’s like, there’s some stuff in there where, you know, you might want some of the higher scoring stuff to maybe show up a little bit higher in the search results. We’ll talk about that later and how to sort of like weight things differently.

Because, you know, distance, well, it is a good, you know, indicator of similarity between two, two, two, two strings. Right? The, the, the, the vectors and the embeddings, they’re a good indicator of like similarity there, but they’re, they’re not like, they’re not a terrible, good, terribly good measure of like, is the content after that quality?

Like, like it could be completely wrong. It could be an AI generated response, which is full of malarkey. Right?

If you’ve ever asked the AI a question and gotten a bunch of stuff back where you’re like, none, none, none of that is right. None of, none of that is a key insight. Thank you.

You, it doesn’t do anything to tell us like, like how high quality, like the information after that is. Right? It can tell us how similar these things are. And these things are very similar.

They all say foreign keys in them. Right? Granted, there’s a little bit of a spread there between like MySQL and like, you know, general databases and stuff. But what you get back is at least, you know, foreign key related without having to search like percent, foreign percent key percent over and over again. Like doing traditional keyword searches that often looked like this.

Right? Like SQL performance and foreign in key and stuff like that. And we all know how we feel about this. And while you can get, you know, stuff that says, you know, insert, we can get stuff back that is SQL and performance.

You know, it, it, it, it does not give us like, we’d have to give it way more things, way more tokens, if you will, in order to narrow it to think something that we care about, like SQL Server. And then like maybe a SQL Server version and then like, I don’t know, is this, we just like, like how many likes are you going to throw in there before you’re like, ah, I finally found what I was looking for. It’s a lot of likes, a lot of likes, unlike my posts.

It’s a lot of likes. All right. Thank you for watching. Hope you enjoyed yourselves.

I hope you learned something. And again, if you are interested in the full course content, it is all available for sale and purchase at that link, which is down in the video description. The coupon code embedded will get you 100 smackaroos, dollaroos, buckaroos.

I’m going to stop there. Off the, off the price. So, thank you and goodbye.

Going Further

Get AI-Ready With Erik: A Little About VECTOR_DISTANCE

Posted on February 18, 2026January 23, 2026 by Erik Darling

Get AI-Ready With Erik: A Little About VECTOR_DISTANCE

Video Summary

In this video, I discuss vector distance measures in the context of similarity search, specifically focusing on cosine and Euclidean distances. I start by giving a shout-out to ChatGPT for creating an apt back-to-work image that perfectly captured the dreary mood of a cold morning. The video then delves into how cosine distance ignores the length of vectors, making it ideal for text similarity searches where direction is more important than magnitude. In contrast, Euclidean distance measures the straight-line distance between points, which can be useful in certain scenarios but often isn’t as relevant when dealing with vector embeddings and textual data. To illustrate these concepts, I walk through a practical example using SQL queries to find similar posts based on performance-related questions, demonstrating how cosine similarity provides more meaningful results for text-based comparisons.

Full Transcript

Erik Darling here with Darling Data. And before we jump into the, we’re not, I’m not going to go through the whole deck, but I just want to, did want to give out, give a shout out to my boy ChatGPT for coming up with a banger back to work image for 2026. Right? I was just like, give me, give me something grim, right? And nailed it. My man over here is missing an arm again. It froze off. Everyone’s coffee is a disaster. Like, like, like that. I don’t, I don’t know, I don’t know what that is. That’s like a coffee with a muffin in it. But yeah. Uh, good job. Good job. All right. Nailed it. All right. Everyone looking miserable and cold, hunched over. That’s right. It’s like a Dostoyevsky or something. Anyway, with that out of the way, let’s talk a little bit about vector distance. Uh, so I, I, like, I, like I said before, like if you’re doing similarity search, you like, like 100 emoji, just want to use cosine. All right. Nothing else makes sense to use for it. Uh, okay. Maybe not like a hundred percent. There might be some weird, like real weird stuff, but if you’re just getting into this, you’re probably not going to be getting into weird stuff. Someone’s just probably going to be like, Hey, tell me, like, give me some recommendations. Like, give me some recommendations.

I mean, like, like, like build a chat bot for me. And like, this, this stuff is much more useful for that. Um, but the thing with cosine is like, it ignores what’s, let’s call it the length of, of, of a vector. The length of a vector you can think of as just being like, if you added up all the numbers in the vector, how long is it? Right. Um, cosine doesn’t care about that. It just cares if all of the vectors are sort of like near each other. Right. So, um, it doesn’t care about like, like, like, like this example here. It doesn’t care if one, one set of vectors is one, two, three, and the other is two, four, six.

It means that they’re kind of like pointing in the right general neighborhood. Right. Um, but, uh, with the Euclidean stuff, uh, it does care about length. Right. Um, Euclide measures stuff in sort of like, uh, a straight line distance between two points. It’s like, again, like GPS coordinates. So like, if, if we were to say like, if, if I were to walk from point A to point B, how far is that? Um, so if you have the same vectors, like one, two, three, and two, four, six, they’re like, like this over here, like granted they’re pointing in the same direction, but Euclide is just like, well, one might be over here. One might be over here. But like, even though they’re still going the same way, it’s not like, they’re not close to each other in that like similarity, like cosine way.

Where like, even though like, you know, like they might be far apart in whatever cosine space there is in the world, they’re still pointing in the same general direction for things. So I realized that’s a little dumb and stupid, but you know, sounding, but it’s just kind of a good generalization of how things work. So like if we took these two, right, where we have one, zero, zero, and then a hundred, zero, zero, uh, cosine is going to be like, you know what?

You are pointing in the same general direction, right? Uh, but Euclid is like, you are 99 Euclids apart, right? You’re like, this is like, well, there’s a, like, there’s 99, there’s a difference of 99 between one and a hundred. So you are 99 Euclids apart. Um, the other one is just like, eh, well, you know, well, like, you know, we’re going from one to zero and the other one’s going from a hundred to zero.

So that’s what that is. Um, I don’t know why one of these is capitalized. The other one isn’t, uh, that’s a very strange artifact of something, but we’ll fix that right now. Uh, and then if we look at these, right, um, you know, like now we have negative one, negative 50, negative a hundred, and we have a hundred, 500, 1000. Now cosine is just like, you’re a horrible match, right? Cause up here, like you, uh, up here cosine was just like, you’re a great match, right? Zero.

We go from zero to two, right? So the closer something is to absolute zero, the better match they are. Like then we run this one, right? It was like negative, negative, negative, positive, positive, positive. And we look at these. Now the cosine is like, you are very, very far apart. You are not going in the same direction.

We have negative numbers. We have positive numbers. They’re all different. Um, so this one is almost as imperfect a match as you can get at 1.99, blah, blah, blah. Euclid is just like, well, now you’re 1,233 Euclids apart, right? So like, like Euclid is just, again, it’s measuring like GPS coordinates. Cosine is measuring the similarity in the way that the vector, um, the way that the, uh, the floating point numbers in the vectors are sort of pointing, right?

Like the, just think of my fingers as arrows and like the negative ones are pointing this way and the positive ones are pointing this way. And so that’s why these are very, very far apart as far as cosine similarity goes now. But if we do this, right, and this one, like, you know, it almost feels like, where are they? Where are they going?

Like, like, cause we have one and 50 and a hundred and then a hundred and 500 and a thousand. Like, like, like there’s still like, those arrows are still pointing in the same general direction, right? Right? Cause it’s one 50, a hundred, a hundred, 500, a thousand.

Like they’re still pointing in the same general direction. It’ll be an okay match. It’s not going to be like a perfect match like before where like, but it’ll be an okay match. But Euclid will of course say you are many Euclids apart, right?

So like the, the cosine similarity is pretty good, right? It’s not like, like, wow, that’s amazing. But, uh, Euclid is like, you’re a thousand and 11 Euclids apart just because all it cares about is the, the, the length, right?

So it’s a hundred and 500 and a thousand and the other one’s one and 50 and a hundred. Like you’re like completely two different points on this map where the cosine is just like, you know, you, you, you could be, you could have some things in common, right? It’s like matchmaker, matchmaker.

Right? So these two are a little bit more similar, but, uh, text, text similarity, you generally want to use cosine, right? I said that a million times now because you don’t care how many Euclids apart two texts are.

You care how similar those texts are to each other and the vectors that just sort of, and the, the vectors, right? The embeddings, uh, the various floating point numbers that describe, uh, sort of the text of the question, right? The intent, the meaning of the question, those are what you care about similarity on.

So if we, uh, we’re just going to do a quick example of that here where let’s say, um, we wanted to, oh, well, I’ll just show you. So it’s a little bit, a lot easier to see in the, in the results. And if I just sit here and yak about what we’re, what we’re after, if we run this query, right?

And what we’re essentially doing is asking for, uh, you know, the, the top one, I mean, granted, we don’t even need this hunt greater than a hundred filter. Order by score descending. That was a little silly on my part.

I think that was just testing. But if we look at this, this query, right, we’re going to find the top one post where that’s about performance. Right. And, uh, we’ll look at the title of what we’re searching for. And then we’ll look at the top 10 results where the vector distance, right?

And one thing that’s important here is you want to, like when you’re ordering by, when you’re trying to figure out what is most similar, uh, you want to order by distance in ascending order. Because that’ll give you the lowest numbers for us, the most similar stuff first. So if we look at what we got back, right?

The question, the question that we were looking for, right? The top scoring question about performance is improve insert per second performance of SQLite. And then as far as like similar things that we got back, uh, like, like here’s our distances, right?

So, you know, start off, you know, pretty similar and then, you know, things get a little bit less similar as we go down the list. But this is sort of how the similarity thing works, right? We have the top one over here is SQLite insert performance, which, you know, pretty, pretty similar to that, right?

Pretty similar. Then SQLite insert very slow question mark, right? Still pretty close to this, right?

Right. Getting there. And then faster bulk inserts in SQLite 3, uh, you know, uh, the, the, the vectors and the embeddings are not sort of good at figuring out, like, if there’s a difference between SQLite and SQLite 3. It’s like the, the, the same way they’re not good at figuring, figuring out if there’s a difference between like SQL 2008 and 2008 R2, right?

The 3 in SQLite 3 and the R2 and SQL Server 2008 don’t really like stand out, uh, to, to the vectors. And so you kind of get some weird crossover there. And then we have an Android SQLite database, slow insertion.

Romance is alive. And then SQLite .NET performance, how to speed up things. Well, you know, still SQLite, still performance, but not necessarily about inserts.

And then slow SQLite insert using JDBC drivers and Java. Well, I mean, it’s still about slow SQLite inserts, but like is the original, like does JDBC Java matter? Do we, do we need, is that, is that relevant?

Then speed up SQL inserts. Well, we have drifted away from SQLite, but we are still dealing with SQL inserts. And then we, we start, we, we, we sort of start losing the script down here a little bit. Don’t we?

SQLite optimization for millions of entries. SQLite optimization. SQLite slowing down after millions of rows. How to speed up. So there’s still stuff about SQLite and performance. These are still relatively similar question titles, right?

But, but we’re, we’re, we’re sort of starting to get away from like just purely improving SQLite insert performance. Okay. So anyway, uh, that’s what we did there.

And the way we did that was we grabbed, uh, a post ID and embedding from our post embeddings table. We just filled that up in the last video. And then, uh, we looked at what it was.

We could, you know, figure out, Hey, like is what we’re getting back close to what we were asked for. And then what we did down here was use the vector distance function, right? Again, with the, always with the cosine when we want the similarity.

Uh, and then we compared the embedding in the, in the post embeddings table, the, all the embeddings in the post embeddings table to the query vector that we got from up here. Right? So that’s this thing, the embedding that we assigned up there.

And then we ordered those, um, results by the distance column in ascending order. And we removed the original post ID from there. So if we don’t do that, then we’ll get back like the same thing.

Like we’ll get back the post that we were just looking for a similar post to. And we don’t need that. It’s no good for us. So, uh, yeah.

Anyway, uh, that’s probably good for this one. Uh, thank you for watching. Hope you enjoyed yourselves. I hope you learned something and I hope you enjoyed this banger of an image from my, my, my boys at chat GPT. All right.

Thank you for watching.

Going Further

Get AI-Ready With Erik: A Little About Generating Embeddings

Posted on February 17, 2026January 23, 2026 by Erik Darling

Get AI-Ready With Erik: A Little About Generating Embeddings

Video Summary

In this video, I dive into the world of vector columns and embeddings in SQL Server 2025, specifically focusing on Azure SQL Database and Azure Managed Instance. I explore the challenges and considerations involved when adding a vector column to an existing table, such as the need for indexing and potential performance impacts. To illustrate these concepts, I walk through creating a demo table called `post_embeddings_demo` and demonstrate how to use Ollama—a free and user-friendly tool—to generate embeddings for text data. By the end of this video, you’ll understand the process of backfilling your tables with vector data and appreciate the significant storage requirements involved. Whether you’re looking to enhance your SQL Server skills or simply curious about integrating AI into your database operations, there’s plenty here to learn. Don’t forget to check out my training site for the full course, where you can get a $100 discount using the coupon code AIREADY and Get AI Ready with Erik!

Full Transcript

Erik Darling here with Darling Data, returning once more into the vector void to try to sell this course. To try to help the community learn more about vector and SQL Server 2025 Azure SQL database and Azure managed instance. Sell this course, which you can buy from me now.

The, the low, low price of $100 off with the coupon code AIREADY. The course is of course, of course, of course, of course, called Get AI Ready with Erik. It is available over on my training site.

This is just small snippet teaser material from the aforementioned course that will hopefully spur you into buying this. So you can, you can show this to your boss and say, this, this, this man is trustworthy and will teach me all about AI and SQL Server. AI, right?

Anyway, got stuff to do, don’t we? Uh, now, we’re, we’re going to be completely honest with you. Uh, you most likely, um, in your heart of hearts, in your brain of brains, your guts of guts, will know that adding a vector column to an existing table is probably not a great idea.

Because, not only do you have to add this column to the table, which if it’s, you know, there are all sorts of metadata only things that make that not a big deal, but now you have to fill that column up. Uh, and if the, the, the table that you are attaching this column to is of any importance, um, you’re going to have a couple issues with it. One, uh, you know, the, the, the indexing for this table may need to start taking this column into account in some way.

Um, of course, you cannot have a vector column in the key of an index, but the includes might, might, might come into play some, at some point. Um, and now your table is about to get a lot bigger. Not only that, but you, you have to, you have to batch fill your table with this vector data.

If it’s a large table, you certainly don’t want to try to fill it all in one shot. So most likely what you will want to do is create a sort of lookup table for this data, where it can remain unattached from the rest of the data that you actually care about. But you can still, uh, you can still access fairly easily.

So in, in our case, uh, what we, what we, what we are going to do, or rather what I have done is created a sort of side piece table called post embeddings demo. Um, there’s an actual full post embeddings table in the database that I distribute for this course. But, uh, I use one called underscore demo so I can show you what filling, what backfilling, uh, vectors looks like, or embeddings looks like.

So, uh, this table is pretty simple. We’ve got an ID column. Um, we, we mostly, I mostly have this here because in later, um, modules, I guess we’ll call them.

They’re still YouTube videos, but in the, in the real course, they’re modules. So very, very fancy. Uh, we, we, we do explore vector indexes.

And one requirement for vector indexes is to have an ID, ID, integer column is the prime, is the clustered primary key for the table. Otherwise you, you cannot create a vector index. You get all sorts of errors and you can’t have a big int.

Can you imagine? You can’t have a damn 2026 and you can’t have a big int. We get kind of, who designed this? Oh yeah.

God. Anyway, uh, we’ve got some other stuff going on here. Um, you know, just sort of normal things, uh, as well as a fabulous foreign key, uh, that points back to the post table. Uh, for, you know, referential integrity and all that good stuff.

So here is our vector column up here, right? Here is our embedding column and is going to be a vector 1024 float. The reason it’s going to be a vector 1024 float is because of the embedding model that I am using currently for, um, uh, for, uh, generating embeddings, uh, based on, uh, the stuff that we’re generating embeddings for, right?

So, um, what we’re going to do is look at what generating embeddings will look like. Right? So we’re going to use this demo table to do that.

And what we’re going to start with is, uh, embedding the titles, right? So we’re going to start taking the text in these titles and turning them into those crazy arrays of floating point numbers, uh, enclosed in square brackets that mean things to computers, right? That mean things to the AIs that allow them to say, is this similar?

Hmm. Hmm. Let me see which direction this arrow points in. So, uh, that’s what we’re going to be doing, right? And to do that, we are going to use, uh, the wonderful and fabulous Ollama, uh, because it is free and it is easy.

And one reason why it is particularly good for this course material is because, A, it’s free and easy, but also, um, if you were to use something else like OpenAI or whatever, like if you’re in like Azure or something and you’re like, I want to use OpenAI. Let’s line Sam’s pockets with more crap. He needs another, he needs four more Lamborghinis.

I don’t know. Uh, you could, you could use that, but then we would all, you would have to pay per API call, right? You don’t have to do that with the local thing. So what we’re going to do is, uh, we’re going to go look at running a Python script to do the embedding.

Now there are, there are ways to generate embeddings locally in SQL Server that we could like, like, we’ll, we’ll get to where like, you know, you can, you can say, Hey, uh, I’m going to use an embedding model. Uh, and I’m going to, you know, call the AI generate embeddings thing. And with that model, and you could do that here, but it’s real slow.

All right. Like it’s like, like absurdly slow to do that, like within SQL Server. So, but I’ve got a little Python script, I don’t know, year 2026, Erik Darling with a Python script.

Who would have guessed it? At least it’s not PowerShell. You can’t get me on that one. I never said I hated Python.

It is sort of like coding with crayons, but you know, at least it’s not PowerShell. So I’ve got my, my VM over here and I have, um, did I install zoom it on the VM? No, I didn’t.

That’s okay. It doesn’t matter much. Um, we have this over here is the Olama server, right? So you, you, you can see right there, it says Olama serve. So this is where Olama is serving stuff from. And I think Olama is sending me, uh, some, some subliminal messages, but I, I just, I’m just too stupid to understand them.

What does it all mean? I don’t know, but this is, this window is not, not, not so important, right? This, this is not the important window.

The important window is kind of over here. So I’ve got, um, this is the Olama version that I’m currently running. And this is the model that I pulled down the MX BAI Embed Large. The MX BAI Embed Large is a pretty good model, right?

It’s a stunning model. Great on the runway. Uh, and it generates, uh, 1024 dimension embeddings. So, uh, that is why we have chosen vector 1024 for our column, for our vector column.

And SQL Server. So, uh, if we run this and we say Olama run MX BAI Embed Large, hello world. And we let that hang out for a second.

You’ll see that all sorts of stuff happened over in our Olama server window. I don’t know what any of this means. This is, I mean, lunacy, right? We’ve got, I don’t know, Bert. I don’t know who Bert is.

Uh, we generate 1024, uh, things. And you can see that our context length is 512 tokens right there. And, you know, so, you know, that like the part of the reason why I was griping about like the, the stack overflow bodies, right? Is because they’re long and you run out of those kind of quick and we got to think about chunking our bodies.

Uh, I’ve had enough body chunks, man. Anyway. Uh, when all this stuff runs and does its, you know, fancy AI things to, to the, to the help to hello world. Hello world turns into this, right?

Look at all these numbers. There are, I guarantee you, if you counted them, if you pause the video and you count as I scroll through, there will be 1024 of them. And these 1024 floating point numbers, uh, apparently describe hello world to, uh, to, to, to computers in a way where it can understand if something else is similar to hello world.

Magic, right? Absolute magic. So, uh, we’ve got our Python script over here and our Python script is going to, uh, call Olama, much like we called Olama for a single thing there.

And it is going to, uh, fill 1,000 rows in the post embedding demo table with, uh, with embeddings just like that. Right. Just, just like this, they’re all going to end up in there. So let’s, let’s run this thing and let’s see.

We are fetching up to a thousand questions without embeddings. Uh, and now Olama is real busy over here. Right.

Yeah. Uh, embeddings require, but some input tokens are not marked as outputs. Yeah. Look at it go racing along and we’ve got, well, you see, even Olama is not incredibly fast with this. Right.

You know, like, uh, like doing it in SQL Server is pretty, like, I wouldn’t do it for more than like, you know, maybe like five, 10 rows in a batch. Olama, we can, we can bump things up a little bit, but you know, uh, it, it, it moves along. It’s, it moves along a lot faster than SQL Server does.

So you can see, uh, our, our thing has finished. Uh, we have embedded 1000 rows. We have no errors and it took about 40 seconds for a thousand rows. So keep this, I mean, and like, you know, this is maybe not the grandest VM in the world, but just kind of keep these speeds in mind when you’re thinking about like backfilling your data with stuff.

Uh, you know, if, if you were an actual purchasing person of the course, you would get the Python script that does this. So I don’t know, maybe that would be useful to you. Then again, with, with AI being what it is, you can probably just tell it to make a Python script to do that for you.

But yeah, mine’s pretty good. I’ve added some stuff to mine. I’ve zhuzhed mine up a bit.

So, uh, now if we go look at our embeddings table, right? We have, uh, let’s see. I mean, there are, yeah, um, thousand rows in there, right? Thousand rows of embeddings.

And, uh, when we look at what we’ve got in here, we have our, uh, you know, stuff from the post table and stuff from the post embeddings table. This is just a small preview of what ended up in there, right? So all those floating point numbers.

So if we want to know how to horizontally center a div and another div, then this is what the, some of the, the, the floating point numbers that describe how to div a div and a div a div would look like to a computer. And we can see some information about our embeddings, uh, via sys.columns, right? We have a vector dimensions and a vector base type description column, which tell us how many dimensions our column is 1024, which we knew when we created the table and the base type is float 32.

This might be more useful to you if you were dealing with a system where, um, you were unfamiliar with their current vectorization of things. Anyway, uh, we also have a couple new functions in SQL Server that SSMS is pretty damn lazy on. Uh, here I am using SSMS 22.

Um, granted it’s not the newest 22.1 yet cause I’ve been busy and I can’t, I can’t upgrade SSMS every two minutes, but, uh, this vector property function is completely unrecognized by SSMS, but we can still get valid results back from it. We still get, we still get results. I promise.

Now, if we look a little bit at what the size of things look like, and hopefully I highlighted that from the correct point. Um, we have 1000 rows, uh, rather we have 1 million rows in our, in our table. Uh, well, sorry, let me back up a little bit here.

Uh, this is, I’m looking at the real post embeddings table. Now I’m not looking at the demo table with just a thousand rows in it. Okay. So like, this is, this is the actual table that I’ve already gone through and embedded everything in like all million embeddings. Right. So this is not just the demo table with a thousand rows.

And let me clarify that before I make SQL Server look worse than it is. So in our actual, uh, post embeddings table, we have a little over a million rows and, uh, are the size of our embeddings is about 4.2 gigs. Right. And this is just for like the, the titles and stuff. Right.

So this is, you know, pretty big. Um, it’s, you have to do a lot of interesting size forecasting. If you want to add this to a database, cause, um, it’s a lot going on.

Anyway, we’ll talk more about that later, but, uh, we, we, that, that gives us about, uh, about 4 megs per 1000 rows with the float 32, uh, and vector type. So, you know, we do have to think a little bit about, uh, how we’re gonna, how we’re, what, what, what data is really meaningful to embed because, um, you know, it takes up a lot of space.

Well, I don’t know, it’s a lot of space. No wonder storage vendors are so excited about, about vector data types. Cause, uh, sell a lot of diskettes. Anyway, that’s probably good for this one.

Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And, uh, again, this, this, this, the entirety of this course is available for sale and purchase at my site here. You will find the link, this full link down in the video description.

If you would like to get a hundred bucks off, uh, you can do that. And you can Get AI Ready with Erik. Alright, thank you for watching.

Going Further

SQL Server Performance Office Hours Episode 48

Posted on February 16, 2026January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 48

Hi Erik, Happy New Year! On my SQL Server production system, the top waits include CMEMTHREAD and NUMA waits, accumulating a few hours of wait time. I couldn’t find much information online, but I believe that this may be related to parallelism or bad MAXDOP settings. What should I check next? My MAXDOP and CTFP are set to reasonable defaults, and I want to determine if these are causing unseen background issues.

Hi Erik, Happy New Year! On my SQL Server production system, I see that the VDI_CLIENT_OTHER wait is at the top of the wait list. Is this connected to slow AGs? Can I ignore it safely?

We increased MAXDOP from 4 to 8 and the query stopped spilling to tempdb and actually ran faster. Isn’t more parallelism supposed to make things worse?

We replaced a CTE with a temp table and the query got faster, so CTEs must be inherently slower. Should we avoid CTEs for performance?

We captured the execution plan when the query was fast and when it was slow — they’re identical. Since the plan didn’t change, this can’t be a query problem, right?

To ask your questions, head over here.

Video Summary

In this video, I dive into some interesting SQL Server questions submitted by the community. Erik Darling from Darling Data shares insights on topics ranging from CMEM thread weights and trace flag 8048 to the performance implications of parallelism and CTEs versus temp tables. The discussion is both technical and practical, offering real-world advice for managing SQL Server environments. Whether you’re dealing with AG-related issues or optimizing query plans, there’s plenty to learn that can help improve your database management strategies. I also explore the nuances of parameter sensitivity and data distribution, providing a deeper understanding of how these factors impact query performance. By sharing this knowledge, my goal is to empower SQL Server administrators and developers to make informed decisions about their workloads and configurations.

Full Transcript

Erik Darling here, Darling Data, home of the most reasonable rates on the internet. It is Monday, unfortunately, and I apologize in advance, but at least on this Monday you get some office hours. You get to hear me answer five community submitted questions. If I ever get a palm tattoo, that’ll be, maybe I’ll get the number five tattooed on my phone. I don’t know. Some Roman numerals. It’s a binary for five or something. I don’t know. Something dorky, right? Five user community submitted questions to me, yours truly, about SQL Server or whatever. I do say it’s SQL Server questions, but people have asked me about things that I never dreamed anyone would care. ask me about. So, that out of the way. Down in the video description, you’ll see all sorts of helpful links. If you haven’t clicked on these links in a while or ever, you might be surprised to see that I have redone a lot of things in there. There are all sorts of updated consulting packages, some of them at lower price points for those of you who need different types of consulting. There are different types of consulting than things that a typical bucket of hours might cover. Down in the training section, too, there have been some updates there. I have bundled, I have re-bundled the everything bundle to actually be all of my SQL Server performance content into one helpful package. And there are now, you now have the ability to buy that with a subscription. So, you can choose a yearly subscription or a monthly subscription.

If you want to check out that training. You can also, for as few as four US American dollars a month, throw a little support towards this YouTube channel for all of the incredibly high quality content that I provide here. And you can also find the link to ask me office hours questions and of course, the usual shindig where we like subscribe and we tell our friends about the wonder and glory of the darling. And the new day to YouTube channel. I will be leaving the house quite a bit. There are going to be well between one and three more of these showing up in the near future. I will be performing my I’ll be prosecuting my my advanced T-SQL training at all three of these events. I have pre cons at all of these data to Nashville data Saturday Chicago and SQL day Poland. starting in March of 2020.

So, I will be there dressed festively, you know, festooned to the gills and all sorts of things. Poland might be the first place where my all over Adidas gear is finally appreciated. So, see about that.

But anyway, it is, I mean, I don’t, I don’t know. I think, you know, like it, New Year’s starting to wear off a little bit. I’m not really, I don’t know if I’m feeling the 2026 file.

So, I’m going to figure out something new for this. I got to figure out a good background image here. Anyway, let’s get out of this.

And let’s go over to our wonderful Excel file. Our Excel file of hopes and dreams and wonders. Where we will attempt to give the people what they desire. Anyway, here we go.

Let’s put up our wonderful zoom it highlighting here. Our very highly accurate zoom it highlighting. Hi, Eric. Happy New Year. Happy New Year to you too. On my SQL Server production system, the top weights include CMEM threaded NUMA weights.

Accumulating a few hours of wait time. Your top weights have a few hours of wait time? There’s a lot of questions I have here.

Like, I mean, obviously, I mean, I don’t know. You’re looking to a server startup, but how long has your server been up where a few hours is concerning? You need to ask me about it.

Anyway, I couldn’t find much information online. That’s weird because I’ve posted about CMEM thread weights. That’s a weird one.

But I believe this may be related to parallelism wrong or bad maxed up wrong. What should I check next? Well, good question. So, trace lag 8048 can be helpful in reducing CMEM threaded NUMA weights.

Way back in SQL Server 2016, Microsoft had this series of blog posts called It Just Runs Faster.

And, you know, some of them were like, all right, cool, yeah, it does run faster. But other ones, like there was one about DVCC CheckDB running faster. And everyone was like, how’d they do it? And they were like, we skip a bunch of checks now.

And you’re like, oh, okay. Oh, that’s not cool. So, you know, how do you run faster? Well, I got in a car. Like, I took a bus and a train.

That’s why I ran faster. So, but there was one of these It Just Runs Faster posts was, so like back in the old, like pre-2016, if you had like more than eight CPUs or something, and you ran into this CMEM thread weight, there was a, I’ll talk about what CMEM thread is in a moment.

There’s a trace flag called 8048. You can enable it globally, like now on your server, but the most effective way of turning it on is when the server starts up, because there’s a whole bunch of memory stuff that is already sort of allocated and accumulated that 8048, to turning 8048 on in flight won’t help with.

So if you make it a startup trace flag, it affects way more things. But the whole, the reason, like CMEM thread is effectively like, like memory structures being, and like having like exclusive latches taken on them, and other things trying to take latches can’t get latches on things.

So CMEM thread is sort of like a memory safety weight. So it’s not really to do with parallelism. You’ll most, I see a lot of, there’s been a lot of stuff around it with sort of like the plan cache and query store.

And it’s essentially just like, you know, like, like just think like, like in the context of the plan cache, just think of it like, like something’s putting a plan in the cache and other things are trying to figure out if a plan is in the cache and stuff like that.

So it’s like the whole mess of latches. So you could try trace flag 8048 to see if that alleviates it, regardless of if you are on 2016 or newer.

The whole deal with 2016 was that like, like if you hit certain like thresholds, or you like reached a certain level of like contention, then SQL Server would try to sort of like on the fly, change the way that memory is partitioned from like a NUMA node to a CPU, so that there was less contention between like different things, different accessors of things in memory.

So like, like it kind of worked. But if you’ll just like keep like approaching the threshold or keep like approaching like the contention that it would worry about, then you never actually get there, then this like the it just runs faster apart doesn’t like kick in.

So what you really have to do is, or rather the easiest thing to do is just in your hopefully, which you’ll hopefully have access to SQL Server settings.

If you go into like the SQL Configuration Manager, add minus T8048 as a startup trace flag, reboot your SQL Server whenever you feel like, because, you know, who cares?

And you’ll be good from there, or you’ll be better from there. But, you know, I mean, C-Mem Thread and NUMA, like you really have to sort of be hitting quite a bit of that.

There were some spin locks at one point that made sense to look at, but I’ll be damned if I can remember those hieroglyphics off the top of my head. But, yeah, yeah, trace flag 8048 should straighten you out.

But, you know, what you’ve presented me with here are, you know, is this like a question that I can generally answer?

I can’t tell you if turning on trace flag 8048 and alleviating some or any of the C-Mem Thread weights that you’re currently happening, currently experiencing rather, will like massively improve your SQL Server performance experience.

It might make you feel better, right? It might be like, you know, like a CX packet thing where people are like, I changed MacStop to one.

CX packet’s gone. Oh, I can sleep at night. Like, I don’t know if it’s going to like make your life better. That’s that, you know, if you really want to get rid of it, just wanted to know some SQL Jeopardy about stuff, there you go.

But, I don’t know if it’s going to make your life better there. I don’t know if it’s really going to improve anything. Let’s see here. Oh, we got another happy new year. Oh, we got two happy new years.

Hi, Eric. Happy new year. On my SQL Server production system, I see that the VDI client Otherweight is at the top of the wait list. Is this connected to slow AGs? Can I ignore it safely?

I have never seen that thing matter. I can’t for the life of me figure out why that would matter. I think, like, I guess it could be like slow AGs. And that, like, if you, like, I remember maybe it being connected to, like, the initial direct seeding of databases.

So, like, again, go out, go really going back in time machine episode. 2016 introduced a feature for AGs called direct seeding, where, like, anytime you added a database to SQL Server, it would be like, hey, new database.

You’re going in the AG. Now we’re just going to take a backup single thread and throw you on the other server. And for big databases, that could not be fun. But, so, like, I guess maybe for, like, direct seeding could be related, but just, like, general AG activity, no.

I would ignore that one, unless you are actively direct seeding databases. And then maybe, maybe not. Okay.

Let’s see here. We increased max stop from 4 to 8, and the query stopped spilling to tempDB and actually ran faster. Isn’t more parallelism supposed to make things worse? I mean, all right.

So, it’s a fair question. Isn’t more parallelism, you know, for a single query, there is usually, like, a point where there is a limited return on investment for more parallelism, and certainly a point where there would be no more return on investment for increased parallelism.

The point at which you hit that in your max stop setting, or max stop hint for the query, rather, will vary from query to query. You know, if the more, the more rows your query processes per thread, probably the more it slows down, kind of.

So, you know, if you have more threads available to process rows and break up those units of work into smaller bits, you will, you will, you will, it will eventually hit a point where you find, like, the perfect top for every query, but, you know, more parallelism is not generally bad or worse.

You do have, I mean, more parallelism could be generally bad for, like, the workload. If you were just like, you know what, every query max stop zero, let’s just see what happens.

Like, that wouldn’t, that’s probably not a great idea. But, yeah, you know, these are the kind of things that I like experimenting with, because, you know, sometimes you’ll find, you know, a little sweet spot for max stop when you’re working on this stuff.

But, no, in general, more parallelism won’t make things worse. And you may find that in your case, the reason why you stopped spilling to 10 dB was maybe the memory grant was a little bit more effective per thread there.

Maybe you had some imbalanced parallelism where one or more of the threads were getting way more rows than the other threads. Because, of course, when SQL Server, every query plan starts off as a serial plan.

It’s only if your query meets the cost threshold for parallelism that parallel plans are explored. And, of course, if SQL Server finds a cheaper alternative parallel plan, then it will use that, right?

But what happens is the memory grant for a query is assigned to the serial plan. And the serial plan will split up that memory grant amongst your dot threads if a parallel alternative is chosen.

So, it is entirely possible that the reason you stopped spilling to 10 dB was because the row distribution across threads got better and the memory grant per thread was more effective.

So, that is most likely the explanation for why it stopped spilling to 10 dB. But to answer your second question, no, more parallelism doesn’t make things generally worse for a query.

You may find that it does not improve things as much as you would hope, but, like, past a certain point, like if you went from 8 to 16 or something.

But, you know, that’s probably good enough there. All right. We replaced a CTEwith a temp table and the query got faster.

Many such cases. So, CTEmust be inherently slower. Should we avoid CTE for performance? Well, I mean… It’s an interesting question for me.

I generally dislike CTE because too many people like them. That’s really…

But CTE in SQL Server have limitations or rather a lack of featurehood in them compared to other databases.

But let’s see. How far do I want to go on this? Let’s see. All right. We’re going to go all the way. So, yeah. So, generally, like if, you know, a CTE is not materialized in any way in SQL Server.

So, dumping data into a temp table, you get a materialized result set and the optimizer can make generally much better choices off a materialized result set than whatever query was in the CTE and sort of inlining that into the remainder of the query.

Especially if things get quite large and complex, then the optimizer tends to break down in all the usual places. So, you know, replacing a CTEwith a temp table can in many, many, many, many, many cases get you improved query performance.

But if the query performance is fine with it, then there’s no need to go tinkering with things. I used to think that it would be great if Microsoft would give us the option to materialize CTE.

But right now, the only facility available for materializing CTE would be a spool. And spools in SQL Server are a complete nightmare. So, you know, we don’t want to have any more spooling in our query plans than we currently do.

And so, what my thinking is that materialized CTE in SQL Server would be pretty crappy unless Microsoft were to update the code for spools beyond its current state, which hasn’t changed in SQL Server 7.

But of course, Microsoft is busy providing us with groundbreaking or just breaking products like Fabric. It actually hasn’t broken any ground, but sure has broke a lot.

So… Yeah. All right. Hmm.

Hmm. Hmm. We captured the execution plan when the query was fast and when it was slow. They’re identical. Since the plan didn’t change, this can’t be a query problem, right?

Well, I mean, you know, you’re… Let’s see. You’re maybe right that it’s not a query problem, but what it most likely is is a parameter sensitivity problem or if you want to think about it slightly differently, it’d be a data distribution problem.

All right. Because your query ran fast when it had to process, let’s say, this amount of rows and then…

Actually, let’s say this amount of rows. And then your query ran slow when you had to process this amount of rows. Now, this two could be literally two. Could be 200. Could be 2,000.

And this five, well, this five could be 50,000 or 500,000 or 5 million rows, right? So what you have is a parameter sensitivity issue. What you might want to try doing, just to see it, just to make sure…

Like, I’m not saying this is your long-term fix. Of course, there are many ways to fix a parameter sensitivity issue or a data distribution skew issue. But you could try running your query with a recompile hint and seeing if, like, the plan changes and seeing if some of those plans capture lots and lots of rows and some of them capture very few rows.

It could also not be that, right? It could also be, like, the query got blocked when it ran one time, right? Because if you’re looking at wall clock time and you’re like, oh, that query took, like, one millisecond and you’re like, oh, that query took 10 seconds, but the query actually only used, like, you know, that one millisecond of CPU time or something, right?

And something else got in the way, right? So it could be blocking. It could have been some other resource that was under contention at the time when it ran. You know, it could have been CPUs overloaded. It could have been memory grants overloaded.

It could be that the data was in memory the first time it ran and not in memory another time when it ran and it slowed down because you had to read a bunch of data from disk into memory. So, you know, the execution plan not changing.

I mean, you know, everyone loves a nice stable execution plan, right? Yeah. But, you know, it could be lots of stuff that was different at the time that the query ran when it was slow.

It is your job to figure out what was different, whether it was something within the query, like the number of rows that the query had to process, or something external to the query, like the query was blocked or the query was under some other type of resource contention.

So, there we have that. Anyway, that is, let’s just make sure here, one, two, three, four, five questions asked and answered.

That will wrap up this office hours. Wow, nearly 20 minutes. That’s a good one. That’s a good office hours. There was lots to say, I guess.

I hope I didn’t repeat myself too much. All right, let’s go. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something tomorrow. And for the remainder of this week, we will be back to talking about the vector-y things.

We have some interesting new stuff to talk about. We have some new material. We will be doing some groundbreaking material, unlike Microsoft working on fabric.

So.

Going Further

Get AI-Ready With Erik: A Little More About Vector Types

Posted on February 13, 2026January 23, 2026 by Erik Darling

Get AI-Ready With Erik: A Little More About Vector Types

Video Summary

In this video, I delve into the new vector types introduced in SQL Server 2025, focusing on their practical applications and limitations. I explore how these vector data types, particularly float32 and the preview-only float16, can be utilized for embedding models and similarity searches, while also highlighting the importance of consistent data type usage to avoid performance issues and bugs. The video covers key points such as the configuration settings required to access these features, the differences in precision between float32 and float16, and the current limitations on operations that can be performed with vector types. Additionally, I provide a detailed explanation of why Microsoft’s preview feature flag might be less than ideal for developers eager to experiment with new technologies.

Full Transcript

Erik Darling here with Darling Data. And today we’re going to look a little bit more closely at the new vector types in SQL Server 2025. This material and far more is all available in my new Get AI Ready with Erik course. The link you see up there will get you a hundred bucks off the course. That link is down in the video description. You could also pause the video, zoom in and type that whole thing out. But the main point is if you go to training.erikdarling.com and use the coupon code AIREADY, you can get the course for a little bit cheaper. But the vector data type, at least at present, is a vector of n, where n is the number of dimensions that will be generated by your embedding model. For right now, the generally available, fully supported for production usage is a float32. the data type will look like this. The data type will look like this. The 1024 just assumes that you’ll be using 1024 dimensions. It’s not like the limit or the only number you can use. You can generate. You can use a vector of any length, right? Just don’t mix them. There is a minimum of one dimension for float32 and there is a maximum of 1,998. Float32 is four bytes per dimension. Float, there is also a float16, but the float16 is preview only.

If it looks like this, you would have to put float16 as the data type precision there. Again, minimum of one dimension, but this one doubles the number of dimensions you can have from 1998 to 3,996. And this is only two bytes per dimension. Now, with anything, you know, always be specific and consistent about data types. The number of stupid bugs and performance issues and things breaking I’ve seen crop up over the years because of type sloppiness is bordering on absurd. Do not become a sloppy statistic. This is not just limited to vector types. This is limited. This is expanded, right? This is limitless across all data types. Even something as simple as matching Unicode and non-unicode, like making sure your strings are the correct type across everything, that your dates and date times and date time twos all match up correctly is very, very important.

So, Microsoft has this new database scope configuration called preview features where you can say, hey, SQL Server, I want to see the new stuff. Normally, these things would be hidden behind trace flags that you could turn on and off. And then you would get like a trace flag per feature. I don’t love this. And the reason I don’t love this is because when you do this, you get all the preview features. And if you’re using a preview feature and say another, like a cumulative update comes out and like your feature is not generally available yet, you have to leave preview features on and you might get a new feature that’s a preview feature that you don’t want.

And you’re like, no, this thing is messed me up, right? Like I personally think this is kind of annoying, a little scary, but who knows? Maybe Microsoft is committed to not adding preview stuff in. I’m not nuts about this, but if we want to see the float 16 vector type, we do have to use this. This also is necessary for a couple other things in the vector world, like the vector search function, right? We can use the vector distance function. That’s generally available. That’s everywhere in 2025, etc.

But the vector search and vector indexes are not. So we will have to use this one to see any of that stuff for later. But, you know, we can generate a float 32 with the maximum vector size, which for this is not going to look very interesting because I was lazy and I just generated the number 1.0 over and over again. You know, don’t, don’t hold, don’t, don’t, please don’t yell at me. It was just, I just wrote this to do it. I’m not meant to be anything interesting.

Or at least not meant to be anything like, you know, like an actual vector that you would see in the real world. But the, the float 16, actually something that I do want to bring up here is I’m going to leave this open and then I’m going to come back and we’re going to look at this for float 16. Now, keep in mind that I am generating the same 0.1 for all of these, right?

Except just different numbers of them because float 16, I can, I can generate way more of them. But when I run this, right, this will have the same number of bytes, but notice that these numbers are different. So the, the first one, 1.0 is stored pretty much exactly as 1.0, just as a, you know, 1.0 as a float.

For this, it’s 9.99. See, there’s a little negative here. That means it’s not 9.9. It’s actually like 0.9 something. If you were to convert this to like an actual, like the decimal or something, it would be a little bit more obvious. But for, you know, like for this one, like it’s, it’s going under, like it’s not 9.9. It’s like 0.9, whatever.

This is because when you, when you’re representing numbers with two date, two bytes versus four bytes, then you lose a little bit of precision. So like the value 0.1 can’t be stored exactly in binary, just like, you know, one third can’t be written exactly in decimal as 0.3 forever threes.

Float 16 uses fewer bits to store the number. So the approximation of 0.1 is just slightly less accurate than float 32. So, but like values of 0.5 or 0.25 would be, would be stored exactly in both formats. But for similarity search, right, for the cosine similarity search that we largely care about, right?

Like that’s the stuff that we, that we’re mostly going to be doing with our data. Those tiny differences don’t really affect results. Like I did go through an exercise with like, like going through the course with float 32 and float 16.

And there was like really like hardly any difference in any of the results. So like the loss of precision doesn’t really hurt cosine similarity search. So don’t worry too much about that. But again, float 16 is still a preview feature.

So you might not see that, you know, or you might, you might not ever use that in production for a long time. Cause who knows, like micro, like Microsoft has made like roadmaps for like management studio and for fabric. But you know, for like SQL Server stuff, they’re like, you’ll get it when you get it.

There’s no roadmap. You’ll just, you’ll just have to wait and see dummy. So, you know, real nice. Anyway, these things do have hard limits, right?

So if we try to go over, we will get errors and SQL Server will yell at us and say, you cannot, you cannot go past those limits. Diffent, diff-er, and float precisions.

So if we were to, so like another, like, like, like earlier, a couple of videos ago, I said that like when there was a float three and a float four, we couldn’t compare those using the vector distance function.

Same deal, at least at present with float 16 and float 32. So like, even if we have two vector types with the same number of dimensions in them, SQL Server is like, well, float 16 and float 32, you are just too different from me.

I can’t figure you out, right? Two bytes, four bytes, what are we doing here? So, you know, whatever you, whatever you choose, which will probably be float 32 because it’s generally available now.

If you decide you want to change to float 16 later, because it’s like float 32 is just like way too much storage space. Good luck.

Good luck. But there’s a lot of stuff you can’t do with the vector data types. And a lot of ways are very, very similar to like XML and JSON, where like, like, like there’s just like a lot of things that just don’t work, right?

Like you can’t like group by, order by, like do direct equality comparison, stuff like that. Like if I, like if I declare two vector types here, which match in every conceivable way.

And I just say, Hey, if you’re one is equal to the other, like just say yes. SQL Server is like, uh, huh? No, no can do. Sorry.

Like this just ain’t happening for you today. Right? So, Oh, you know what? That blew up both of these. Gosh, darn it. Knew that looked silly. But no comparisons, just like with XML, right? Like if you like, if you were like, try to do something similar to that with XML, like no, right?

Group by XML. What? Out of your mind. But you can just test to see if it’s null, right? So if we have two vectors and one of them is null and one of them has some numbers in it, SQL Server is like, yeah, fine.

We can figure out pretty easily if, you know, we’re allowed to do that. So that, that at least the null checks do work, but there’s a lot of stuff like query wise that you can’t do with them, which isn’t good, which isn’t like a big deal mostly, right?

Like you can’t do, you can like equal less than, greater than comparators. You can’t concatenate or like, you know, like add two vectors together. You can’t like sum or min or max a vector, which what would that look like anyway?

Again, you can’t group by or order by. And they have, and they also have very limited sort of constraint support, at least at present. I don’t know if any of this stuff might change. Like there’s some stuff that seems a little weird.

Like, like you are allowed to use null and not null constraints on them, right? You’re allowed to say if a vector column will allow no marks in it, but like you couldn’t have like a default value. So like if you just wanted to say, like throw like a dummy vector of like, like say you had a vector three column and you just wanted a default constraint of like 0.000 or something, right?

Just like three zeros. You can’t do that. You also can’t have any check constraints on them. I don’t know what you would check in a vector, but also not allowed.

You know, no primary keys, no foreign keys, no unique constraints, all sort of understandable stuff. You can use them in columnstore indexes. My dear, dear friend Nico had a blog post about, about that, I guess, a week or so ago.

But, you know, that’s, that’s, that’s in the course material. So I didn’t steal that from him. Probably never, never steal from Nico. I probably might steal, he had some nice hats.

I might steal one of his hats someday, but I don’t know, I feel bad about it. You can’t, you can’t have them in columnstore indexes. You can’t have them in, include columns for rowstore indexes. And you can, you can have more than one vector column in a table, which sounds like a nice sort of like, cheat for something maybe.

But what’s, but like, like one thing that I want, like I always love figuring out when these new things come out is sort of like, like how much like feature coverage do they have for other things?

So like right now we have the vector distance function in SQL Server, where we can see how similar, or depending on like what metric we’re using, we can use cosine, you could dot, whatever.

But like, you know, for us mostly cosine. So like if we wanted to have two vector columns and a table and figure out how similar those are in like with a computed column, the vector distance function is at least now not deterministic.

So we couldn’t persist that computed column. We can index that computed column. The vector search, vector, vector distance function would have to run for that every time we looked at it. So it’s like, it’s kind of annoying, right?

Like, like, I don’t know who wrote the spec on this thing, but they, I don’t know, maybe, maybe didn’t think a lot of stuff through. So, but that’s sort of typical of SQL Server these days. Anyway, that was a little bit more about the vector data type, sort of some of its limitations, what you can and can’t do with it.

A little bit about float 32 versus float 16. Again, float 32 is generally available and float 16 is preview only at this point. And of course my trepidations about the free, the preview features flag.

So got some stuff there. Anyway, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. Hope you buy the course and I will see you over in, well, today’s, today’s Friday, isn’t it?

Yeah. So I will see you Monday for office hours. Look at us go. All right. We’re really back in the swing of things here. All right. Goodbye.