AI in SQL Server 2025: Embeddings

One of the cornerstones of AI is a concept called embeddings. Virtually every AI model, whether for text, video, or audio, uses something related to embeddings. Starting with SQL Server 2025, this is a term you’ll be hearing a lot, as its potential for applications is enormous!

In this post, I want to offer a more didactic explanation of embeddings, especially for those who haven’t had the opportunity to explore the world of AI or machine learning. My hope is that by the end of this post, you’ll finally understand what embeddings are and see the possibilities for use with SQL Server (or any other database with support).

The goal of this post is to move beyond this simple table to this script!

Starting without SQL and without AI

Let’s start with a simple exercise, and for now, we won’t involve AI or SQL. The goal is for you to understand the idea behind the concepts and operations involving embeddings.

Imagine a car… Now, a bus. In your mind, you probably identified standard elements and characteristics of these two words. You imagined some geometric shape, textures, internal and external elements, etc. A series of unique properties you’ve learned throughout your life that define these two concepts.

And both have common characteristics, but in different quantities. For example, you may have imagined a car as a small rectangular geometric shape, and a bus also as a rectangular geometric shape, but larger. And, most likely, when imagining one of these two concepts, you made little or no association with masculine or feminine, which would be very different if I asked you to imagine a queen, for example.

Notice that with each word or phrase we learn, we make some kind of association with other concepts, even unconsciously. That is, we learn to identify characteristics and how much of that characteristic is present.

To use this on a computer, we need a way to transform these relationships into numbers. Let’s try a very simple approach: we’ll take the phrases above and put each one on a line in a table. Then, we’ll map these two characteristics: Vehicle (indicates whether the phrase refers to a vehicle), Person (indicates whether it refers to a person)

Congratulations! These are your first embeddings.

Maybe you have a more elaborate criterion than mine. But, for now, I want to keep things simple, and that’s why I limited myself to identifying as 1 or 0. It’s already enough for our first objective: we managed to represent a phrase in numbers, even if few and simple numbers, but there it is.

Vectors

We can represent this table in a “more mathematical” way: using vectors (or, if you prefer, call them arrays). Each characteristic we have mapped (vehicle and person) is a position in our vector. We name each position a “dimension.” That is, if we have 2 characteristics, then, for each phrase, we need a 2-dimensional vector:

If there were 10 characteristics, there would be 10 dimensions, and so on… These are the embeddings: the vectors that represent each word, phrase, etc. Up to this point, we haven’t used any AI… Just a little human reasoning and mathematics! Let’s continue a little further, still without AI…

Comparing Vectors

Now, how can we compare this? This is where a mathematical concept comes in: Cosine Similarity. In other posts, we can go into detail about why this calculation helps to compare, but just understand that this operation can tell us how much two vectors are the same or not: you pass two vectors and it returns a value between 0 and 1.

The closer to 0 the comparison returns, the closer the compared vectors are. that is, equal, and the closer to 1, the further apart they are, that is, different. Besides this, there are other algorithms that also compare vectors (such as Euclidean distance), but for now, let’s only use cosine.

Again: I’m keeping things simple here, okay? If you’re a PhD in AI or mathematics, don’t get mad at me.

SQL Server joins the group…

So far, you’ve learned the main concepts: We have embeddings (which are these vectors that represent each phrase; it’s common to use the term “vectors” or “embeddings”—they refer to the same thing, okay?!), and we have a calculation to compare them. And this is where SQL Server and its new support for all this comes in!

Since I don’t yet have access to SQL Server 2025, I set up an Azure SQL Database, which already has minimal vector support and is sufficient to explain what I need. When 2025 is publicly released, we’ll do new tests there. So, here’s a very simple script that you can run on any Azure SQL Database:

The query creates a variable for each of our phrases, with the new vector type. This is the data type that Microsoft created to represent these arrays. The value passed in parentheses is the number of dimensions, which in our case is 2. I initialized each variable with its respective vector, according to our previous table. I could have created a table with a varchar column and a vector column, but I chose to use variables directly in the script to keep the code simpler.

Then, right after, we have a SELECT to compare these vectors, using the new function: VECTOR_DISTANCE. The first parameter is the mathematical comparison function. Here I specified ‘cosine’, because I want to use the cosine distance (‘euclidean’ would be for Euclidean distance). The other two parameters are the vectors to be compared, which in our case are in the variables (they could be columns, literals, etc.)

I generated a comparison between each of the phrases and the result was this:

What does this result tell me?

Of course a car and a bus are different, which is why the vectors are going to quite a bit more complicated as we continue.

A slightly more realistic example

Our embeddings are very, very simple… I kept it simple on purpose, just so you understand the comparison. Now, let’s make something a little more elaborate. See the table below:

Now we have 4 dimensions: Vehicle, Person, Color, and Gender. I chose 4 characteristics, and the values represent how much each phrase possesses of each characteristic. Here are the rules I established to define the values:

