Getting started with delivering generative AI capabilities in SQL Server and Azure SQL
AI is transforming everything we do, including how we interact with data. Data is the fuel for AI. Microsoft SQL Server and Azure SQL is the data platform to power today’s modern applications with security, performance, and availability, but also have capabilities and support scenarios required in the era of AI.
Azure SQL and SQL Server support building new generative AI experiences that become supercharged when combined with your data. In addition, SQL brings AI assistance to a new level with copilot experiences for both self-help and natural language to SQL capabilities.
In this blog post, I’ll share how you can get started with these new AI experiences—Azure SQL and SQL Server. First, check out our latest story on Microsoft Mechanics:
Responsible AI
Many conversations about AI starts with a statement on responsible AI. Microsoft has established a set of policies, research, engineering efforts, and principles to ensure AI technologies are adopted, implemented, and used in a responsible manner.
These principles include fairness, reliability and safety, privacy and security, inclusiveness, transparency, and accountability. Your data is your data. One promise for Microsoft is that private data of any user, including prompts and responses, are never used to fine tune a model that Microsoft hosts or implements.
Generative AI applications with your data
One of the motivations for generative AI applications is to become more productive, creative, and efficient through the generation of content in all forms: text, audio, and video. Many of today’s examples for generative AI applications involve the user of a natural language prompt and the interaction with a language model. Many of you have probably at some point used an application like ChatGPT or Microsoft Copilot which are great examples of generative AI applications.
Get smarter with your data
While these are great applications, they don’t know about your data. The combination of a generative AI application with your data, for example, stored in a database, can be quite powerful. Generative AI provides methods for smarter searching on your data. A common application pattern is to use language models with a prompt application to “chat with your data.” Using the concept of vector embeddings, language models allow you to get more precision on questions about your data. In addition, responses to questions are more tailored to your users and searches can often be faster because language models allow you to use the power of natural language. Generative AI applications with your data provide unique intelligence in an interactive manner, including conversations. Language models are trained to provide more context on your search, often giving you more (hence generated) content than you might normally get using common searching techniques within a database engine with a language like SQL.
Prompt engineering, Retrieval Augmented Generation fundamentals, and hybrid search
As you investigate how you can take advantage of generative AI with language models, there are two important concepts to understand:
Prompt engineering is the discipline of using high quality and descriptive prompts when interacting with a language model. The concept is simple. The better the prompt, the likelihood of a better response from the model. For example, let’s say you use Microsoft Copilot and type in a prompt like “What are the best steak restaurants in Fort Worth, Texas?” You will get a good list of steak houses in Fort Worth, Texas based on a search by Copilot of rankings across a broad set of searches. But what if you are on a bit of a tight budget? Instead of looking at the results from the prompt and trying to figure out what prices you can afford you could instead ask “I’m on a tight budget but want to eat at a good steakhouse in Fort Worth, Texas.” Now your results are more tailored for what you really want. And since you are interacting with a language model, it understands the phrase “tight budget” means you need choices that are good but affordable.
While this technique can be great if you are interacting with a model that is trained to help you search the internet, what about your own data? One prompt engineering technique to get smarter with your data is called Retrieval Augmented Generation (RAG). The concept of RAG is to search for information from a source of data and use those results to augment the prompt to the model. For Azure SQL and SQL Server, this could mean using standard SQL techniques to search for data using Transact-SQL (T-SQL), taking these results, and sending them along with the original prompt to the language model. This technique is simple and can be an effective way to get smarter with your data, and this can work with almost any type of data you search, not just SQL databases.
For Azure SQL and SQL Server, a more sophisticated technique is called hybrid search. With hybrid search, you can use the power of vector search combined with the query capabilities of your SQL data. Vector embeddings are numerical representations of data that capture semantic meaning and similarities. The key to embeddings with language models is that the model can generate embeddings based on data like text. This means you can take text data inside your SQL database and use a model to generate embeddings and then store these embeddings in your database. Now anytime you want to search for data inside the database, you can send a prompt to a language model which will generate embeddings for the prompt. And then you can use vector search techniques to compare the embeddings from the prompt with the embeddings stored in your database. You can then combine the vector search with other techniques you would normally use in T-SQL to find data in your database: a hybrid search.
There are methods today to use hybrid search completely inside the engine using T-SQL and outside the engine using Microsoft Azure AI Services or frameworks like LangChain or Semantic Kernel.
Get started quickly with Azure AI Services
One approach to get started quickly with no code required is to index your SQL database using Azure AI Search and then use Azure OpenAI Service to build a simple prompt app and “chat with your data” using a hybrid search technique.
You can use Azure AI Search to build an index based on a table in your SQL Server or Azure SQL database. When you build the index, you can apply a skillset to generate embeddings based on your data and store the result in the index. Now you can use Azure OpenAI with a prompt application to perform hybrid searches on your data. One example prompt application to perform simple testing is to use Azure AI Studio. In addition, as you change your SQL data, the index is automatically updated including the embeddings. The figure below shows the basic flow:
You can see this in action from the latest Microsoft Mechanics video or download a deck with demo recordings. One of the interesting aspects of this example is the method of changing the system message to direct the language model to respond in a unique way using the same data. This is also a great example of prompt engineering.
Learn more about Azure SQL in Azure AI Search.
Use hybrid search inside the engine with T-SQL
Let’s say instead of using a separate index, you would like to build generative AI capabilities for your application all inside the engine using T-SQL. You can do this in a very powerful way for Azure SQL Database today using a combination of vector embeddings, vector search, and other T-SQL search methods. This is a true hybrid search because you are using all the power of the SQL query processor together with a vector search. An example my colleague Davide Mauri has developed uses these techniques to help him find the best restaurant for one of this favorite Italian foods, focaccia bread.
Davide built an application that stores reviews from restaurants in the form of vector embeddings using Azure OpenAI Service with Azure SQL Database Representational State Transfer (REST) API inside the engine. With this in place, he can take any prompt to search for the best focaccia bread and use the same technique to generate embeddings for the prompt. Then, he can use a new T-SQL vector_distance function to perform a similarity search. The true power of SQL is possible because Davide built queries to combine this vector search with other criteria from spatial types, the new JSON data type, and the new Regular Expression (RegEx) T-SQL capabilities.
You can see a diagram of how these techniques are combined together below:
You can see this demo in action in our Microsoft Mechanics video or download a deck with demo recordings. You can learn more about the new JSON data type (preview). You can also sign-up to preview the new vector search capabilities and RegEx in Azure SQL Database.
Building generative AI applications using frameworks
There are other methods to build generative AI applications with Azure SQL and SQL Server using frameworks such as:
- LangChain:
LangChain is an open-source framework to orchestrate AI applications with language models. You can use programming languages such as Python and JavaScript to build your own generative AI application. LangChain supports the SQL Agent Toolkit which allows you to interact with a SQL database using natural language prompts. The toolkit integrates the connection to your database with a language model to generate SQL queries based on natural language prompts. You can see an example of this in the blog post “Building your own DB Copilot for Azure SQL with Azure OpenAI GPT-4.” - Semantic Kernel:
Semantic Kernel is an open-source SDK to allow you to build AI applications in C#, Python, and Java, interfacing with many common models in the industry such as OpenAI, Azure OpenAI, and Hugging Face. A library has been built to allow a Semantic Kernel application to interact with Azure SQL Database (and use the new vector search capability) called the SQL Connector.
See a full range of SQL and generative AI examples.
The age of copilots
Microsoft has transformed the industry and how we work and live with a new set of AI assisted experiences called Microsoft Copilot. Copilots are AI companions that work everywhere you do and intelligently adapt to your needs.
Use Copilots where you live
I realize there seem to be copilots everywhere. It is hard to keep track. Microsoft is investing in Copilot experiences in almost every product or service. Use the product or service you normally do and see what Copilot can offer. For example, if you have Microsoft 365, use Copilot for Microsoft 365 naturally within Microsoft Teams or any Office product or service. I personally use Microsoft Copilot in my Edge browser or on the app on my phone for any search experience I need today—web or work related.
Microsoft Copilot in Azure
The primary resource to manage and explore Microsoft Azure is the Azure portal. You can now use Microsoft Copilot in Azure within the Azure portal to manage, deploy, and troubleshoot Azure resources. Azure SQL Database is one of the most popular Azure resources in the world, so we have built two distinct experiences within the Copilot in Azure framework using natural language for self-guided assistance and T-SQL query authoring:
Microsoft Copilot in Azure integration
One of the strengths of SQL Server is the deep built-in telemetry within the engine all accessible through T-SQL. This includes Dynamic Management Views (DMV) and Query Store. These rich, traditional capabilities shine through now in Copilot. For example, you can prompt with Copilot a general statement like “My database is slow” and Copilot, based on your permissions, will access real-time diagnostic data, in the context of your database, to help you quickly navigate difficult, and often vague, performance problems. Here is an example:
You can then continue a conversation with Copilot to tune the query causing the problem. There are many different skills that Copilot can help you all in the context of your database. Learn about all the possibilities of Copilot skills in Azure SQL Database (preview).
Natural language to SQL
The T-SQL query language has so many great capabilities and possibilities. But the open nature of T-SQL also leads to difficulties in crafting queries to meet the need of your application. Along comes a copilot experience to allow you to “chat” with your database using natural language in the context of your database and schema: table, columns, and key relationships. A simple example is being able use a natural language statement to generate a query that typically requires several joins over multiple tables like the following:
Learn more how to use natural language to SQL.
You can see both experiences in action in our Microsoft Mechanics video or download a deck with demo recordings.
Innovations moving forward
We are just beginning with SQL and AI. We have innovations for the future planned for enhancements with AI services, enhancements for deep integration for vector search, and enhanced Copilot experiences for SQL Server. Stay tuned for future blog posts showing all of these innovations.
Learn more today
Here are more resources for you to learn more about SQL and AI:
- Explore the capabilities of Azure SQL Database.
- Keep track of SQL and AI at our one-stop documentation hub.
- Keep track of all of our SQL and AI examples.
- Showcase your own demos using our decks with demo recordings built-in.
- Read our team’s blog on developer experiences for SQL including the latest AI examples at Azure SQL Devs’ Corner.
- To see all this in action, check out our new Microsoft Mechanics video.
- Learn the essentials to building a SQL generative AI application.
- Check out the comprehensive blog post from Sanjay Mishra to explore the power of generative AI applications with Azure SQL Database.
- Learn more about Copilot in Azure SQL Database experiences from Joe Sack’s Data Exposed episode.
- Check out our Microsoft Build 2024 sessions:
Azure SQL
Migrate, modernize, and innovate with the modern SQL family of cloud database services