Windows Copilot vs. Google Gemini to Build SQL Queries

By: Rick Dobson   |   Updated: 2024-06-25   |   Comments   |   Related: More > Artificial Intelligence

Problem

Please illustrate how Microsoft's Windows generative AI software can enhance my productivity as a T-SQL developer. Also, include some examples that compare Windows Copilot to Google Gemini.

Solution

MSSQLTips.com recently published an article for creating T-SQL code with the help of generative AI. This article demonstrated the use of generative AI implemented by Google Gemini.

This tip focuses on creating and evaluating T-SQL code with the help of generative AI implemented by Windows Copilot, a Microsoft offering. You can learn more about how to gain access to Windows Copilot with either Windows 10 or 11 at the Microsoft Support site.

The term generative AI refers to software-database applications that can create content in response to a prompt from a human operator. The technology grew from chatbot applications, which typically accept text-based inputs and return responses in text, graphic, and/or audio format). The response is drawn from the training of the application on a corpus of text (or other kinds of source data) used to train generative AI models.

This technology is evolving rapidly, even reaching the point where any technology professional should have at least a passing acquaintance with generative AI software operations and capabilities. Critically, for SQL professionals, the prompt can include a request for a T-SQL script to perform a task. To the extent that a technologist needs to write code in more than one computer language, the significance of generative AI can grow to provide programming examples from natural language prompts in many different programming languages.

A Quick Introduction to Windows Copilot

Windows Copilot is a digital assistant. You can open Windows Copilot from an icon on the Windows taskbar. Generative AI applications depend on large language models to facilitate parsing prompts and generating appropriate responses.

Windows Copilot can accept prompts based on input to a chat box in the form of text messages, images, files, and audio inputs from a microphone. Its interface includes an input box for text prompts along with the other forms of input. The interface also displays the generated response to the prompt.

A typical prompt for T-SQL code generation might include a statement like "Show me the T-SQL for a query that does 'X'." X represents any task that a T-SQL select statement can perform. Windows can then respond with a T-SQL select statement or say that X is not within the range of tasks a T-SQL query can perform. Windows Copilot can also perform many other tasks besides code generation, some of which are demonstrated in this article.

The Windows Copilot Interface

The following screen image shows the embedded Windows Copilot window within Windows 10. The Copilot window appears on the right-hand side of the Windows 10 screen. This notion of Copilot being embedded within Windows is significant because it facilitates concurrently working on Copilot and a Word document file, an Excel file, or a script on a tab within SQL Server Management Studio.

Selected elements of the Windows Copilot window are numbered to help you identify the function of these elements within Windows Copilot and Windows:

• Item 1 resides in the Windows taskbar. If Copilot is not open, click this icon below to open the window. Similarly, if you want to close the Copilot window, click the X in the top right corner of the Copilot window. Clicking X frees up screen space in Windows previously occupied by the Copilot window.
• Item 2 refers to the Copilot version identifier. This tip uses the Copilot (preview) version.
• Item 3 is an icon for toggling the view, allowing the Copilot window to overlay or sit side-by-side with other Windows screen content.
• Item 4 appears in the center of four rounded boxes. Each rounded box displays a sample prompt for a Copilot user to try. You can submit any pre-configured prompt to Copilot by clicking inside a rounded box. The two arrows above and to the right of the rounded boxes allow you to cycle through the pre-configured prompts to find one you may want to submit to Copilot.
• The text above item 5 has sample text you can type over with your own custom prompt for Copilot.
• Item 6 sits above special icons for adding custom content from an image, a file, or microphone input. In your custom prompt within item 5, you can reference the content for a file that you designate with the help of an icon below item 6.
• Item 7 is an arrowhead icon for submitting a prompt based on the selections made in items 5 and 6. You can successively submit multiple prompts to Copilot to have a conversation about a topic. Each prompt-response pair represents an element in a conversation on a topic.
• After you submit one or more prompts on a topic to Copilot, you may optionally copy and paste a set of prompt-response pairs for future reference. Clicking item 8 clears the Copilot window so you can start a new conversation in an empty one.

