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.
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.