Jeff Taylor
Principal Data Consultant
Database Consulting, LLC
Your Daily Driver
Got an Overhaul
What's new in SSMS 21 and SSMS 22
JaxData.org
Every 3rd Wednesday 6-8pm
Except for May, November and December
Jeff Taylor
Principal Data Consultant
Database Consulting, LLC
Super Hero Suit
Questions
- What is your role?
- Which version of SSMS do you use?
- Have you tried GitHub Copilot in SSMS yet?
The route
- SSMS 21 - the rebuild, all the new features
- The AI false start - Copilot in 21, and why it changed
- SSMS 22 - GitHub Copilot, Schema Compare, formatting, and more
- Gotchas and Tomorrow's plan
SSMS 21
The rebuild: new engine, new chassis
Finally, 64-bit
The engine
- The old pain - 32-bit SSMS ran out of memory on big scripts and huge result grids
- SSMS 21 - a 64-bit application on the Visual Studio 2022 shell
- What you feel - fewer out-of-memory errors, smoother on large workloads
New installer, automatic updates
Pit crew
- Visual Studio Installer - install and update SSMS the modern way
- Automatic updates - stop hunting for the download link every few months
- Workloads and Components - install only what you need, smaller footprint
Dark theme
Demo
Object Explorer, Query Editor, Results, Template Explorer.
Yes, this is the one you will get asked about most.
Tabs that work the way you think
Cockpit
- Vertical tabs - see 20 open windows down the side, not crammed across the top
- Tab coloring - color by project or file type, so prod and dev look different
- Tab width - set a min and max so titles stay readable
Results grid options
Dash
- NULL coloring - give NULL cells their own font and background, spot them instantly
- Grid font - set it once, read it from the back of the room
Modern connection dialog
Connect
- Refreshed connect experience - clearer, faster to your servers
- Opt-in preview in 21 - the classic dialog is still the default here (that changes in 22)
SQL Projects
Projects
- Database as code - your schema lives in a project, in source control
- Build and publish - deploy from a known-good definition, not ad hoc scripts
Always Encrypted Assessment
Security
- In the wizard - assess columns right from the Always Encrypted Wizard
- Why it matters - the banks and healthcare shops in the room have asked for this
Copilot in SSMS, take one
AI, v1
- The first Copilot - built on Azure OpenAI, you brought your own endpoint and keys
- Honest take - cumbersome to set up, and clunky to use
- Good news - Microsoft reworked it in 22, so that is the version we will spend time on
A turbo that never quite spooled.
SSMS 22
Race day: the tune that works
Connection dialog, now the default
Tech inspection
- Now the default - the modern dialog replaces the classic one
- Strict encryption (TDS 8.0) - the secure default for new connections
- Gotcha - no valid certificate means a connection error the first time you connect
Windows Arm64
Arm64
- Native Arm64 build - runs natively on Arm64 Windows, a long-requested one
GitHub Copilot, the retune
AI, v2
- Out with the old - Microsoft dropped the Azure Copilot for GitHub Copilot
- Just sign in - a GitHub account, no endpoints or keys to wire up
- Chat and completions - reached general availability in 22.4.1
Chat: write, explain, fix
Demo
-- Prompts I actually use in the chat window
Write a query to find the top 10 customers by total order value
Explain what this stored procedure does
Why is this query slow, and how would you rewrite it?
Add error handling and a transaction to this scriptEditor autocompletions
Autocomplete
- As you type - Copilot suggests the rest of the line of T-SQL
- Tab to accept - like IntelliSense, but it understands intent
- Heads up - completions ship off by default since 22.5.2, switch them on in options
Agent Mode (preview)
Demo
- You say - hand it a goal, not a query (SSMS 22.7)
- It works the problem - runs queries, reads execution plans, proposes schema changes
- You stay in control - READ_ONLY by default, it asks before it changes anything
Powerful, and worth respecting
Agent Mode
Let it work. Read every step.
Treat it like traction control: it helps, but you are still driving. Agent Mode can execute queries and modify schema, so approve deliberately, especially outside dev.
Schema Compare (preview)
Demo
- Compare - databases, .dacpac files, and SQL projects (SSMS 22.7)
- Save as .scmp - recurring compares become a single click
- SQLCMD variables - now supported in the publish dialog
T-SQL formatting (preview)
Before
select o.OrderId,c.Name,sum(od.Qty*od.Price) total from Orders o join Customers c on c.Id=o.CustId join OrderDetail od on od.OrderId=o.OrderId group by o.OrderId,c.NameUSE [SSMSDemo];
GO
-- BEFORE: paste this as one line, then run the formatter live.
select o.OrderId,c.Name,sum(od.Qty*od.Price) total from Orders o join Customers c on c.Id=o.CustId join OrderDetail od
on od.OrderId=o.OrderId group by o.OrderId,c.Name
GOOne command, readable again
After
SELECT
o.OrderId,
c.Name,
SUM(od.Qty * od.Price) AS total
FROM Orders o
JOIN Customers c ON c.Id = o.CustId
JOIN OrderDetail od ON od.OrderId = o.OrderId
GROUP BY
o.OrderId,
c.Name;-- AFTER: what the formatter produces (shown here so you have the target).
SELECT
o.OrderId,
c.Name,
SUM(od.Qty * od.Price) AS total
FROM Orders o
JOIN Customers c ON c.Id = o.CustId
JOIN OrderDetail od ON od.OrderId = o.OrderId
GROUP BY
o.OrderId,
c.Name;
GOGrid zoom and status-bar aggregates
Demo
- Zoom the grid - hold Ctrl and roll the mouse wheel to zoom results in and out
- Status-bar aggregates - select numbers in the grid, see SUM, COUNT, AVG at the bottom
JSON gets easier
JSON
- JSON index wizard - create an index on JSON data through the UI, no T-SQL
- View JSON in the grid - open and read JSON values right from the results
Fabric integration
Fabric
- Warehouses and SQL endpoints - work Fabric objects from SSMS
- Nicer browsing - organize by schema, query snapshots, context-aware menus
Odds and ends
More
- Linked server wizard - a streamlined create flow with encryption options
- Import settings - carry over registered servers, shortcuts, and preferences from a prior version
- SQL projects in the main UI - with Git branch status in the status bar
Keep in mind
Gotchas
- Strict encryption default - a cert error on first connect can surprise you
- Previews are previews - Schema Compare, formatting, and Agent Mode
- Copilot completions - off by default since 22.5.2, turn them on
- Stay current - 22.7.1 fixed memory and hangs on large scripts, take the update
Green flag, Tomorrow
Recap
- Move to SSMS 21 for 64-bit and dark mode
- Then SSMS 22 for GitHub Copilot and the DevOps tools
- Turn on completions, try Agent Mode in dev
- Expect Strict encryption, fix your certificates first
Resources
Questions?
Thank you!
Jeff Taylor
Thank you for attending my session today.
If you have any additional questions, please don't hesitate to reach out.
What's New in SSMS
By reviewmydb
What's New in SSMS
Your daily driver got an overhaul. A full walk through SSMS 21 (the rebuild) then SSMS 22 (race day): GitHub Copilot, Schema Compare, T-SQL formatting, Strict encryption, and more. By Jeff Taylor (@ReviewMyDB).
