Thomas Hallam

14 March 2024

Enter Prompt+ EAP: your AI-powered database development partner in the making

After many cups of coffee and takeaway pizzas, something changed in the world of SQL Prompt in November 2023. For the first time in SQL Prompt’s history, our engineering team at Redgate brought AI to its breadth of capabilities and called it Prompt+. Using generative AI-powered insights and context-based awareness, Prompt+ takes natural language queries and turns them into SQL coding suggestions. This means you can spend more time analyzing, iterating and optimizing your code instead of developing from scratch each time.

Right now, Prompt+ is an Early Access Program (EAP), with hundreds of participants already in the program and an ever-growing waitlist to join. We’re hugely appreciative of the community contributions to the evolution of Prompt+, and in this blog post we want to share more about the ways Prompt+ could supercharge your productivity as you code. If you haven’t joined our EAP yet, I’ve shared a link to our sign-up page below.

What is Prompt+?

Prompt+ brings together the power of a generative AI model, context-based insights and expertise in SQL coding to collaborate with you as you code.

This isn’t just another code generation tool – it’s your database development partner. What sets Prompt+ apart is its ability to create, analyze and modify SQL, using natural language together with a deep understanding of your database schema . It’s like having a virtual companion by your side 24/7, offering ready assistance so you make the most of your full coding potential.

Prompt+’s introduction is a natural next step given the increasing popularity and take up of AI and ML tools in code development. Our recent State of the Database Landscape survey found that 20% of organizations were already using AI in the context of database management and 35% were considering it.

For now, Prompt+ is a preview feature, available through our Early Access Program. We’re in the process of developing features and options that are most valuable to users, based on our EAP participants’ feedback. If you’d like to be a part of the team shaping Prompt+, find out how to join in How do I try Prompt+ for myself.

Improvement of Prompt+ over ChatGPT alone

ChatGPT is an incredible online tool allowing a back-and-forth chat with OpenAI’s large language models. In 2023 it has become a sensation and made its way into many of our professional and personal toolkits . It’s flexible, and always there in your browser when you need it. But that flexibility is often slightly inconvenient when it comes to working with external resources, say code, or your databases. That’s where Prompt+ comes in.

For AI to create accurate and productive queries, it needs to understand the structure of your database. For example, some of the things it might need to know about could be tables, columns, data types and how all those tables relate to one another.

Not only would it be time-consuming to provide ChatGPT with the relevant information yourself, if your database structure is very large, then it could be too large to pass to ChatGPT at once.

Prompt+ handles all this at the click of a button, selecting and providing the AI with the information about your schema that it needs to help you achieve your goals. This makes generating queries using AI for your database simple; saving you time.

How do I try Prompt+ for myself

While we refine Prompt+ we’re growing our active EAP participant group at a steady pace, so that we can learn and develop capabilities more efficiently. We’re very grateful to all who have already joined our EAP waitlist, and we will welcome you into the program soon. Meanwhile, if you’d like to join the waitlist, please head over to this welcome page to find out more. Since Prompt+ is part of our Early Access Program and uses AI technology, you will need to review and accept a new EULA to participate.

Getting started with Prompt+ EAP

In this section, we’ll walk through some examples for how Prompt+ can help you write some simple reporting queries, as well as perform DDL tasks like creating tables. Please note that as with many AI powered tools, the responses from Prompt+ are non deterministic and may vary between requests.

How to access Prompt+ in SSMS

Prompt+ can be opened in the following ways from SSMS:

• Click Alt+Z
• Select “Open Prompt+ AI” from the “SQL Prompt” menu.

It can be opened:

• Without selecting any existing text. Prompt+ will not be aware of any text in the window.

• By highlighting some text in a query and opening it. Prompt+ will then use the highlighted text to inform its response and replace the selected text with its reply.

Using natural language with Prompt+ to generate SQL