If you disagree with these values, or think they are disproportionate, don’t worry. The objective now is not the values themselves, but only that you understand that something defines these dimensions and calculates these values for each of them. For now, that thing was me… But it could be, for example, an AI trained with a giant amount of data that teaches this relationship…

Now, let’s see how all this compares, using cosine distance:

Executing this results in:

Now, we have much more interesting results than before. Let’s explain each column of the result:

Notice how mathematics does interesting magic in these results. Up to this point, we haven’t used absolutely anything from AI, just mathematics, and we only transformed concepts into numbers.

AI joins the group…

And where does AI fit into this whole story? As I mentioned above, it’s when it comes to generating embeddings. Most of language models can generate embeddings. In fact, in almost all cases, they need embeddings, because it is by using embeddings that they can understand the text, and thus can generate more text considering context.

So, basically, all this work I did of choosing dimensions and mapping the values is done by an AI that was trained with a lot of data (or with data from a specific area, which makes it generate the values of these embeddings much better than an AI that “knows a little bit of everything”).

And, in addition, you have much more than just 4 dimensions. Imagine how much we could compare if we added just one more dimension: Do the test yourself… use your creativity, choose a dimension and adjust it in the code above, and assign some values… You’ll see how this extra dimension can completely change the results. Now, consider the fact that AIs usually generates 768, 1536 dimensions…

To generate embeddings, you must have access to an AI model, input the desired text, and receive the embeddings as output. You can access AI models in many ways; most of them involve accessing a model via API using some programming language or a web UI. This table provides a summary of options, along with some of their pros and cons:

Generating Real Embeddings

Now that you know the basic concept behind the scenes, let’s generate real embeddings using an AI, and not from my head anymore.

For this post, we will use a Hugging Face Space. Hugging Face, often referred to as the “GitHub of AI,” hosts open-source models and demos that showcase the functionalities of these models through a web UI. I’ve set up a demo (called a “Space” in Hugging Face terminology) where you can simply paste your text, and it will generate embeddings that you can easily copy and use in your SQL Server script.

The link to the Space is: Text Embeddings – a Hugging Face Space by rrg92

This Space utilizes an open-source AI model from a company called Nomic. The model generates embeddings with 768 dimensions, which are significantly more than the 2 or 4-dimensional embeddings we explored in previous tests.

But you can use any model you want. You can use OpenAI API, or your own local install with ollama, for example. If you want more details on another ways, just leave comments that I show in future post how do that.

Note: When SQL 2025 is publicly available, I plan new posts showing how generate embeddings directly from database using lot of providers.

Real embeddings contain more decimal places that those I showed as example here. And it can contains negative values. So don’t worry about this slight difference from our example, just remember: they are just a vector of numbers (of a floating point numbers to be more precise).

Here is an example of embeddings generated using link above:

Important info about that big monster value

Real embeddings are a lot of data. You might be wondering: “Is it really that much information for just three words? Where does the AI get all of this from? Where does all this data come from?” The answer is simple: embeddings are fixed representations created by the AI model.

To better explain that let’s revisit our small 4-dimensional embeddings: Vehicle, Person, Color, Gender. Take the phrase: “Hi!”

Even though my sentence has just one word, it still maps to 4 dimensions. In this case, the values of these dimensions indicate low relevance for that word.

Now, imagine scaling this process up to 768 dimensions. If just 4 dimensions can capture useful information, think how complex and complete can be 768. The model has been trained to map a wide range of features (or characteristics) of words and texts into this fixed vector space. Each model has its own unique way of determining these values, and which the dimensions will be used, and this process is often a “black box” to us. For features that don’t have a direct mapping, the model still assigns some value—this could be zero or even a small negative number.

To illustrate further, let’s compare the phrases “a blue fish” and “a blue car.” Both embeddings would likely share similarities in the dimensions associated with the color “blue,” but they would differ in dimensions related to context—fish versus car. There are a lot of dimensions for “fish” that will have different values for “car.”

For example, if an AI model has a dimension to represent how “aquatic” a word is, for “fish,” this would be something close to 1, while for “car,” this could be close to or less than 0. If the model contains a dimension to represent how “metallic” a word can be, for “car,” it will be close to 1, but for “fish,” it will be close to or less than 0. Values closer to 1 indicate a strong positive association, closer to 0 indicate little to no association, and closer to -1 represent a negative or inverse association. Note that the two dimensions I mentioned, Aquatic and Metallic, are just examples. I cannot say if a model uses it because it is part of model’s internal workings and architecture.

Another important thing about real embeddings is that AI models don’t directly apply embeddings to entire sentences as shown in the simplified examples earlier. That approach was used here to make the concept more didactic and easier to understand. In practice, AI models calculate embeddings for each word (technically for each token, but for now, imagine a token as being equivalent to a word).

For a sentence, there are several techniques to combine these token-level embeddings into a single representation. One common method is to take the embeddings of all tokens and calculate the average for each dimension. However, this can sometimes result in a loss of meaning, as averaging might dilute the impact of certain key words. This issue is often more noticeable in shorter texts, where each word carries more weight in the overall meaning, compared to longer texts where the effect is less perceptible. This can explain why, in some examples below, we observe large distances compared to our previous examples.

It’s worth noting that embeddings are not perfect. For example, not all embeddings account for word position, though some advanced models do. Each AI model has its own particularities, and better models produce embeddings that are more accurate for comparisons. However, these better models often come with higher costs—either in terms of API usage fees or computational resources like GPUs required to run them.

All the details mentioned earlier about AI model embeddings can be further explored in future posts, especially once SQL 2025 becomes available, allowing us to dive deeper into techniques and real-world scenarios.

So, here’s the code with the real embeddings I generated using the Hugging Face Place mentioned earlier for each text.

I took each text from our previous example, entered it into the Hugging Face Space, and copied the resulting embeddings back into the script in their respective variables. Additionally, I updated the vector size to vector(768), as we now have embeddings of that size. (Note: If you use embeddings of an incorrect size, SQL Server will raise an exception with a clear error message.)

Running the query, the result is that:

Note that the result changed slightly, but we can still make the same interpretation:

In practice, most of the time you won’t use the absolute values of these comparisons, but rather as a sorting criterion, for example. Let’s demonstrate this now by inserting all these embeddings into a table and then performing searches:

The script contains a simple INSERT statement followed by several SELECT queries on the table with an ORDER BY clause. The results are sorted based on the output of VECTOR_DISTANCE.

I use an auxiliary variable called @Search to store the embeddings of my “search text.” These embeddings are then compared with the embeddings of each row in the table, and the results are sorted by the output of the comparison, which is calculated using VECTOR_DISTANCE.

Note that I repeated the @Search variable for each search text. I generated each embedding using the Hugging Face Space and copied it into the script.

Note, this short video shows how creating your own search vector is done using Hugging Face.

The text used to generate the embeddings is included as a comment above the variable assignment. For example:

In a real scenario, the values of the @search variable would come from an application that, before running the script, invokes an API to retrieve the embeddings. Additionally, in SQL Server 2025, we should be able to do this directly within T-SQL.

Once I have access to a public preview, I will write another blog post with detailed instructions on how to achieve that. For now, just keep in mind that these values are only here to demonstrate the comparison process!

Important: I generated each embedding using the same model. You (still) can’t compare embeddings generated by different models, because, after all, each model has its own rules. For example, in our first example, dimension 0 was vehicle. Most likely for the Nomic model, dimension 0 is not vehicle, and even less, if it were, it would be classified in the way I defined.

Here are the output examples of SELECT statements for each text for which I populated each the variable search and the commented results:

Notice how the distance between “A white car” and “white cars” is much smaller compared to the others. By sorting distance, we can retrieve the most similar segments.

Although the distance is high, compared to the others, the phrase about the queen has the smallest distance, which reflects its meaning. A more detailed phrase or a model with more dimensions could likely produce a smaller value.

The last example was a bit frustrating, right? There can be many reasons for this. Our model might struggle to understand certain terms due to limitations in its training data or might not perform well with short texts. I suspect it could be related to the Nomic model and its training. We can explore these scenarios in greater detail in future posts.

But for now, I’ll leave you with an exercise, and you can share your results in the comments:

And that’s not all, folks!

A super important point is that I didn’t need to know which dimension represents what in these embeddings generated by the AI. I don’t know if position 0 represents a vehicle, I don’t know what the value means. I only know that this whole set is enough for me to compare with another, and that I can use the result of this comparison to test the similarity of two phrases, based on context and meaning. That’s why I mentioned earlier that you don’t need to worry about the values. For a basic understanding of embeddings and their use, you don’t need to know the internal details. However, for more advanced use cases or troubleshooting, understanding the underlying model can help you predict how your text will behave. This requires diving into the fascinating and complex world of AI and large language models (LLMs).

The only relevant information for you is the number of dimensions and the quality of the model in handling the subject you are going to represent. For example, if the model doesn’t understand Brazilian Portuguese well, then your embeddings will be very bad for phrases in Portuguese. If you are going to use common terms in a specific area, such as medicine or law, and the model doesn’t know them well, you will also have problems.

It’s art!

In the case of SQL Server, especially in Azure SQL Database, support is still very basic. If you tried to apply this to a table of millions of rows, it would probably take a table scan, which is bad enough when doing simple comparisons between text values. That’s where that algorithm mentioned for 2025 comes in: DiskANN, which is a way to index and search for embeddings in these indexes.

I will cover this in a later post when I’ve tested it and it has become available to talk about.

This post provided the fundamentals for you to understand how SQL Server fits into the world of AI through embeddings. Any questions, just find me in the comments or on social media.

If you want more practical examples of how to use embeddings:

Thanks for reading and until next time!