SQLServer Management Studio(SSMS) 22 Copilot 最佳实践学习笔记摘要在最新的SSMS 22版本中加入了微软官方的AI工具GitHub Copilot。此篇结合微软官方的文档汇总了我们作为机长应该如何跟这位副驾磨合。一、SSMS Copilot 是什么SSMS 中的 Copilot 是一种高级 AI 工具帮助 SQL Server Management Studio 用户管理和开发 SQL 数据库。适用角色包括数据库管理员DBA数据库开发人员应用程序管理员业务分析师核心能力回答有关 SQL Server、Azure SQL 数据库、Azure SQL 托管实例、Fabric 中 SQL 数据库的问题用自然语言编写 T-SQL 查询NL2SQL帮助解决错误消息、记录、解释和修复 T-SQL 查询二、六大使用场景场景 1数据库和环境探索在边车聊天Sidecar Chat中随意提问Copilot 会基于当前连接的数据库上下文回答。示例提问提问方向Prompt 示例版本信息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?表大小Whats 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 failed in the last week and if so, which ones?场景 2多阶段体验逐步深入提问每个后续问题基于前面的回答展开——像跟一个懂行的同事对话。示例对话链1. What is the compatibility mode for this database? 2. What is the latest compatibility mode available for this version of SQL? 3. How do I change the compatibility mode? 4. How will this change affect query performance? 5. 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.关键技巧从宏观到微观从是什么到怎么做到如何验证逐步缩小问题范围。场景 3使用脚本的多轮次体验在多轮对话中不仅提问还要求 Copilot 生成可执行的脚本。示例对话链1. What is a database backup? 2. What is the difference between a full and log backup? 3. How do I create a database backup? 4. Does my database need a transaction log backup? 5. How do I create a transaction log backup? 6. Create the script to backup the database ← 生成脚本 7. How do I automate backups? 8. What should my backup schedule be if the RPO is 30 minutes? 9. Create the script to create SQL Agent jobs for the recommended backup schedule ← 生成自动化脚本关键技巧先理解概念 → 确认细节 → 要求生成脚本 → 进一步自动化。知识递进脚本也递进。场景 4编写 Transact-SQL用自然语言描述需求Copilot 生成 T-SQL 查询。⚠️ 重要限制Copilot 具有基于查询编辑器连接的上下文但无法直接访问查询编辑器在空白编辑器中输入不会启用 Copilot 辅助功能Copilot 当前无法直接从编辑器读取或复制内容示例提问需求类型Prompt 示例基础查询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场景 5多轮次查询开发像自己写查询一样逐步引导 Copilot 优化查询。示例对话链1. How many customers spent over $100000 in 2023? 2. Give me the query you ran to find that information 3. Take the inner query and change it to select customer ID, customer name, and total spent and order it by total spent descending 4. I forgot I want to include the state where the company is located in the output, please update the query to add that关键技巧先问结论再要查询再逐步修改——这种迭代方式比一次性描述完整需求更高效。场景 6读写模式与数据库开发默认情况下 Copilot 以只读模式运行仅执行读取数据的查询。通过命令切换模式命令模式说明默认只读只能执行 SELECT 查询/rwa读写需审批执行写操作前会询问确认/rw读写可直接执行写操作审批流程示例更新统计信息1. /rwa ← 切换到读写审批模式 2. Update statistics that are out of date ← 请求更新 3. List the name of statistics that havent 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 更新统计信息数据库开发示例1. /rw ← 切换到读写模式 2. Were designing a database for a fictional company called Contoso Wireless. They are similar to existing wireless companies...Can you suggest some tables that will support my application? ← 请求设计表结构 3. Are there any indexes you think I need based on the kinds of queries that we will run? ← 请求创建索引 4. We need to generate data for each of the tables. Each table should have 100-1000 rows... ← 请求生成测试数据三、聊天窗口最佳实践❌ 别把聊天窗口当结果窗口Copilot 不会在执行前判断返回行数。如果你问的查询返回 1000 行Copilot 会试图全部显示在聊天窗口里——结果不可读、不可操作。推荐做法❌ 不推荐✅ 推荐How many customers are in each state?可能返回海量行Write a query to return customer count by state让 Copilot 直接执行并显示结果让 Copilot 生成查询脚本你在编辑器中执行四、Prompt 编写最佳实践4.1 理解不一致性AI 回复存在不一致性是正常现象——因为模型是概率性生成而非确定性计算。同样的提问可能得到不同但都合理的回答。减少不一致的关键是优化 Prompt 的精确度。4.2 五大原则原则说明❌ 避免✅ 推荐使用自然语言避免俚语、行话、模糊表达Yo cook me up something cool for grabbing sales dataWrite a SQL query that selects the top 10 most recent orders from the Sales.Orders table清晰具体提供足够细节让 Copilot 明确意图Okay, so Ive got this thing where I need to maybe get some kind of report...Write a query to return users who registered yesterday and have not yet made a purchase提供上下文给出时间范围、具体对象等额外信息List western schools with no language reqsList schools in the US-West geo that have a lang_req value of 0使用示例基于前一轮回答迭代优化Write a query to get recent customer infoWrite a query to get customer ID, name, and total number of orders for the last 10 business days ordered by total desc定义输出格式指定文本、表格或列表格式Give me hospital locations and size with the busiest emergency roomsList the hospitals with the busiest emergency rooms in table format and include city state and size4.3 避免模糊表达❌ 模糊 Prompt问题✅ 精确 PromptFind the hottest tables in the database“hottest” 有歧义最忙最热数据List the top 10 tables in the database that have the most reads4.4 其他实用 Prompt 示例-- 空间管理 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 employees 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.五、速查卡片操作模式切换命令模式适用场景默认只读查询、探索、分析/rwa读写需审批更新统计信息、修改配置/rw读写创建对象、生成测试数据对话策略探索阶段是什么 → 为什么 → 怎么做 开发阶段概念 → 脚本 → 优化 → 自动化 调优阶段问题 → 定位 → 修复 → 验证始终记住⚠️在执行之前始终审查 Copilot 生成的脚本因为 AI 可能会出错。基于微软官方文档整理来源使用场景 · 最佳做法