共用方式為

SQL Server Management Studio 中 Copilot 的使用情境

SSMS 中的 Copilot 是一種進階 AI 工具，其設計目的是協助 SQL Server Management Studio （SSMS） 的使用者管理及開發 SQL 資料庫。 無論您是資料庫管理員、資料庫開發人員、應用程式管理員、商務分析師，還是介於兩者之間，SSMS 中的 Copilot 都可以節省您的時間，並協助您在工作流程中更有效率。

在 SSMS 中使用 Copilot 回答 SQL Server、Azure SQL Database、Azure SQL 受控實例或網狀架構中 SQL Database 中資料庫的相關問題。 您也可以使用 自然語言 （NL2SQL） 要求撰寫 Transact-SQL （T-SQL） 查詢的協助。 Copilot 也可以協助處理錯誤訊息、記錄、說明及修正 T-SQL 查詢。

資料庫和環境探索

在側車聊天中，以任何順序詢問科皮洛特任何問題。 例如：

  • 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 可以使用您在側欄聊天中提供的提示來協助撰寫 T-SQL（將自然語言轉換為 T-SQL 或 NLtoSQL）。

  • 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.

相關內容