Get AI-Ready With Erik: Vector Search Function, Nice Someday
Summary
In this video, I delve into the fascinating world of vector search functions in SQL Server, specifically focusing on how they can transform your search queries by making them more efficient and accurate. I break down the intricacies of using the vector search function, which returns a single column called distance, and explain its unusual syntax and functionality. By walking through a practical example with a full post embeddings table, I demonstrate how to use this function effectively while highlighting its quirks, such as the odd behavior when aliasing tables and the necessity for a top number of rows to be specified. Additionally, I compare vector search against vector distance, discussing their performance differences and providing insights into optimizing your queries for better results.
Topics
`SQL Server`, `vector search function`, `table-valued function`, `distance column`, `cosine metric`, `vector index`, `query plan`, `full post embeddings table`, `cross-apply`, `embedding tables`, `foreign key`, `column store indexes`, `vector distance function`, `filtered searches`, `SQL Server preview feature`, `exact search`, `course recommendation`
Chapters
- *00:00:00* – Introduction
- *00:01:30* – Vector Search Function Basics
- *00:04:07* – Query Plan and Performance Comparison
- *00:05:31* – Filters After Vector Search
- *00:06:21* – Top Number Considerations
Full Transcript
Erik Darling here with Darling Data, and continuing on with more vector-y nonsense, I’m going to talk about why the vector search function will someday be able to make your search queries suck a little bit less. So the vector search function is a table-valued function that returns one column called distance. The inside of the function is very, very strange, at least syntactically to me, because you can’t just use like position, like at least I haven’t been able to get it to work with like positional stuff. But you have a table that equals, you know, whatever table you want to hit. You can alias that table here and then use columns from the table in here outside of the vector search function, you know, cross-apply-ish. Then we have the column in this table, right, that has our, that is a vector data type that has our vector index on it. We have the original vector that we want to compare and find things that are, that are similar to it similar to it. And then we tell it which metric to use, which, you know, because we’re doing all similarity search, we want cosine. And then we have to give it a top number of rows to pull out. I’m going to talk a little bit more about this at the end of the video, but this is, this is an odd, this is an odd duck to me. So this is like sort of the basic breakdown of it. Now, what we’re going to do is just search a little bit and just kind of look at a query plan for one, using the vector search function against our, our full post embeddings table, just to find 10 rows, right?
So like, obviously this, you know, stuff is all in there. It’s kind of weird. Is it like, you can’t alias this, right? And if you say like PE dot embedding, uh, it’s like, nah, I don’t know you, right? Like what? It’s kind of, it’s a little bit odd that that’s a, that’s a thing there, but you know, what can you do? Oh, green screen. Never knew you cared. So, uh, we run this query. We will see our vector index in use. And you can see that you, you, you, you, you can actually seek into a vector index, which is, is going to be great. Um, what’s kind of weird is that you will, you always get a one row guess out of the vector index, which is maybe not great. Like I’ve, I’ve messed around with this and it’s always one row and you get this new vector index seek, uh, icon, which looks a little bit like someone sitting down doing something lewd, but I don’t know. That’s maybe that’s just my Rorschach test, but, uh, the only column that you get back is the added VS dot star. And this is aliased as VS. The only column you get back is this distance column from there. If you want other columns, you have to either, they have to either be in the table.
You store your post and bet your, where your embeddings are, or like you have to join off to another table. So for example, if we wanted to get information from the actual post table, uh, for like rows that the po like matched with the post embeddings table, we could join like the, again, the syntax is very odd. You can join like the table that you reference in here outside over here. I mean, it’s kind of like cross supply ish, but you know, it’s just like an odd thing to see. So now we can get like base table data out and also, you know, search for similar embeddings at the same time. So it does take a little bit more there again, like, like, like to start your, you’re going to want, not because of the vector index thing, but to start, you’re probably going to want to keep your embeddings in a different table, uh, with like a foreign key that like, you know, like maintains the primary key from whatever table your text data lives in.
Cause you don’t, you’re not, you’re not really don’t want to blow it out your main table with all the embedding stuff. It’s pretty big right now. And there’s not really like a great compression story for it, especially if it’s like all in like, you know, some historical legacy base table, um, like new tables, you could create columnstore indexes on, but like old ones, it’s not good. So let’s just do a brief comparison here between, um, vector distance and vector search. All right. So we’ll run these two queries and we’ll get some timing from them because timing is important, right? So the vector search, uh, just for a small table takes about a second right here. And the vector search, uh, is about 16 milliseconds. And of course, you know, that’s, that’s exactly what sort of, that’s sort of exactly what you would expect because being able to seek into an index, even if it’s a vector index, it takes a long time to create only ever gives you a one row estimate and, um, you know, makes your table read only.
At least it still gives you a faster query. Whereas with, with vector distance, vector distance, you don’t get that. What’s interesting. I think about this query plan is like, I expected to see like a filter operator way later in the query plan for the, for the vector distance. But this one, it shows up at the nested loops join, right? Like this is where we do our filtering.
Cause I, I, I had stuck the vector index in a where clause up here. Uh, there we go. All right. So it was just like where vector distance is less than 0.2. And, and like, just, just to have a number in there. And I was like, ah, where’s the filter operator, but no, there it is sitting at the nested loops join, like a big weirdo.
So, I mean, I guess that’s, that’s, that’s better than, you know, a lot of other things. It might, uh, result in the late filter in SQL Server, but you know, still, still, still a little funny looking. Um, so one thing to understand about the vector search function though, is that any filters, um, that you might have in like the outer part of the query will apply after vector search does its thing.
Not before, not during, after. Inside vector search, you have to specify the top end number of similar vectors that you want it to return first. And then your where clause might filter those results out.
So if your filter eliminates like a lot of the results that the top end similarity thing finds, you might get way, way fewer rows than you expected back, which might, might be fine for some, some searches, right? That might be totally okay, right? You might be like, give me the top 10, then I got to weed them out a little bit, or give me the top 100, then I got to weed them out a little bit.
Um, but sometimes if you are expecting to like, you know, get back a top 10 or a top five or anything like that, you might need to put a much larger top number in here in order to get those rows out. Right. In order to get that number out.
And it’s, it’s really hard to sort of predict that ahead of time. So you might have to do a lot more work to like, you know, like say I want the top 10. Like I say, I want like the top 5,000 or 10,000 here and then apply whatever search stuff and then still give you this back. Like, I don’t know.
It’s, it’s, it’s a real weird situation in there. But, uh, if you’re using filtered searches, use a larger top end to account for filtering. Um, you know, again, you want to create separate embedding tables by, uh, you know, like by category so that, you know, you can, you know, sort of manage them separately and differently. Or if, you know, you’re, you’re not going to go the vector index route.
If you’re not like in, you know, just like get down with the preview feature sickness, uh, you know, right now you can just use a vector distance function. You can get exact search, but which is, you know, and still, you can still filter on that, but you know, it’s just, it’s just slower because there’s no, like it can’t take advantage of an index. So, anyway, that’s about a good here.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you will buy my wonderful course, Get AI Ready with Erik, and I will see you next time. All right.
Goodbye.
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.