I Just Don’t Understand Why You Don’t Update SSMS.
A long time ago in a galaxy far, far away, SQL Server Management Studio was included as part of the SQL Server installer.
Back then, upgrading SSMS was not only a technical problem, but a political one too. Organizations would say things like, “Sorry, we haven’t certified that cool new SQL Server 1982 here yet, so you can’t have access to the installer.” Developers and DBAs were forced to run SSMS from whatever ancient legacy version of SQL Server that their company had certified.
These days, SQL Server Management Studio v22 has:
- A totally separate standalone installer
- A totally separate version numbering system (SSMS v22 as opposed to SQL Server’s year-based numbers)
- No designed-in dependencies (you can run new versions of SSMS on your desktop and connect to any supported version of SQL Server)
- A much, much, much faster release schedule than SQL Server
- Pretty few known issues – the list looks long at first, but if you go through ’em, few are relevant to the kind of work you do, and frankly, it’s still a shorter list than most of the previous SSMS versions I’ve used
- A lot more cool features than the old and busted version you’re running today
And current versions even have a built-in, kick-ass upgrade mechanism:
You should upgrade.
It keeps improving, quickly.
For example, SSMS v22.2.1 – a seemingly tiny version number change – just got a massive improvement in code completions. T-SQL code completion has never been great – IntelliSense doesn’t even auto-complete foreign key relationships. SSMS v22.2.1’s code completion will make your jaw drop.
For example, I never remember the syntax to write a cursor. It’s the kind of thing I don’t have to do often, and for years, I’ve used text files with stuff like this that I rarely (but sometimes) need quickly. With SSMS’s latest update, I just start typing a comment:
In that screenshot, see the different text colors? I’d started a comment and just written “Declare a cursor to” – and SSMS has started to fill in the rest. My goal in this case isn’t to loop through all the tables, though, so I’ll keep typing, explaining that I want to iterate through rows:
SSMS guessed that I wanted to iterate through the Posts table – and that’s SO COOL because SSMS actually looked at the tables in the database that I was connected to! If I try that same thing in the master database’s context, I get a different code completion!
Now, this does mean that Github Copilot & SSMS are running queries against your server in order to do code completion, and that they’re sending this data up to the cloud to do code completion. I totally understand that that’s a big security problem for many companies, and … okay, maybe I just answered that question about why some of you aren’t upgrading. But look, you can turn that feature off if you want, and you can track what queries it’s running if you’re curious. Let’s keep moving on through the task I have at hand today. I’m not trying to run through the Posts table, I need to do something else, so let’s keep typing:
uh wait what
In the words of Ron Burgundy, that escalated quickly. That is most definitely NOT what I’m trying to do, but that’s the state of AI these days. It’ll gladly help you build a nuclear footgun with speed and ease. Let’s continue typing:
(I don’t really need this specific thing, mind you, dear reader – it’s already built into sp_Blitz – but I’m just using this as an example for something a client asked me to do.) Now that I’ve clearly defined the comment, SSMS starts writing the code for me. I’m going to just tab my way through this, taking SSMS’s code completion recommendations for everything from here on out, just so you can see what it coded for me:
In a matter of seconds, just by hitting tab and enter to let AI code for me, it’s done! Not only did it write the cursor, but it wrote the dynamic SQL for me to do the task too. Now all I have to do is click execute, and:
This right here is the part where you expect me to make an AI joke.
But let’s stop for a second and just appreciate what happened. All I needed SSMS to do was just to build a cursor for me, and it went WAY above and beyond that. It wrote dynamic SQL too, because it understood that in order to get the right checkdb date, it has to be run inside dynamic SQL. That’s pretty impressive. I don’t mind troubleshooting some dynamic SQL that frankly, I probably would have written incorrectly the first time too!
Today, what we have is Baby’s First Code Completions. I can’t get angry about that – I’m elated about it, because we’ve never had code completions before, and now at least we have it! That’s fantastic, and it will absolutely make me more productive – in the places where I choose to use it, judiciously. I can’t rely on it to build whole tools for me out of nothing, but as an expert, using it to augment and speed things up, it’s helpful, period.
I expect it to get even better, quickly.
I’m not saying that because I’m optimistic or because I have inside information. Microsoft simply doesn’t have a choice, because the only AI model that SSMS v22.2.1 supports right now is GPT-4.1. That’s so old and underpowered that OpenAI is retiring it this month, so Microsoft is going to have to switch to a newer model – which will automatically give us better code completions.
You’ll see evidence of that in the code completion documentation, and under SSMS v22.2.1’s tools-options, under Text Editor, Code Completions:
Because I installed the AI components of SSMS, I get a dropdown for Copilot Completions Model. That’s the brains of the operation, the cloud AI model that comes up with the ideas of what you’re trying to code, and codes it for you.
Today, as of this writing, the only option is GPT 4-1, the old and busted one. I’m excited to see which one(s) we get access to next. Github Copilot’s list of supported models is huge, and it includes some really heavy hitters that produce spectacular results, like Claude Opus 4.5 and Gemini 3 Pro.
Side note – if you’re on the free Copilot individual tier, you only get 2,000 code completions per month for free. You’re gonna wanna check the box in the above screenshot that says “Show code completions only after a pause in typing” – otherwise you’ll keep getting irrelevant suggestions like how to drop all your databases, ha ha ho ho, and you’ll run out of completion attempts pretty quickly.
So do it. Go update your SSMS, make sure to check the AI tools during the install, sign up for a free Github Copilot account if your company doesn’t already give you a paid one, configure SSMS with your Copilot account, and get with the program. You’ll thank me later when it starts auto-completing joins and syntax for you. It’s free, for crying out loud.
40 Comments. Leave new
Until recently, I’ve agreed with you until I realized that the custom name property in the connection window in SSMS 21 and 22 was not automatically resetting for new connections and was even auto populating for connections made from the AG group replicas (see link below). This issue is dangerous and could easily have caused me to query or make changes to the wrong server – even if I never explicitly used the custom name property in the connection window. I’ve even tried switching the connection window back to classic mode in settings, but it still has the same behavior without showing you the custom name property (even worse).
https://developercommunity.visualstudio.com/t/10989190
Good news! Looks like that issue happened to get a private note just a couple days ago from Microsoft, which means someone’s looking at the issue.
Unfortunately, that comment was actually from me. Ha ha! I was just emphasizing to MS how dangerous the issue could be and hoping to keep it on their radar. I’ve had to send my own team a big warning about it in the meantime.
Local admin really the only reason. We don’t get the permission and so are relegated to old version until that day an admin feels sorry for us and gives us temp rights. Then you install every piece of software you can while that password is active.
HAHAHA, I’ve been there. That is absolutely true.
Yeah, I don’t get the reluctance to upgrade to SSMS 22. All older SSMS versions are officially out of support (meaning no new releases, not even for security issues).
https://learn.microsoft.com/en-us/ssms/support-policy
If folks are on SSMS 17 or 18, they should notice that SSMS cold startup time has improved quite a bit, which makes a big difference when SSMS is running on a low core count VM with a slow host CPU.
SSMS 22 has the Query Hint Recommendation Tool which is interesting (and free). Doing an assessment of databases that are running on an older version of SQL Server using the SQL Server migration component helps find upgrade issues and allay some fears about upgrading to a newer version of SQL Server.
Erin Stellato and Makena Barickman are doing a great job releasing new versions of SSMS and listening to feedback.
I use 22 daily and love it, but not in a rush to use any Github/Copilot features for the reasons you mention. Same with VSCode. I think once it’s easier to plug-and-play a local model into these tools it will be a no-brainer.
I’d usually agree coming from an infra/service desk background, but it feels like Microsoft have been rushing the version changes out the door and have been dropping the ball lately (see MS update breaking DB mail) so I’m actually pulling back on how up to date we are on SQL.
Also SSMS 21 released without SSIS support and still only has experimental feature support on that which is not great.
If they took the time to give us a full featured version on release of these versions, people would be more inclined to update. (that and redgate toolbelt lags a bit behind!)
Well, at least it was going to show mercy to the system databases so you know there’s a better chance that the code won’t throw an error. I’m intrigued as to whether it would’ve chucked a SET SINGLE_USER WITH ROLLBACK IMMEDIATE into the generated code for maximum droppage ¯\_(?)_/¯
In seriousness though, I only flirted briefly with SSMS 22 at my previous job and now work in a SQL Server-free estate for the first time in my career, but what I did see of it looked pretty cool. I’d disable all the AI stuff personally, but if the completion and shortcut engine in the actual IDE has been overhauled too then that’s a win — much as I love SQL Prompt it kinda sucks having to rely on paid third-party tooling to give you what the IDE should be capable of having baked in. Using Prompt for instance I’d have my own two-letter shortcut that could wrap a highlighted object name into a code block of cursor (with all the cursor options I’d tend to default to) + loop, and drop my (text) cursor into the right place to continue coding. Whatever the old SSMS’s snippets equivalent was never seemed good enough for actual rapid development work.
‘Going fast and breaking stuff’ is cool, until it affects 90% of all business infrastructure (Microsoft).
It only takes one ‘vibe coder’ firing his nuclear foot gun, or not spotting ‘that silly server association bug’ to create major destruction.
Fools rush in, and so on.
SQL Plan Explorer add in doesn’t work in SSMS 22.
Yes, AI and automatic code generation does seem like the nuclear foot gun to me. The built-in improvements to Intellisense are long overdue and I’ve worked around them with 3rd party add-ins, so it could be done, MS just never put the effort into it until AI.
I still use VS2017 but have later versions installed too. Why, you ask? Because I have very complex stored procedures with dynamic sql and need to be able to debug them with the debugger. MS removing the debugger from SSMS over numerous objections and little notice was a huge slap in the face to the development community. Some of the new features are starting to make up the damage.
I had SSMS 22 installed but was forced to uninstall and go back to version 20. Why? Because after (temporary) admin rights (using PIM) to our Sql-managed-instance expired, it was impossible to get the SSMS to show admin-stuff again (e.g. Sql-Agent). Even rebooted my laptop several times.
https://developercommunity.visualstudio.com/t/No-longer-able-to-connect-to-sql-agent-i/11033486
Hi Brent.. SSMS doesn’t have many of the features I need when I get stuck with cleaning up a database mess and the First Responder Kit is a bit archaic and 30 years old tech. You have some great stuff but it’s slow and difficult to use so I have been FORCED to write my own tools.
Writing a tool right now that looks at Query Store and grabs every table and stat related to user tables so I am ignoring the SQL Server system queries along with those that just go against the Microsoft DMVs.
With ad-hoc queries I am extracting parameters for those queries and then based on the stats I can give you a parameters that probably would give you a better overall performance so you can drop all the existing plans and run the new plan with the parameters that I have suggested.
The other thing I can show you is for any given index, how many queries are hitting that index and their execution counts (aggregated or by Execution Plan) so I can more quickly tell you make a suggestion to somebody like you that I think we can get rid of “Index X” and these are the queries using that index and the number of times those queries that are executed.
I can also tell you all the different Queries that are the exact same query but with additional whitespace or different parameters. I can group those queries together and give you numbers based on Ad-Hoc. What is nice is that I can run the app I am currently working on and save the results and place those results on a shared drive and you can see my data without having to crank the data again.
SSMS is a good tool but if you are a dev in a small shop it doesn’t provide you the tools that you need to clean up a mess that didn’t have proper oversight so that developers can add any index they want whenever they want.
My update schedule for SSMS used to be very prompt barring a bug they introduced that broke something critical. But the new visual studio installer has made it absolutely horrible to install or update in an airgapped environment
I fully agree. I am working for a hosting company and DB-Server do not have internet access. THe .exe was about 500MB
Up to SSMS 20 I can copy the .exe to the respective Servers and install it remote using SQLServerDSC. That even works for removing old versions in case of an update
With VS Installer, the package is now 2 GB and remote installation is, as far as I have experienced, not working
Maybe someone can tell me: just run yxz and it works…
100% agree here, the new Copilot AI autocomplete in SSMS is amazing! (as it is in Visual Studio) It blows away Red Gate SQL Prompt’s AI functionality, which I found useless.
Even better: Get your database schema and code into a git repository. (however you wish) Preferrably alongside your other code. Like I typically have a big Visual Studio solution with all the projects and code for a customer engagement (C# libraries, web sites, function apps, etc) and then add a database project which copies all the SQL code into it. Then, go to Github Copilot (on the web site) and tell the agents there what you want. New features; changes; bug fixes; security checks; code reviews; whatever. It’ll generate all the code. HTML, C# backend, SQL, everything. And it’ll generally do a very very good job of it.
What are the implications of Copilot “looking through” the database? Performance wise? I’ve seen a number of tools cause issues like blocking by simply querying system tables.
There was dependency in SSMS 2016 and below where different versions were not able to connect to integration services. Was that fixed in 1x.x and 2x.x versions? I’m just operational DBA and do not have any touch with contents of reporting or integration services.
[…] Brent Ozar wants an update: […]
Note, For pro users, premium models like claude opus and gpt-codex are available (has to be enabled)
Really curious how it compares with RedGate SQLPrompt AI+ . Company is only letting us use copilot for privacy reason but Redgate has been phenomenal with their intellisense historically
I’m a pro user, and it’s enabled in my Github Copilot profile. Those models still don’t show up, so I’m assuming that’s because the documentation explicitly says only GPT 4-1 is included, as I wrote in the post.
Do you have a screenshot of the dropdown showing other models? And if so, how specifically did you enable that? Thanks!
Gabriel was apparently on the bleeding edge of SSMS. Claude Opus was added to the preview documentation in January.
Preview Documentation – https://learn.microsoft.com/en-us/ssms/github-copilot/ai-models
Github Commit that added Claude Opus – https://github.com/MicrosoftDocs/data-tools/commit/be713ff18ec678db761e8d53892a31bb372fb5b9
In fairness, I do get Microsoft commenters here who use personal emails to comment. He might well have been an MSftie.
I use mostly Claude Opus lately in Copilot. I did briefly try Red Gate’s SQL Prompt AI and was super disappointed. It’s no where near as smart or magical as Copilot. You aren’t missing anything.
Impacted by your post, I decided to finally upgrade. Configured SSMS v22 to be more like my SSMS v20:
– font of the query text (set to Consolas), size (8 to 9)
– color of grid lines in grid results. This one is important! SSMS v22 has irritating visible grids by default.
– reduced color of NULLs to be less contrasting
Those little things were something that kept me from upgrading. Now its more usable. There are still annoying things and differences from SSMS v20, but I guess just need to get used to those.
Once upon a time (probably 2017) there was a lovely customer who sent me a screenshot of her SSMS with an “Index out of bounds” error message, which she said popped up whenever she tried to go to object explorer. She was asking me how to fix it.
The answer was to stop using SSMS 2005 to access SQL2017.
Please do not use SSMS 2005. SSMS 2005 was lovely in 2005, but now it’s old enough to drink, drive, and vote. Hopefully not in that order.
For the longest time, there was a great registry hack for enabling thumbnails when you went to switch open script windows using Ctrl+Tab. If you held down the Ctrl key and cycled with TAB, you got a nice thumbnail picture of the script as it looked at the time, with possible results window or execution plan, etc. SUPER handy.
But in SSMS 21 and 22, options are not stored in the registry anymore. They are hidden deep in the bowels of your app settings folder (I think). I put in a feature request if anyone is willing to lend their vote:
https://developercommunity.microsoft.com/t/Add-Thumbnails-to-fast-switching-using-C/11029077?port=1025&fsid=733f264e-d156-4fca-8d16-b887afc0ed18&ref=native&refTime=1770760429460&refUserId=e1e51c5f-f412-6bc3-a83f-18e931aeaf72
@Katherine: Maybe your customer’s SSMS 2005 could log in and vote for it???
I’m sticking with SSMS20 for the time being.
I’ve installed SSMS21 and SSMS22 and they both have (as far as I’m concerned) the same bug. Yes, I now have all 3 versions installed side-by-side.
I use server groups to connect to multiple instances at once (2012 (yes I know, I know), 2016 and 2022).
At least one of the them stick on “expanding” forever (which server it is seems random) when first connecting and I can’t do much with that instance.
I can still run code/queries, etc but I can’t *see* anything.
I’ve done a lot of searching (and even ChatGPT’d the problem) but there’s no workaround/fix for it (unless anyone knows better).
If you have a “fix” for this issue, please, *please*, let me know.
Until that’s fixed, I’ll stick where I am thanks.
Richard
I want to use 22!
Every morning I open all my servers by right clicking on “Local Server Groups” in the “Registered Servers” window.
In 21 all servers connect, always. In 22 not. There are always 1 or 2 or more servers which keep “expanding”, very buggy.
That’s why I keep using 21.
I can’t get Ctrl-W reprogrammed to Select Word in SSMS 22. That is why I haven’t switched. Works fine in 21.
Why don’t people “just upgrade”? Welcome to big organisations, where the people using software who may want to upgrade cant just install whatever they like. Organisations control what versions of software you’re allowed to have installed, and are often very, very slow to roll out newer versions. And i cant blame them for being cautious.
There needs to be a way to unsubscribe from a thread once it’s gone off the rails… 😉
In our environment, we have been told to stick to the version of SSMS that works with SSIS. We also have been told by our official DBAs that SQL 2022 should be using SSMS 19.3. Many of our servers are still running 2019 which is supported by SSMS 18.12.1. Some of our servers are finally getting off of SQL 2016!
I have even been told by our DBAs that newer versions of SSMS require certs to be installed on all the db servers. We have hundreds of SQL servers in our environment and not enough DBAs to update those certs. I have not had issue with this on SSMS 18 because I just tell SSMS to trust the server or make the cert optional.
Because of these perceived “issues” newer versions of SSMS have not been approved by our corporate software approval process. Any input or data anyone has one this would be appreciated. I would love to use a more current version and have it be “approved”.
Did you ask them for proof?
I’m sorry, proof of which? I know from personal experience that I’ve had issues connecting to SSIS on SQL 2016 with newer versions of SSMS. I have to run the SQL 2016 version of SSMS to connect to SSIS properly.
Anytime anyone tells you something as the gospel truth.
We are a very government-regulated organization. In talking with one of our official DBAs, I think they are too busy to go through our required Software Quality Assurance (SQA) process to get updated tools like SSMS approved. We have to go through one board to get approval to even pilot test a product. We then have to go through an SQA process, including software testing and the associated documentation, to get to the next step. That final step is to then present all of these approvals to a production review board prior to pushing it out to the general population and even then, the software usually has to be installed via a local software distribution tool (usually based on the vendor-supplied installer). Change is slow here (unless you want to break the rules and just install what you want from the Internet – a big no-no officially).
Is copilot smart enough to understand all the database on a server or still limited to the actual connected one ?
And how about multiple agents analyzing multiple servers in a server group.
One approach would be to run it and find out.
Another approach would be to ask me to do your work for you.
I’ll give you some time to think about which one is likely to be more successful.
Cheers!