通过

SQL Server Management Studio 中 Copilot 的使用场景

SSMS 中的 Copilot 是一种高级 AI 工具，旨在帮助 SQL Server Management Studio （SSMS） 的用户管理和开发 SQL 数据库。 无论你是数据库管理员、数据库开发人员、应用程序管理员、业务分析师，还是介于两者之间，SSMS 中的 Copilot 都可以节省时间并帮助你在工作流中提高效率。

使用 SSMS 中的 Copilot 回答有关 SQL Server、Azure SQL 数据库、Azure SQL 托管实例或 Fabric 中的 SQL 数据库的问题。 还可以请求有关使用 自然语言 （NL2SQL）编写 Transact-SQL（T-SQL）查询的帮助。 Copilot 还可以帮助解决错误消息、记录、解释和修复 T-SQL 查询。

数据库和环境探索

在边车聊天中按任意顺序询问 Copilot 任何问题。 例如：

  • What version of SQL is this?
  • List the databases on this instance.
  • List server configuration settings that have been changed from the default in table format with the setting and current value
  • How do I find out who is connected to this database?
  • What is the compatibility mode for the database?
  • hat's the largest table in the database?
  • What columns store email addresses?
  • How many customers do we have and where are they located?
  • Have any SQL Agent jobs have failed in the last week and if so, which ones?

多阶段体验

询问 Copilot 一系列问题，后续的每个问题都与先前的答案相关或是对其的回应。 例如：

  • What is the compatibility mode for this database?
  • What is the latest compatibility mode available for this version of SQL?
  • How do I change the compatibility mode?
  • How will this change affect query performance?
  • Give me step-by-step instructions for testing a change in compatibility mode for a query without changing the compatibility mode for the entire database.

使用脚本的多轮次体验

在询问 Copilot 一系列问题时，还可以要求它创建与主题相关的查询。

  • What is a database backup?
  • What is the difference between a full and log backup?
  • How do I create a database backup?
  • Does my database need a transaction log backup?
  • How do I create a transaction log backup?
  • Create the script to backup the database
  • How do I automate backups?
  • What should my backup schedule be if the RPO is 30 minutes?
  • Create the script to create SQL Agent jobs for the recommended backup schedule

使用 Copilot 获取关于 Transact-SQL 的指导帮助

SSMS 中的 Copilot 具有基于查询编辑器连接的上下文，但目前无法直接访问查询编辑器。 具体而言，在空白编辑器中键入不会启用 Copilot 辅助功能。 此外，它当前无法直接从编辑器读取（如果突出显示了某些内容）或将某些内容复制到编辑器中。

编写 Transact-SQL

Copilot 可以使用在 sidecar 聊天中提供的提示（向 T-SQL 或 NLtoSQL 提供自然语言）帮助编写 T-SQL。

  • Write a query to return sales information for the last week
  • Write a query to find all the customers who placed orders in July 2024 that totaled more than $1000 and order based on total descending
  • Write the query to change the ReferenceID column in the Tickets table from an INT to a VARCHAR(25)
  • Give me the query to find how much space is being used in tempdb
  • Give me the query to find all open transactions

获取将 T-SQL 查询编写为多轮次体验的帮助

除了向 Copilot 请求有关编写查询的帮助之外，还可以在聊天中请求查询开发方面的帮助，就像自己编写查询一样。

  • How many customers spent over $100000 in 2023?
  • Give me the query you ran to find that information
  • Take the inner query and change it to select customer ID, customer name, and total spent and order it by total spent descending
  • I forgot I want to include the state where the company is located in the output, please update the query to add that

批准执行 Copilot 的查询

默认情况下，Copilot 以只读模式运行。 Copilot 仅执行读取数据的查询。 但是，可以使用聊天中的命令将模式更改为“通过审批/rwa读取/写入”。

  1. 更改模式：

    /rwa

  2. 要求 Copilot 更新过期的统计信息：

    Update statistics that are out of date

  3. 为 Copilot 提供更多详细信息，以便它可以执行正确的分析：

    List the name of statistics that haven't been updated in over a week that have more than 10% data changed for all tables in the database

  4. Copilot 列出了统计信息，并询问是否要更新它们。 如果未询问，可以告知它更新统计信息。

  5. Copilot 为更新提供 T-SQL，并提供选择“ 运行 ”或“ 取消”选项。

  6. 选择“运行”让 Copilot 更新统计信息。

使用 Copilot 进行数据库开发

Copilot 可帮助你在数据库中以 读/写 模式快速开发对象。 若要启用 读/写 模式，请使用 /rw 命令。

  1. 更改模式：

    /rw

  2. 请求 Copilot 为数据库创建一组表：

    We're designing a database for a fictional company called Contoso Wireless. They are similar to existing wireless companies in the sense that they sell cell phones, accessories, phone plans, chargers, headsets, and more. Can you suggest some tables that will support my application? This application will support browsing a website, creating orders, paying bills, tracking customer activity, tracking usage, etc. Please provide scripts to create all the tables you see fit and make sure each table has a clustered index for the primary key.

  3. 要求 Copilot 创建索引：

    Are there any indexes you think I need based on the kinds of queries that we will run to find data?

  4. 请求 Copilot 创建示例数据：

    We need to generate data for each of the tables. Each table should have 100-1000 rows. The data needs to be realistic (we can't repeat names, products, etc.). Can you help generate scripts for that? It's ok if the scripts are long. Variability in the data is more important than having a short script. For the customers table, use a combination of 50 first names and 50 last names and also use 30 different city/state combinations.

其他示例

可以询问 Copilot 有关 SQL 或编写 T-SQL 的任何问题。 在执行之前，请始终查看脚本，因为 AI 可能会出错。

  • List the 10 largest tables in the database based on row count in a bulleted list

  • What are the file sizes for this database, their file growth settings, and how much free space do they have?

  • What are the worst performing queries for the last hour?

  • What queries executed most frequently in the last two hours?

  • List all employes and their email address in comma delimited format

  • What are the top 5 products ordered the most in 2024?

  • Calculate the total number of orders for each company in 2023

  • Create a table named Offices in the Sales schema. It should have the columns officeID, office name, address, city, state, zip code, and office manager ID which is foreign key to the People table.

相关内容