In the below example, we’re using the AdventureWorks database. We’ve asked Prompt+ the question: “How many people live in the UK?” by typing the question into the Prompt+ text box, like so:

By clicking the blue arrow in the text box, Prompt+ will then consider the question and, shortly after, respond with a suggestion. For our question in this example, Prompt+ suggested:

If we then run that query, we get the correct answer for this example using the AdventureWorks database:

If we’re happy with the response and want to close the window, we can click the tick. Clicking the cross will close the window if we’re unhappy with the response. But if we want to regenerate the response, we can click the spinning arrows. This will undo the response we just received and try again. Prompt+ will also try to make the new response different than before.

Interacting with Prompt+’s suggestion

Let’s say we ask Prompt+ the following question about AdventureWorks database:

And we get the following suggestion:

We may also want to know more than just the total sales. Perhaps we want to know the names of the people involved. Prompt+ helps us to iterate on our previous suggestion. For example, we can ask for additional information in the already-generated query:

The resulting query still answers our initial question, but Prompt+ has now included additional columns we asked for when iterating on the generated query:

It does this by remembering what we previously asked of Prompt+ in this session, including it in each request, as well as including the SQL generated by the previous request. If we want to clear the history, we can close and reopen Prompt+.

Creating database objects

Let’s say we now want to extend the AdventureWorks database to include Marketing Campaigns. We could make the following request:

Prompt+ could then give the following response, tackling each element we specified in our natural language request:

Writing a stored procedure

If we want to write a stored procedure, for example, that updates the price of a product from Production.Product, based on a given percentage or a fixed amount, and then updates Production.ProductCostHistory appropriately; then we could use Prompt+ to make the following request, again using natural language:

Prompt+ could then give the following response, which, as we can see, addresses the requirement to update the price on a given percentage or a fixed amount, and then inserts the new price into the product cost history table:

Getting a faster response from Prompt+

All the examples above have been using Prompt+’s “Quality” mode by default. This aims to provide responses with the highest quality, but at the cost of speed.

If we’d prefer faster responses, but potentially at the expense of quality, then we can click the word “Quality” to change it to “Speed” mode, and vice versa. Prompt+ will then provide faster responses.

For example, the request we gave at the start of the blog took 8 seconds to produce its response, but got the answer correct the first time:

If we try this again (with a fresh Prompt+) and set it to Speed mode:

Prompt+ may give the following response:

This took less than 2 seconds to produce, but it isn’t quite as accurate as the response we received for Quality mode, as it’s joining Person.Address.AddressID with Person.Person.BusinessEntityID. In this situation then, we will have to do more manual checking and work to make sure the SQL is as we want it to be. The decision on whether to choose Quality or Speed mode will be context-dependent and we’re particularly keen to hear our EAP users’ experiences with both modes.

Clarifying questions

Sometimes we may give our question to Prompt+ and, as is the nature of working with AI, it may not always understand our intention.

For example:

Prompt+ may give the response:

This may not be quite what we wanted. Perhaps we only wanted the salaries of those called “Sales Representative” to be included, rather than the whole of the sales department including managers. Let’s also say that we only wanted to use the latest salary information, rather than taking an average over time.

We could clarify our query by making the request:

At which point Prompt+ will work to create a query that’s nearer to our intention, as shown in the example below.

We can see in this example that iterating on our question can help Prompt+ better understand our intention and generate something closer in line with our requirements. We can add multiple layers of clarification to our original question for Prompt+. When we’re happy with the response, we can click the tick. There’s also the option to click the spinning arrows to request Prompt+ generates a different response than the previous one.

What next for Prompt+?

We’re continuing to build on the capabilities in Prompt+ and work with our EAP participants’ feedback to introduce further enhancements. If you’d like the chance to participate in the EAP, you can sign up for the waitlist and we will be introducing new participants as soon as space becomes available. And for our active EAP participants, please get in touch with our engineering team sqlpromptteam@red-gate.com to discuss your feedback. We’ll be really happy to hear from you.