A Conversation about a T-SQL Script File

You can learn about the script files in a code repository with Windows Copilot. Many firms have code repositories because they can facilitate code documentation and maintenance. The contents of these repositories can serve vital functions in keeping a business operational. When programming staff members leave an organization, the documentation may not be readily discovered by the remaining staff. Undiscovered documentation (or code without documentation) can cause maintenance issues when script changes become necessary.

This section illustrates some steps that show how to use the Windows Copilot interface to learn about a T-SQL script and a data file it references. Assume it is known that the script's file resides in a file named bulk_insert_11.sql in the PC > Documents > SQL Server Management Studio path.

Starting from a fresh Windows Copilot session, you can attach the script file to the current conversation with the paper clip icon (item 6) in the preceding Windows Copilot interface. Navigate to and select the script file with the file explorer window that displays after clicking the paper clip icon. Then, click the Open button in File Explorer (see below). This attaches the script file to the conversation.

Next, enter a text prompt (item 5) in the Windows Copilot interface. The text prompt used in this example is "List the script in the attached file." After entering the prompt, click the Submit control (item 7) in the Windows Copilot interface. This causes the script to be displayed in the Windows Copilot interface, followed by a brief summary of the actions performed by the script.

In the following three screenshots, an excerpt from the updated Windows Copilot interface shows the response to clicking the Submit control.

• Screenshot 1: Displays the beginning portion of the script through a Bulk Insert statement. This code excerpt includes comment lines, but not all code in repositories will have comments.
• Screenshot 2: Displays code through the end of the script, along with summary comments about the actions that the script performs.
• Screenshot 3: Reminds the user to ask additional questions and offers some suggested questions. By clicking one of the three rounded boxes with suggested prompts, a user can submit the boxed prompt and get a response.

Note: The Bulk Insert statement in the script returned by the response parses data from a file named 'C:\My Text Files for SQL Server\Text_Document_1.txt'. Many database professionals may likely find the contents of the file helpful in understanding the code. PowerShell can be used to display the file's contents, but some professionals may also benefit from a reminder about PowerShell syntax for displaying the text in the file.

The next screenshot shows a Windows Copilot prompt to display the contents of the Text_Document_1.txt file in the 'C:\My Text Files for SQL Server' path using PowerShell.

Here is a PowerShell script and the results set with the contents of the Text_Document_1.txt file. Notice that the where clause in the last line of the bulk_insert_11 script filters out the last two lines from the Text_Document_1.txt file in the results set from the script.

Windows Copilot versus Google Gemini Responses for T-SQL Queries

Google Gemini and Windows Copilot are two generative AI applications that can respond to prompts for creating T-SQL scripts and returning remarks about the comparative performance of T-SQL scripts. Windows Copilot and Google Gemini are different in that they have different LLMs. The LLMs include text on a wide variety of topics, including T-SQL scripts. Therefore, you can input text prompts and expect feedback about T-SQL scripts, including code samples to accomplish a typical kind of task, as well as comparative remarks about two different scripts to accomplish the same task.

This section demonstrates how to formulate prompts to highlight the differences and similarities between Windows Copilot and Google Gemini for selected T-SQL queries.

Let's say that you want a query from Windows Copilot to return the top 10 customers based on customer_id values. The following screenshot shows a prompt to and response from Windows Copilot for a T-SQL query.

• The prompt asks for a query to display the top 10 customers by order value.
• Next, a response from Windows Copilot appears.
  • After a brief restatement of the prompt, the response lists the T-SQL code to find the top 10 customer_id values from the YourOrdersTable.
  • The SELECT statement for the query:
    • Starts with a TOP clause; the clause is set to return the top 10 customer_id values sorted by TotalOrderValue in descending order.
    • Aside from the TOP clause, the query has two select list items – namely, customer_id and an expression for the sum of ordervalue; the expression for the sum has an alias of TotalOrderValue.
    • The From clause designates the source for the query – namely, YourOdersTable.
    • The Group By clause indicates that results set rows from the query are for distinct CustomerId values.
    • The Order By clause indicates results set rows will be returned in descending order starting with the maximum TotalOrderValue.
