{"id":6186,"date":"2025-11-18T13:00:20","date_gmt":"2025-11-18T21:00:20","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=6186"},"modified":"2025-12-08T08:13:38","modified_gmt":"2025-12-08T16:13:38","slug":"ai-functions-public-preview-azure-sql","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/ai-functions-public-preview-azure-sql\/","title":{"rendered":"New T-SQL AI Features are now in Public Preview for Azure SQL and SQL database in Microsoft Fabric"},"content":{"rendered":"<p>At the start of this year, we released a new set of <a href=\"https:\/\/learn.microsoft.com\/sql\/t-sql\/functions\/ai-functions-transact-sql\">T-SQL AI features<\/a> for embedding your relational data for AI applications. Today, we have brought those features to Azure SQL and SQL database in Microsoft Fabric.<\/p>\n<p>This post will help you get started using the new AI functions of Azure SQL.<\/p>\n<h2>Prerequisites<\/h2>\n<ul>\n<li>An <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-sql\/database\/free-offer?view=azuresql\">Azure SQL database<\/a> or <a href=\"https:\/\/learn.microsoft.com\/en-us\/fabric\/database\/sql\/overview\">SQL database in Microsoft Fabric<\/a> created with the <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/samples\/sql-samples-where-are?view=sql-server-ver17\">adventureworks<\/a> sample data<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/ssms\/install\/install\">SSMS<\/a> or VS Code with the MSSQL Extension<\/li>\n<li>An <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/ai-foundry\/foundry-models\/concepts\/endpoints?tabs=python\">Azure OpenAI embeddings endpoint<\/a>\u00a0with the following 2 key pieces of information:\n<ul>\n<li>The endpoint URL<\/li>\n<li>The auth key for the endpoint<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>Set up your environment<\/h2>\n<p>The following section guides you through setting up the environment and installing the necessary software and utilities.<\/p>\n<h2>Set up the database<\/h2>\n<p>The following section guides you through using the embeddings model to create vector arrays on relation data and use the new vector similarity search functionality in Azure SQL and SQL database in Microsoft Fabric.<\/p>\n<h3>Create database scoped credentials<\/h3>\n<p>Use the following sample code to create a set of database scoped credentials for calling our Azure OpenAI Endpoint and providing the key in the header:<\/p>\n<p><strong>Note: Your Endpoint URLs and Key will be different that these in the blog post<\/strong><\/p>\n<pre>-- Create a master key for the database\r\nif not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##')\r\nbegin\r\ncreate master key encryption by password = N'V3RYStr0NGP@ssw0rd!';\r\nend\r\ngo\r\n\r\n-- Create the database scoped credential for Azure AI Content Understanding\r\nif not exists(select * from sys.database_scoped_credentials where [name] = 'https:\/\/azure.cognitiveservices.azure.com\/')\r\nbegin\r\ncreate database scoped credential [https:\/\/azure.cognitiveservices.azure.com\/]\r\n<span class=\"kwd\">with<\/span><span class=\"pln\"> identity <\/span><span class=\"pun\">=<\/span> <span class=\"str\">'HTTPEndpointHeaders'<\/span><span class=\"pun\">,<\/span><span class=\"pln\"> secret <\/span><span class=\"pun\">=<\/span> <span class=\"str\">'{\"api-key\":\"<\/span><strong><span class=\"variable\" data-name=\"openaiApiKey\">YOUR_AZURE_OPEN_AI_KEY<\/span><\/strong><span class=\"str\">\"}'<\/span><span class=\"pun\">;\r\n<\/span>end\r\ngo<\/pre>\n<h3>Create the EXTERNAL MODEL in the database<\/h3>\n<p>1. Using SSMS or VS Code, login to the database.<\/p>\n<p>2. Open a new query sheet<\/p>\n<p>3. Next, run the following SQL to create an EXTERNAL MODEL that points to an Azure\u00a0 OpenAI embedding model (here ill be using text-embedding-3-small):<\/p>\n<p><strong>Note: Your Endpoint URLs will be different that these in the blog post<\/strong><\/p>\n<pre>CREATE EXTERNAL MODEL text3small\r\nWITH (\r\nLOCATION = 'https:\/\/azure.cognitiveservices.azure.com\/openai\/deployments\/text-embedding-3-small\/embeddings?api-version=2023-05-15',\r\nAPI_FORMAT = 'Azure OpenAI',\r\nMODEL_TYPE = EMBEDDINGS,\r\nMODEL = 'text-embedding-3-small',\r\n<span class=\"pln\">CREDENTIAL <\/span><span class=\"pun\">=<\/span> <span class=\"pun\">[https:\/\/azure.cognitiveservices.azure.com\/<\/span><span class=\"com\">]<\/span>\r\n);<\/pre>\n<h3>Test the EXTERNAL MODEL<\/h3>\n<p>To test the embeddings endpoint, run the following SQL:<\/p>\n<pre>select AI_GENERATE_EMBEDDINGS(N'test text' USE MODEL text3small);<\/pre>\n<p>You should see a JSON vector array returned similar to the following:<\/p>\n<pre>[0.1529204398393631,0.4368368685245514,-3.6136839389801025,-0.7697131633758545...<\/pre>\n<h3>Embed Product Data<\/h3>\n<p>This next section of the tutorial will alter the Adventure Works product table to add a new vector data type column.<\/p>\n<p>1. Run the following SQL to add the columns to the Product table:<\/p>\n<pre>ALTER TABLE [SalesLT].[Product]\r\nADD embeddings VECTOR (768),\r\nchunk NVARCHAR (2000);<\/pre>\n<p>2. Next, we are going to use the EXTERNAL MODEL and AI_GENERATE_EMBEDDINGS to create embeddings for text we supply as an input.<\/p>\n<p>Run the following code to create the embeddings:<\/p>\n<pre>-- create the embeddings\r\nSET NOCOUNT ON;\r\n\r\nDROP TABLE IF EXISTS #MYTEMP;\r\n\r\nDECLARE @ProductID int\r\nDECLARE @text NVARCHAR (MAX);\r\n\r\nSELECT * INTO #MYTEMP FROM [SalesLT].Product WHERE embeddings IS NULL;\r\n\r\nSELECT @ProductID = ProductID FROM #MYTEMP;\r\n\r\nSELECT TOP(1) @ProductID = ProductID FROM #MYTEMP;\r\n\r\nWHILE @@ROWCOUNT &lt;&gt; 0\r\nBEGIN\r\nSET @text = (\r\nSELECT p.Name + ' ' + ISNULL(p.Color, 'No Color') + ' ' + c.Name + ' ' + m.Name + ' ' + ISNULL(d.Description, '')\r\nFROM [SalesLT].[ProductCategory] c,\r\n[SalesLT].[ProductModel] m,\r\n[SalesLT].[Product] p\r\nLEFT OUTER JOIN [SalesLT].[vProductAndDescription] d\r\nON p.ProductID = d.ProductID\r\nAND d.Culture = 'en'\r\nWHERE p.ProductCategoryID = c.ProductCategoryID\r\nAND p.ProductModelID = m.ProductModelID\r\nAND p.ProductID = @ProductID\r\n);\r\nUPDATE [SalesLT].[Product] SET [embeddings] = AI_GENERATE_EMBEDDINGS(@text USE MODEL text3small), [chunk] = @text WHERE ProductID = @ProductID;\r\n\r\nDELETE FROM #MYTEMP WHERE ProductID = @ProductID;\r\n\r\nSELECT TOP(1) @ProductID = ProductID FROM #MYTEMP;\r\nEND<\/pre>\n<p>2. Use the following query to see if any embeddings were missed:<\/p>\n<pre>SELECT *\r\nFROM SalesLT.Product\r\nWHERE embeddings IS NULL;<\/pre>\n<p>3. And use this query to see a sample of the new columns and the data within:<\/p>\n<pre>SELECT TOP 10 chunk,\r\nembeddings\r\nFROM SalesLT.Product;<\/pre>\n<h2>Use VECTOR_DISTANCE<\/h2>\n<p>Vector similarity searching is a technique used to find and retrieve data points that are similar to a given query, based on their vector representations. The similarity between two vectors is measured using a distance metric, such as cosine similarity or Euclidean distance. These metrics quantify the similarity between two vectors by calculating the angle between them or the distance between their coordinates in the vector space.<\/p>\n<p>Vector similarity searching has numerous applications, such as recommendation systems, search engines, image and video retrieval, and natural language processing tasks. It allows for efficient and accurate retrieval of similar items, enabling users to find relevant information or discover related items quickly and effectively.<\/p>\n<p>This section of the tutorial will be using the new function VECTOR_DISTANCE.<\/p>\n<h3>VECTOR_DISTANCE<\/h3>\n<p>Uses K-Nearest Neighbors or KNN<\/p>\n<p>Use the following SQL to run similarity searches using VECTOR_DISTANCE.<\/p>\n<pre>declare @search_text nvarchar(max) = 'I am looking for a red bike and I dont want to spend a lot'\r\ndeclare @search_vector vector(768) = AI_GENERATE_EMBEDDINGS(@search_text USE MODEL text3small);\r\nSELECT TOP(4)\r\np.ProductID, p.Name , p.chunk,\r\nvector_distance('cosine', @search_vector, p.embeddings) AS distance\r\nFROM [SalesLT].[Product] p\r\nORDER BY distance;\r\n\r\ndeclare @search_text nvarchar(max) = 'I am looking for a safe helmet that does not weigh much'\r\ndeclare @search_vector vector(768) = AI_GENERATE_EMBEDDINGS(@search_text USE MODEL text3small);\r\nSELECT TOP(4)\r\np.ProductID, p.Name , p.chunk,\r\nvector_distance('cosine', @search_vector, p.embeddings) AS distance\r\nFROM [SalesLT].[Product] p\r\nORDER BY distance;\r\n\r\ndeclare @search_text nvarchar(max) = 'Do you sell any padded seats that are good on trails?'\r\ndeclare @search_vector vector(768) = AI_GENERATE_EMBEDDINGS(@search_text USE MODEL text3small);\r\nSELECT TOP(4)\r\np.ProductID, p.Name , p.chunk,\r\nvector_distance('cosine', @search_vector, p.embeddings) AS distance\r\nFROM [SalesLT].[Product] p\r\nORDER BY distance;<\/pre>\n<h3>Chunk with embeddings<\/h3>\n<p>This section uses the `AI_GENERATE_CHUNKS` function with `AI_GENERATE_EMBEDDINGS` to simulate breaking a large section of text into smaller set sized chunks to be embedded.<\/p>\n<p>1. First, create a table to hold the text:<\/p>\n<pre>CREATE TABLE textchunk\r\n(\r\ntext_id INT IDENTITY (1, 1) PRIMARY KEY,\r\ntext_to_chunk NVARCHAR (MAX)\r\n);\r\nGO<\/pre>\n<p>2. Next, insert the text into the table:<\/p>\n<pre>INSERT INTO textchunk (text_to_chunk)\r\nVALUES ('All day long we seemed to dawdle through a country which was full of beauty of every kind. Sometimes we saw little towns or castles on the top of steep hills such as we see in old missals; sometimes we ran by rivers and streams which seemed from the wide stony margin on each side of them to be subject to great floods.'),\r\n('My Friend, Welcome to the Carpathians. I am anxiously expecting you. Sleep well to-night. At three to-morrow the diligence will start for Bukovina; a place on it is kept for you. At the Borgo Pass my carriage will await you and will bring you to me. I trust that your journey from London has been a happy one, and that you will enjoy your stay in my beautiful land. Your friend, DRACULA');\r\nGO<\/pre>\n<p>3. Finally, create chunks of text to be embedded using both functions:<\/p>\n<pre>SELECT c.*, AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL text3small)\r\nFROM textchunk t\r\nCROSS APPLY\r\nAI_GENERATE_CHUNKS(source = text_to_chunk, chunk_type = N'FIXED', chunk_size = 50, overlap = 10) c<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>At the start of this year, we released a new set of T-SQL AI features for embedding your relational data for AI applications. Today, we have brought those features to Azure SQL and SQL database in Microsoft Fabric. This post will help you get started using the new AI functions of Azure SQL. Prerequisites An [&hellip;]<\/p>\n","protected":false},"author":95874,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[601,599,1,582,576,577,619],"tags":[],"class_list":["post-6186","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ai","category-azure-openai","category-azure-sql","category-openai","category-rest","category-rest-endpoint-invocation","category-t-sql"],"acf":[],"blog_post_summary":"<p>At the start of this year, we released a new set of T-SQL AI features for embedding your relational data for AI applications. Today, we have brought those features to Azure SQL and SQL database in Microsoft Fabric. This post will help you get started using the new AI functions of Azure SQL. Prerequisites An [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6186","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/95874"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=6186"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6186\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=6186"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=6186"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=6186"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}