Embedding models and dimensions: optimizing the performance to resource-usage ratio

If you have built anything with vector search on Azure SQL or SQL Server, you have probably run into this limit: the vector data type currently tops out at 1998 dimensions per embedding. That number stops a lot of people cold the moment they try to use OpenAI’s text-embedding-3-large model, because it returns 3072 dimensions by default, well past what the column can hold.

The natural assumption is that the newest and largest embedding model is off the table for Azure SQL. That assumption is wrong, and more importantly, chasing the full 3072 dimensions is usually not worth the cost anyway. Davide Mauri, Principal Product Manager on the Azure SQL team, laid out the reasoning and the T-SQL to back it up in a post on the Azure SQL Dev Corner blog. Here is what the numbers actually show, and how to apply the dimensions parameter directly from a stored procedure call.

What the MTEB Benchmark Actually Shows

MTEB, the Massive Text Embedding Benchmark, measures how embedding models perform across a wide range of retrieval and classification tasks. Filtering the public MTEB leaderboard down to the models available through OpenAI gives a useful comparison, because these are the models most Azure SQL customers reach for first.

The pattern in the leaderboard is consistent: average performance barely moves as you shrink text-embedding-3-large from its full 3072 dimensions down to smaller sizes. At 256 dimensions, roughly one-twelfth the footprint of the full model, the score is still close to the top of the chart. That is a lot of accuracy retained for a twelvefold cut in storage.

This is not a quirk specific to one benchmark run. OpenAI documented the same behavior when it released the text-embedding-3 family in January 2024: a text-embedding-3-large output shortened to 256 dimensions still outperforms the older, unshortened text-embedding-ada-002 model running at its full 1536 dimensions. In other words, a smaller slice of the newer model beats the full size of the older one.

Why You Can Shorten an Embedding Without Losing Its Meaning

text-embedding-3-large and text-embedding-3-small are trained so that the most important information sits toward the front of the vector. Passing a dimensions parameter in the API call truncates the tail of the vector rather than compressing it after the fact. Because the model was trained with this truncation in mind, the shortened vector still represents the underlying concept well, it just does so with less precision at the margins.

This matters for the Azure SQL dimension cap. Instead of treating 1998 as a wall that rules out the newer models, treat it as a budget. You can request exactly the number of dimensions you plan to store, and the API does the trimming for you before the vector ever reaches your database.

Setting the Dimension Count From T-SQL

The dimensions parameter is just another field in the JSON payload you send to the Azure OpenAI embeddings endpoint. Azure SQL can call that endpoint directly using sp_invoke_external_rest_endpoint, without a middle-tier service in between. The snippet below builds the request, calls the endpoint, pulls the embedding array out of the JSON response, and casts it into a vector(1024) column value.

declare @inputText nvarchar(max) = 'It''s fun to do the impossible.';
 
declare @payload nvarchar(max) = json_object(
    'input': @inputText,
    'dimensions': 1024
);
 
declare @retval int, @response nvarchar(max)
exec @retval = sp_invoke_external_rest_endpoint
    @url = 'https://<your-resource>.openai.azure.com/openai/deployments/text-embedding-3-large/embeddings?api-version=2023-03-15-preview',
    @method = 'POST',
    @credential = [https://<your-resource>.openai.azure.com],
    @payload = @payload,
    @response = @response output;
 
declare @re nvarchar(max) = json_query(@response, '$.result.data[0].embedding')
select cast(@re as vector(1024));

The credential referenced here, [https://<your-resource>.openai.azure.com], is a DATABASE SCOPED CREDENTIAL that stores the Azure OpenAI API key so it never has to appear in application code. If you would rather not manage keys at all, Azure SQL also supports Managed Identity for this call, which is the safer option in production since there is no secret to rotate or leak. The one pitfall worth flagging: the dimensions value in the JSON payload and the size you cast to with vector() must match exactly, otherwise the cast fails at runtime rather than at query design time, and that mismatch is easy to miss when you copy this pattern across multiple stored procedures.

Picking a Sweet Spot: Why 1024 Dimensions Works Well

1024 dimensions is a reasonable default for text-embedding-3-large in most Azure SQL workloads. Each dimension stores as a 4-byte single-precision float, so 1024 dimensions costs 4KB per row versus 12KB for the full 3072-dimension vector. That is not just a storage saving, it directly reduces the CPU work behind every similarity query.

Vector search leans heavily on dot product or cosine distance calculations, and both scale with the number of dimensions being compared. Cutting the vector to a third of its size cuts that per-query computation by roughly the same proportion, which shows up as lower CPU usage and faster response times at scale, particularly once you are running similarity search against millions of rows rather than a demo table of a few hundred.

None of this means you should default to the newest, largest model and simply truncate it every time. Model choice should come from the MTEB leaderboard itself, not from brand recognition. Factors like language support, domain fit, latency, and cost per call matter as much as raw benchmark score, and a smaller model tuned for your domain can outperform a truncated large one. Spend ten minutes on the leaderboard before locking in a model for a production pipeline, it is cheaper than re-embedding your entire dataset six months later.

When You Genuinely Need More Than 2000 Dimensions

There are legitimate cases where 1998 dimensions is not enough. Certain machine learning workloads use embeddings with 10,000 or more dimensions, and some newer embedding models are pushing toward the 4,000-dimension range even at default settings. There is also growing interest in binary quantization, where each dimension is represented as a single bit rather than a float, which changes the storage math entirely and does not fit neatly into the current column size discussion.

The Azure SQL team has acknowledged these scenarios and is evaluating how to support them, though nothing was committed at the time of this post beyond continuing to gather feedback through 2025. If your workload genuinely needs the full dimensionality, the honest answer today is that Azure SQL’s native vector type is not yet the right fit, and you may need an external vector store or a workaround until that support lands.

Practical Takeaway

The 1998-dimension cap in Azure SQL looks restrictive until you look at what the benchmark data actually says about diminishing returns past a few hundred dimensions. For the vast majority of retrieval and RAG scenarios, requesting 1024 dimensions from text-embedding-3-large gives you near-identical retrieval quality at a third of the storage and a meaningful cut in query compute. Test with your own data before committing to a number, since MTEB results are an average across many tasks and your domain may behave differently, but treat 1024 as a sensible starting point rather than reaching straight for the default 3072.