• After the query statement, a brief text statement indicates what the code will accomplish. Also, there is an instruction on how to adapt the code to a different source dataset than the one in the YourOrdersTable.
• After the text statement describing the supplied query statement, there are some links for you to learn more about the prompt from the internet.

The following screenshot shows the same prompt submitted to Google Gemini. Notice that the prompt is identical to the preceding screenshot from Windows Copilot, but the query returned from Google Gemini is different.

• For example, the query statement from Google Gemini references the Customers table and the Orders table in its FROM clause.
• Also, an INNER JOIN operator matches rows from the Customers table to rows from the Orders table by Customer_id.
• Aside from adding the Customers table and the INNER JOIN operator, the two queries are the same. As a result, the query statements from Windows Copilot and Google Gemini return the same results sets presuming Orders from the Gemini query is identical to YourOrdersTable from the Windows Copilot query.

The next prompt and response compares the query design from Windows Copilot to the query design from Google Gemini. The top query is from Google Gemini, and the bottom is from Windows Copilot. After some minor editing for table names, the prompt begins by asking which query statement will run faster.

The response from Google Gemini has three answers to the prompt.

• It begins by asserting that the second query will likely run faster because it avoids the unnecessary overhead of a join between the Orders and Customers tables. The first answer also indicates that an index for the customer_id column in the Orders table will reduce the second query's run time.
• The second answer provides caveats to the first answer. If these caveats do not pertain to your query requirements or dataset values, then the first answer is best for your needs.
• The third answer starts by reiterating the main points from the first and second answers. Then, it concludes by suggesting that you test both queries.
• Relying on the first answer subject to testing from the third answer, you can conclude that, for these queries, Windows Copilot returns a faster-running query than Google Gemini.
• You can also use the general design of this approach to comparatively evaluate T-SQL query statements from Google Gemini versus those from Windows Copilot.

Comparing Performance for Identical Queries in Windows Copilot and Google Gemini

This section compares two identical queries for performance in Windows Copilot and Google Gemini, respectively. The objective is to assess if different generative AI applications will yield different responses about comparative performance.

The following screenshot shows the two query statements being compared in Windows Copilot. An identical prompt was submitted to Google Gemini as well.

• The first query begins by specifying a common table expression named TopCustomers. Then, it joins the common table expression to the Customers table. The results set columns are customer_id, customer_name, and total_order_value.
• The second query makes a single pass through the data instead of two passes through the data with the first query. The main reason for this difference is that the second query computes TotalOrderValue in a subquery within a select list instead of a common table expression, which must be joined to the Customers table.

Google Gemini starts its comparative remarks with this statement: In most cases, the second query will be faster. Towards the end of its performance appraisal based on design considerations, Windows Copilot asserts the performance difference between the two approaches may be negligible if the tables are large and well-indexed. However, both generative AI applications suggest testing with both approaches to determine which one works the fastest.

Next Steps

My experience using generative AI for more than a year has left me convinced that it provides valuable help quicker than internet searches. The content in responses from generative AI to prompts is often easier to grasp than from internet searches. Also, the feedback from generative AI can be shaped in ways that are specific to particular issues because of its prompt-response technology. Finally, the initial demonstration in this tip highlights how Windows Copilot can be adapted to search for and examine content to which a user has secure access instead of unprotected internet content.

You are urged to compare Google Gemini and Windows Copilot to other queries besides those analyzed in this tip. This may help you assess which generative AI application will best suit your needs. In any event, this tip and the prior one, which focuses exclusively on Google Gemini, may empower you to apply either or both generative AI solutions to your database development and analysis issues.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips