{"id":108262,"date":"2026-02-09T13:52:11","date_gmt":"2026-02-09T13:52:11","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108262"},"modified":"2026-04-08T10:25:00","modified_gmt":"2026-04-08T10:25:00","slug":"sql-server-2025-create-external-model-and-ai_generate_embeddings-commands-explained","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/sql-server-2025-create-external-model-and-ai_generate_embeddings-commands-explained\/","title":{"rendered":"SQL Server 2025 CREATE EXTERNAL MODEL and AI_GENERATE_EMBEDDINGS Commands Explained"},"content":{"rendered":"\n

When SQL Server 2025 was announced<\/a>, Bob Ward mentioned that there would be some new commands for managing AI models and their definitions, as well as T-SQL<\/a> functions to generate embeddings<\/a>, etc. I even made a guess that the command might be CREATE AI MODEL<\/code>…<\/p>\n\n\n\n

Now that SQL Server 2025 has finally been released to the public, I was finally able to test and learn a lot about this command (named CREATE EXTERNAL MODEL<\/code><\/a>), and I want to share my knowledge with you here. If you’re new to the world of AI, don’t worry, we’ll start with the basics and evolve to where this command fits in!<\/p>\n\n\n\n

What’s an AI Model?<\/h1>\n\n\n\n

Just as the term “database” in SQL Server can mean the “database itself”, a “SQL instance”, or a general definition, the term “model” has come to be used in many ways. If you talk to someone who lives only in the world of Machine Learning, they will have one definition. A mathematician will have another definition, as will a developer introducing AI to their product. To help you understand what SQL Server’s definition is for the CREATE EXTERNAL MODEL<\/code> command, let’s dig deeper.<\/p>\n\n\n\n

An AI model is software that produces a result using AI algorithms and\/or libraries. You can access this software through an API<\/a>, whether in a cloud service, a virtual machine, a container, etc. This API can range from a simple HTTP call, to loading a DLL<\/a> and calling its functions. These AI models are created by companies, open-source teams, developers, and others around the world. For example, one of the most famous companies is OpenAI<\/a>, which became popular with ChatGPT. However, there are lots of them: Google<\/a>, Cohere<\/a>, Hugging Face<\/a>, Meta<\/a>, Nomic<\/a>, Microsoft<\/a>, and many others<\/a>.<\/p>\n\n\n\n

There are AI models for various tasks, such as generating text, images, or audio – and these are likely the ones you have interacted with the most lately. However there are many others that, while they don’t necessarily produce a final interactive result, you can use for processing data. For example, there are models that can calculate trends from history (like a line on a graph).<\/p>\n\n\n\n

The Embeddings Model<\/h2>\n\n\n\n

One of these model types is very important in the world of text processing: embedding models. Embeddings are a sequence of numbers that can represent the meaning of a word, phrase, or entire text. They are generated by AI models, and you can use this to search for similar texts or group them. It’s an extremely powerful resource for those who need to compare texts.<\/p>\n\n\n\n

However, embeddings are not just limited to text: you can have embeddings that represent an image, audio, a binary document, etc. Likewise, they will represent the meaning of that content, and if you want to compare it with others, you just need to generate the embeddings again for the content you want to compare. Because they are a numerical sequence, there are some mathematical algorithms you can use to compare them, and see how similar they are.<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

For example, OpenAI has an embedding<\/a> model called text-embedding-3-small<\/code>. To access and use it, I need to invoke it via API by passing the text I want, and it returns the embeddings to me:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

If you want to do this locally, you can use Ollama<\/a>, download lots of open-source models, and run them in your own GPU. Similarly, you access it via API:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

I can also load a model directly using Python<\/a> code to generate these embeddings:<\/p>\n\n\n\n

import torch\nimport torch.nn.functional as F\nfrom transformers import AutoTokenizer, AutoModel, AutoImageProcessor\nimport torch\n\n#prepare the model\ntokenizer = AutoTokenizer.from_pretrained('nomic-ai\/nomic-embed-text-v1.5')\ntext_model = AutoModel.from_pretrained('nomic-ai\/nomic-embed-text-v1.5', trust_remote_code=True)\ntext_model.eval()\n\ndef mean_pooling(model_output, attention_mask):\n    token_embeddings = model_output[0]\n    input_mask_expanded = attention_mask.unsqueeze(-1).expand(token_embeddings.size()).float()\n    return torch.sum(token_embeddings * input_mask_expanded, 1) \/ torch.clamp(input_mask_expanded.sum(1), min=1e-9)\n        \ntext = \"a car\"\nsentences = [text]\nencoded_input = tokenizer(sentences, padding=True, truncation=True, return_tensors='pt')\n \nwith torch.no_grad():\n   model_output = text_model(**encoded_input) # invoke the ai model!\n \n text_embeddings = mean_pooling(model_output, encoded_input['attention_mask'])\n text_embeddings = F.layer_norm(text_embeddings, normalized_shape=(text_embeddings.shape[1],))\n text_embeddings = F.normalize(text_embeddings, p=2, dim=1)\n\nprint (text_embeddings.tolist())[0]<\/pre><\/div>\n\n\n\n
In this post<\/a> I provide a much more detailed explanation of what embeddings are and how to use them within SQL Server. From this point forward, I assume that you already understand what embeddings are and the vector data type, which are essential for use with SQL Server.<\/em><\/p>\n\n\n\n
How to Access AI Models in SQL Server 2025<\/h1>\n\n\n\n
Now that you are familiar with the concept of AI models and what embeddings are, let’s use SQL Server to access them. As you noticed, we can access models using an HTTP API or directly in code by loading libraries. SQL Server allows both methods.<\/p>\n\n\n\n
Let’s start with an HTTP API first, which is the easiest way. Before using the CREATE EXTERNAL MODEL<\/code> command, let’s use the new procedure sp_invoke_external_rest_endpoint<\/code><\/a>. With this new procedure in SQL Server 2025, you can invoke APIs via HTTP, which is how most AI models are available.<\/p>\n\n\n\n
Before SQL Server 2025, it was possible to invoke HTTP using OLE Automation<\/a>, CLR<\/a>, or extended procedures<\/a>. However, all these methods enable more than just simple HTTP access, which might make DBAs hesitant to use them. With SQL Server 2025 you can now enable only<\/strong> the functionality to invoke HTTP APIs, which significantly reduces the amount of extra code enabled to run within your SQL instance.<\/em><\/p>\n\n\n\n
For these tests, we will use the API from the Cohere<\/a>, which allows you to create a free account. Cohere is one of the major developers of AI models. To create your account and generate an API KEY, Click here <\/a>and follow the steps to create a new account. Once your account is created, you will be redirected to your dashboard. Go to \u201cApi Keys\u201d:
<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
Your key will be available in the \u201cTrial Keys\u201d section. Trial keys are limited (1000 calls per month), but this is more than enough for you to be able to perform the tests on this page. If you ever want Cohere in production, you can obtain a \u201cproduction key\u201d, which is unlimited, but you need to pay for its usage.<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
To copy the trial key, click the eye icon in the \u201cKey\u201d column. Soon, you will need to copy this key.<\/em> <\/p>\n\n\n\n
Next, we’re going to invoke the Cohere API to obtain the embeddings of a test text. Let’s create a new database where we will centralize all these tests:<\/p>\n\n\n\n
CREATE DATABASE AiTests;<\/pre><\/div>\n\n\n\n
Let’s enable the sp_invoke_external_rest_endpoint<\/code> procedure:<\/p>\n\n\n\n
EXEC sp_configure 'external rest endpoint enabled',1\nRECONFIGURE\nGO\n<\/pre><\/div>\n\n\n\n
According to the Cohere documentation<\/a>, we can invoke the generation of embeddings by sending an HTTP POST request to the address https:\/\/api.cohere.ai\/compatibility\/v1\/embeddings<\/a>. At this address, we can pass the same format accepted by OpenAI (which is one of the formats acceptable by SQL Server). Translating this into the external rest endpoint call, this would be the code:<\/p>\n\n\n\n
USE AiTests\n\ndeclare @result nvarchar(max)\n\n\nexec sp_invoke_external_rest_endpoint 'https:\/\/api.cohere.ai\/compatibility\/v1\/embeddings'\n,@payload = '{\"model\":\"embed-v4.0\", \"input\":\"Test direct from SQL Server\"}'\n,@response = @result output\n\nselect\n    HttpStatus = json_value(@result,'$.response.status.http.code')\n    ,errors =   json_value(@result,'$.result.message')\n    ,GeneratedEmbeddings = json_query(@result,'$.result.data[0].embedding')\n    ,FullResult = @result<\/pre><\/div>\n\n\n\n
\"An<\/figure>\n\n\n\n
However, when running the code above, the returned response is an http 401 error. To rectify this, you need to use the @credential parameter of this procedure and specify the credential with the Authentication data, which you can do using the CREATE SCOPED DATABASE CREDENTIAL<\/code> command:<\/p>\n\n\n\n
USE AiTests\nGO\n\nCREATE DATABASE SCOPED CREDENTIAL [https:\/\/api.cohere.ai]\n    WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{\"Authorization\":\"bearer APIKEY\"}';\nGO\n<\/pre><\/div>\n\n\n\n
Enter your account’s ApiKey where it says \u201cAPIKEY\u201d – note that the name of the credential must be the base URL you’ll use. When trying to run the command above, you may receive this error:<\/p>\n\n\n\n
Msg 15581, Level 16, State 6, Line 37
Please create a master key in the database or open the master key in the session before performing this operation.<\/strong><\/p>\n\n\n\n
Since you’ll be storing sensitive data in your database, SQL requires it to be encrypted. In the case of scoped credentials, you need to create a master key<\/a> in your database (the key used to encrypt this type of object.) So, re-run the command above, now with the master key created:<\/p>\n\n\n\n
USE AiTests\nGO\n\n-- Remember to save the master key password, in case you need to restore the database on another server, etc\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Colocar Uma senha Forte aqui Guardar no Cofre!' ;\n\n-- Now you can create the scoped credential. Remember to replace APIKEY with your trial key from your Cohere account\nCREATE DATABASE SCOPED CREDENTIAL [https:\/\/api.cohere.ai]\n    WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{\"Authorization\":\"bearer APIKEY\"}';<\/pre><\/div>\n\n\n\n
Now we can repeat the command that invokes the Cohere API, using the @credential parameter:<\/p>\n\n\n\n
USE AiTests\n\nDECLARE @result nvarchar(max)\n\nexec sp_invoke_external_rest_endpoint 'https:\/\/api.cohere.ai\/compatibility\/v1\/embeddings'\n,@payload = '{\"model\":\"embed-v4.0\", \"input\":\"Test direct from SQL Server\"}'\n,@response = @result output\n,@credential = 'https:\/\/api.cohere.ai' -- > We just added that!\n\nselect\n    HttpStatus = json_value(@result,'$.response.status.http.code')\n    ,errors =   json_value(@result,'$.result.message')\n    ,GeneratedEmbeddings = json_query(@result,'$.result.data[0].embedding')\n    ,FullResult = @result<\/pre><\/div>\n\n\n\n
\"An<\/figure>\n\n\n\n
Congratulations! You have finally generated your first embeddings using an AI model!<\/p>\n\n\n\n
For a simple test, this execution is okay. But what if you need to generate embeddings for texts stored in a table column?<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Write accurate SQL faster in SSMS with SQL Prompt AI<\/h3>\n                
\n                                            Write or modify queries using natural language, get clear explanations for unfamiliar code, and fix and optimize SQL with ease – all without leaving SSMS.                                    <\/div>\n            <\/div>\n                                            Learn more and try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
The New CREATE EXTERNAL MODEL Command in SQL Server 2025<\/h2>\n\n\n\n
Now you know how to manually invoke the API of an AI provider, but SQL Server 2025 has introduced even more features that don’t require the use of a procedure directly.<\/p>\n\n\n\n
The first of these is the CREATE EXTERNAL MODEL<\/a><\/code> command, where you can create the definition of an AI model within your SQL database. Note that I am referring to \u201cthe definition,\u201d not the model itself. You don’t load the actual model into your SQL database, only the metadata<\/a> that SQL requires to invoke the model’s API.<\/p>\n\n\n\n
Let’s create a model for the Cohere API using this command:<\/p>\n\n\n\n
USE AiTests\nGO\n\nCREATE EXTERNAL MODEL CohereTest\nWITH (\n      API_FORMAT = 'OpenAI',\n      LOCATION = 'https:\/\/api.cohere.ai\/compatibility\/v1\/embeddings',\n      MODEL_TYPE = EMBEDDINGS,\n      MODEL = 'embed-v4.0',\n      CREDENTIAL = [https:\/\/api.cohere.ai]\n);<\/pre><\/div>\n\n\n\n
In CREATE EXTERNAL MODEL CohereTest<\/code>, the segment CohereTest<\/code> is an identifier, just like when you create a procedure or a table<\/a>. You can name it whatever you want, and it follows the same identifier rules.<\/p>\n\n\n\n
Immediately following, we have the list of parameters defined within the WITH (\u2026 )<\/code> (just like other commands have).<\/p>\n\n\n\n
The API_FORMAT<\/code> parameter specifies which API format SQL should use when this model is invoked. I could list the acceptable formats here, but this might change over time. What you need to know is that this defines how SQL will send the data to the URL you provide; therefore, the format you choose must be compatible with what the service you are using accepts. The official documentation is your primary source<\/a> for this. In our case, we are using the OpenAI format which is accepted by most AI tools and services, such as Cohere.<\/p>\n\n\n\n
The LOCATION<\/code> parameter will depend on the API_FORMAT<\/code>. In the case of OpenAI, it is the service URL. But in other formats, such as ONNX<\/a>, it is a directory where the files are located. Therefore, in these cases, it is always best to consult the documentation (and preferably keep an eye on the section on remarks<\/a>).<\/p>\n\n\n\n
MODEL_TYPE<\/code> is the model type (of course). Remember our model definition? There are several types. For now, SQL Server only accepts EMBEDDINGS<\/code> – however, it’s very likely that other models will be supported in future SQL updates or versions.<\/p>\n\n\n\n
MODEL<\/code> is the name of the model. Each AI provider may have several models, with various versions, costs, and quality. This parameter allows you to define which model name to use. In our case, we will use embed-v4.0, which is the most recent version from Cohere at the time this article was written. How do I know this? Cohere documentation: Cohere\u2019s Embed Models (Details and Application) | Cohere<\/a><\/p>\n\n\n\n
And finally we have the CREDENTIAL<\/code> parameter which, just like you specified when directly invoking the API with sp_invoke_external_rest_endpoint<\/code>, you need to specify here.<\/p>\n\n\n\n
Now that you have created the model, to use it you need the new function AI_GENERATE_EMBEDDINGS<\/code><\/a>, where you specify the text and the model created with CREATE EXTERNAL MODEL<\/code>:<\/p>\n\n\n\n
SELECT\n    AI_GENERATE_EMBEDDINGS('Test direct from SQL Server' use model CohereTest)\n<\/pre><\/div>\n\n\n\n
Note that the name I passed there, CohereTest<\/code>, is the same name I used when creating the external model via CREATE EXTERNAL MODEL<\/code>. We can store this data in the new vector type:<\/p>\n\n\n\n
DECLARE\n    @embeddings vector(1536)\n\nset @embeddings =  AI_GENERATE_EMBEDDINGS('Test direct from SQL Server' use model CohereTest)\n\nselect @embeddings\n<\/pre><\/div>\n\n\n\n
\"An<\/figure>\n\n\n\n
Why did I use the value 1536? By default, the Cohere API generates embeddings with 1536 dimensions (read the article I pointed to about embeddings to understand what these dimensions are). We can control this quantity in several ways. You can change the default value of the EXTERNAL MODEL<\/code>:<\/p>\n\n\n\n
ALTER EXTERNAL MODEL CohereTest SET ( PARAMETERS = '{\"dimensions\": 256}' )<\/pre><\/div>\n\n\n\n
The PARAMETERS<\/code> option allows you to specify a JSON<\/a> containing parameters that will be sent to the API. The supported value depends on each API, and thanks to the Cohere documentation, I know this. Now that I have changed the default parameter, if I repeat the same code above, this happens:<\/p>\n\n\n\n
Msg 42204, Level 16, State 2, Line 69
The vector dimensions 1536 and 256 do not match.<\/strong><\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
Now that we have altered the API, we need to adjust the value in the variable as well:<\/p>\n\n\n\n
DECLARE\n    @embeddings vector(256) -- changed (1536) to (256)\n\nset @embeddings =  AI_GENERATE_EMBEDDINGS('Test direct from SQL Server'  use model CohereTest)\n<\/pre><\/div>\n\n\n\n
And you can also adjust the parameter directly in the call to AI_GENERATE_EMBEDDINGS<\/code>:<\/p>\n\n\n\n
DECLARE\n    @embeddings vector(1024) -- vamos gerar agora embeddings de 1024 dimensoes\n\nset @embeddings =  AI_GENERATE_EMBEDDINGS('Test direct from SQL Server' use model CohereTest parameters convert(json,'{\"dimensions\": 1024}')  )\n<\/pre><\/div>\n\n\n\n
Note that I have now added the PARAMETERS<\/code> option to AI_GENERATE_EMBEDDINGS<\/code>. You need to pass a JSON type (hence the reason for converting it to the new JSON type, also added in SQL 2025).<\/p>\n\n\n\n
Multiple Rows<\/h2>\n\n\n\n
For a more practical example, let’s create a script that generates the embeddings for 50 SQL Server error messages<\/a> and stores them in a table (if you wish, you can adapt the script for another table of your preference, but remember that you are using a trial key and it has request limits):<\/p>\n\n\n\n
USE AiTests\nGO\n\ndrop table if exists #embeddings;\n\nselect top(50)\n\t text\n\t ,embeddings = convert(vector(256),AI_GENERATE_EMBEDDINGS(text use model CohereTest))\ninto\n\t#embeddings\nfrom\n\tsys.messages\n\nselect * from #embeddings;\n<\/pre><\/div>\n\n\n\n
\"An<\/figure>\n\n\n\n
Note how you can use AI_GENERATE_EMBEDDINGS<\/code> in places where any function is accepted. This makes the process much more flexible compared to using a procedure.<\/p>\n\n\n\n
Handling Errors and Performance<\/h2>\n\n\n\n
Up until now, we have only worked with ideal and perfect cases. However, in the real world, the response won’t always be correct or fast like these examples. Therefore, I thought it prudent to present some scenarios so that you are prepared and know how to investigate.<\/p>\n\n\n\n
The first thing that can happen is errors. The API you choose to use can generate errors for various reasons: something wrong you passed, invalid or expired credentials, errors on the other side, the returned format not being what SQL Server expects, etc.<\/p>\n\n\n\n
For example, here’s the result returned with the HTTP 401 error, indicating authentication problems (which could be an incorrect or expired API key):<\/p>\n\n\n\n
Msg 31742, Level 16, State 3, Line 77
Unrecoverable HTTP error 401 occurred.<\/strong><\/p>\n\n\n\n
Here is an error returned when a parameter specified in PARAMETERS<\/code> is not accepted by the API:<\/p>\n\n\n\n
Msg 31742, Level 16, State 3, Line 87
Unrecoverable HTTP error 422 occurred.<\/strong><\/p>\n\n\n\n
Note that the message will include the returned HTTP code. However, the API might return generic codes, such as 500. It is always best practice to consult the API documentation to understand what the returned code means. An important point is that the error codes above were returned by the Cohere API. Other providers may return different error codes, so it is always best to check the provider\u2019s API documentation to understand the codes it outputs and their meanings.<\/p>\n\n\n\n
SQL Server assumes that the API will return results in the same format it accepts according to the API_FORMAT<\/code> parameter. For example, this is the error it generates when the result is not a valid JSON expected by the OpenAI format:<\/p>\n\n\n\n
Msg 31744, Level 16, State 1, Line 87
The JSON path for embeddings could not be found in the response.<\/strong><\/p>\n\n\n\n
Another common scenario you will face is dealing with query performance. Invoking external services completely takes control away from SQL execution, and the only thing it has to do is wait. Therefore, in a scenario where you invoke it for every row in a table, you must be very careful. For one row, it’s fast, but for 10 rows, the time can change considerably. <\/p>\n\n\n\n
You can use the DMV sys.dm_exec_sessions_wait_stats<\/a> and monitor the consumption of AI_GENERATE_EMBEDDINGS<\/code>. For example, in the previous example we did with error messages, these were the session waits, ordered by what took the longest in total:<\/p>\n\n\n\n
select\n\t*\nfrom\n\tsys.dm_exec_session_wait_stats \nwhere\n\tsession_id = 58\norder by\n\twait_time_ms desc<\/pre><\/div>\n\n\n\n
\"An<\/figure>\n\n\n\n
What you primarily need to keep in mind is that using this within a transaction, for example, can cause many locks, especially if you are updating embeddings in a frequently altered table. You can use temporary tables<\/a> to minimize the impact on concurrency. For example, suppose you want to update the embeddings of a products table. Instead of performing the update directly on the products table, you can use an auxiliary temporary table:<\/p>\n\n\n\n
drop table if exists #ProductsEmbeddings;\n\n-- get next set of rows to update!\nselect top 10\n\tProductId\n\t,ProductDescription\n\t,embeddings\ninto\n\t#ProductsEmbeddings\nfrom\n\tproducts_embeddings \nwhere\n\tembeddings is null\n\n-- generate the embeddings (locking just temporary rows)!\nupdate #ProductsEmbeddings\nset\n\tembeddingsembeddings = AI_GENERATE_EMBEDDINGS(ProductDescription use model CohereTest)\n\n-- Update back\nupdate p\nset \n\tembeddings = tp.embeddings\nfrom\n\t#ProductsEmbeddings tp\n\tjoin\n\tproducts_embeddings p\n\t\ton tp.ProductId = p.ProductId<\/pre><\/div>\n\n\n\n
A final, extremely valuable performance tip that can make a huge difference is sending a batch of embeddings. Many AI providers allow you to generate embeddings for multiple texts simultaneously, avoiding sending 1 HTTP request per row, which is the main bottleneck when using AI_GENERATE_EMBEDDINGS<\/code>. <\/p>\n\n\n\n
The disadvantage is that your code might become more complex, but it can be very worthwhile if performance is crucial for you. For example, the code below shows a time comparison when generating embeddings for 50 error messages using row-by-row versus batch:<\/p>\n\n\n\n
USE AiTests\nGO\n\ndrop table if exists #messages;\n\nselect top(50)\n\t id = row_number() over(order by (select null))\n\t,text = convert(nvarchar(max),text)\n\t,VectorSingle = convert(vector(1024),null)\n\t,VectorBatch = convert(vector(1024),null)\ninto\n\t#messages\nfrom\n\tsys.messages s\n\n\nselect * From #messages\n\n\n-- updating using row by row\n\tdeclare @Start datetime\n\t\tset @Start = getdate();\n\t\tupdate #Messages\n\t\tset\n\t\t\tVectorSingle = AI_GENERATE_EMBEDDINGS(text use model CohereTest parameters converT(json,'{\"dimensions\": 1024}') )\n\tselect ElapsedAiGenerated = datediff(ms,@Start,getdate())\nGO\n\n-- updating using batch\n\n\tdeclare @body nvarchar(max),@result nvarchar(max),@Start datetime\n\t\n\tselect \n\t\t@body = (\n\t\t\tselect \n\t\t\t\t input = JSON_QUERY(JSON_ARRAYAGG(text order by id))\n\t\t\t\t,model = 'embed-v4.0'\n\t\t\t\t,dimensions = 1024\n\t\t\tfor json path,without_array_wrapper\n\t\t)\n\tfrom #messages\n\n\tset @Start = getdate();\n\n\t\texec sp_invoke_external_rest_endpoint 'https:\/\/api.cohere.ai\/compatibility\/v1\/embeddings'\n\t\t,@payload = @body\n\t\t,@response = @result output\n\t\t,@credential = 'https:\/\/api.cohere.ai'\n\n\t\tdrop table if exists #embresult\n\t\tselect  \n\t\t\tr.[key],embeddings =JSON_QUERY(r.value,'$.embedding')\n\t\tinto\n\t\t\t#embresult\n\t\tfrom \n\t\t\topenjson(@result,'$.result.data') r\n\n\t\tupdate m\n\t\t\tset VectorBatch = embeddings\n\t\tfrom \n\t\t\t#embresult o\n\t\t\tjoin\n\t\t\t#Messages m\n\t\t\t\ton m.id = o.[key] + 1\n\n\tselect ElapsedBatched = datediff(ms,@Start,getdate())\n\n\nselect \n\t*\n\t,convert(decimal(30,29),VECTOR_DISTANCE('cosine',VectorSingle,VectorBatch))\nFrom \n\t#Messages<\/pre><\/div>\n\n\n\n
\"An<\/figure>\n\n\n\n
An absurd difference – from 12 seconds to 510 milliseconds using batch. However, as you can see, this method makes the code more complex. Hopefully, in future versions of SQL 2025 (or through updates), there will be an easier way to use batch mode.<\/p>\n\n\n\n
sp_AddEmbeddings<\/h4>\n\n\n\n
Until then, if you want an easier way, consider using this procedure I created: sp_AddEmbeddings<\/code><\/a>. By passing the table name and the model, it uses batching to generate the embeddings:<\/p>\n\n\n\n
USE AiTests\nGO\n\ndrop table if exists #messages;\n\nselect top(50)\n\t id = row_number() over(order by (select null))\n\t,text = convert(nvarchar(max),text)\n\t,VectorBatch = convert(vector(1024),null)\ninto\n\t#messages\nfrom\n\tsys.messages s\n\ndeclare @Start datetime = getdate()\n\nexec sp_AddEmbeddings \n\t'#messages','text'\n\t,'openai'\n\t,@IdCol = 'id'\n\t,@model = 'CohereTest'\n\t,@IsExternal = 1\n\nselect ElapsedBatched = datediff(ms,@Start,getdate())\n\nselect * from #messages<\/pre><\/div>\n\n\n\n
\"An<\/figure>\n\n\n\n
sp_AddEmbeddings<\/code> allows you to generate embeddings into a vector column, using a model you specify in the parameter and AI providers. You need to create both sp_AddEmbeddings<\/code> and the respective provider procedure, for which I left three examples of here<\/a> (scripts spAddemb.*.sql).<\/p>\n\n\n\n
Metadata<\/h2>\n\n\n\n
Just like other objects you create, the sys.external_model<\/a>s<\/a> view returns all models created with CREATE EXTERNAL MODEL<\/code>. This way, you can query the current models and options.<\/p>\n\n\n\n
For example, this script that I made available for free in my Git repository<\/a> uses sys.external_models<\/code> to list the models created in all databases. This can be useful if you need to view something quickly:<\/p>\n\n\n\n
\"Image<\/figure>\n\n\n\n
ONNX<\/h2>\n\n\n\n
Now that you know how to connect your SQL to an AI model via an HTTP API, I will show you another way, which is not via HTTP but via a DLL, and the model will run directly on the same SQL Server machine. This is a useful scenario for environments that have strict data processing policies, cannot use external services like the ones we used, and need to process locally. This method uses ONNX.<\/a><\/p>\n\n\n\n
What is ONNX?<\/h3>\n\n\n\n
AI models are like an audio file, for example an .mp3. There are several programs that can play an MP3, which is an open format that anyone can read and reproduce. With AI models, we have the same scenario. The basis is neural networks that have a standard and format, and the values of the hundreds of thousands of parameters found during training.<\/p>\n\n\n\n
The ONNX<\/a>(O<\/strong>pen N<\/strong>eural N<\/strong>etwork EX<\/strong>change), which is open source and was created by Microsoft, Facebook, and AWS<\/a>, defines exactly how the binary of these networks should be saved and can be read for processing. To note, there are other formats as well – such as GGUF, SafeTensors, etc. See this post on Hugging Face<\/a> for more.<\/p>\n\n\n\n
As I mentioned, ONNX defines the format, which means you need software capable of ‘opening’ and executing that format. ONNX models have the onnx extension. To \u201copen and execute\u201d<\/em> these files, you need ONNX Runtime – software (or a library) with all the ONNX logic implemented. This runtime is available for various languages<\/a> but for SQL Server, we’ll use the runtime created for Windows, which is made available as a DLL, on its GitHub: Releases \u00b7 microsoft\/onnxruntime<\/a><\/p>\n\n\n\n
\"Image<\/figure>\n\n\n\n
In the case of SQL Server, Microsoft followed the same security standard as other services, such as Full Text Search or the execution of external scripts: the ONNX Runtime DLL is not loaded into sqlservr.exe, but rather into Launchpad.exe, which is started by the LaunchPad service<\/a> (formerly Machine Learning Services, used to run Python and R code.) The reason is simple: if there are any issues with the runtime, it does not stop the entire SQL database – only the runtime process suffers the consequences.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Enjoying this article? Subscribe to the Simple Talk newsletter<\/h3>\n                
\n                                            Get selected articles, event information, podcasts and other industry content delivered straight to your inbox.                                    <\/div>\n            <\/div>\n                                            Subscribe<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Using the ONNX Runtime<\/h3>\n\n\n\n
Now that you have the background on what ONNX is and an idea of how it works, we can see this in practice. To use ONNX in SQL Server, firstly install Launchpad (if you haven’t already), by opening the same SQL installer, choosing add features to existing<\/em>, and checking the option Ai Services and Language Extensions:<\/em><\/p>\n\n\n\n
\"Image<\/figure>\n\n\n\n
Once installed, make sure it’s been started in SQL Configuration Manager:<\/a><\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
Note the account name under \u201cLog On As\u201d – we will need it later.<\/p>\n\n\n\n
Now, let’s install the runtime:<\/p>\n\n\n
\n