I generally write T-SQL code in SSDT (Sql Server Data Tools) and find that I often publish to my local database instance and then test the actual code in SSMS, or at least debug the code in SSMS - whether this is using the Sql debugger or modifying procs to output additional data (debugging Sql is so old fasioned!).

It occured to me that I am wasting time switching between windows when SSDT should do everything I want already so I am investigating whether I can move away from SSMS to just SSDT and Visual Studio - is it possible? Is it better or worse?

What do I use SSMS for?

If I look at what I commonly use SSMS for, it is:

• Running queries
• Briefly looking at execution plans (anything more involved is done in Sql Sentry)
• Starting Sql Profiler
• Debugging queries (which can involve modifying and them and re-running them)

I also occasionally use it to:

• Configure / View Extended Event traces
• Perform managment tasks like taking backups, restoring backups, creating logins / checking logins exist etc

Please note I am currently a developer and not a dba so my time is really spent writing and testing code, rather than maintenance etc.

Replacements

Running queries

SSDT comes with a query window, which is very similar to the query window in SSMS, this lets you parse the query, view estimated plans, send the output to text, grid or file and also has the familiar messages tab so really it does everything I need to run queries:

I prefer to use a keyboard when programming, so I have using the Tools–>Options–>Keyboard menu in Visual Studio to run queries when I press F5 - you can make the mapping only apply to the query window so it doesn’t change the default F5 behaviour:

Looking at execution plans

The query window lets you view execution plans and the ui looks, at least to me, exactly like the SSMS ui so that is all good.

Starting Sql Profiler

The tools menu has no option to start profiler, but it is easy to extend the list of existing tools and add it:

Debugging queries

There are two ways to debug queries, the first way is using the debugger which works in SSDT just like it does in SSMS.

The second way, is to edit code (procedures and functions etc) and run alter scripts or drop/create scripts and then running the newly created objects - this again works exactly the same in SSDT that it does in SSMS although I am going to attempt to just update the original object definitions and then publish them, either by doing a full publish or by using the Visual Studio add-in I wrote to just push out stored procedures bypassing the build / publish phase.

There is one additional feature of SSDT which I will also try using, if you use the “Sql Server Object Explorer”, right click on your database and choose “New Object” - this gives you a window with an “Update” button which lets you write create statements which are deployed using SSDT, kind of like a really filtered compare and deploy:

Less common operations

I am not really sure how I will do some of the less common operations, for things like backups I can manually write scripts pretty fast (I used to be a DBA so “CREATE BACKUP…” is ingrained!), I don’t really fancy learning the syntax of every command especially command for creating extended events etc.

I guess I will see what happens and how often I find myself reaching for SSMS.

Finally

I started today and found it did actually make testing and debugging quicker, I also found it better staying within one UI although I have been using SSMS so long now I kept trying to open it or switch to it - I guess that will get easier in time.

To clarify, I am not saying that there is anything wrong with SSMS, I just prefer SSDT for development and want to see if I can use that more instead of relying on my old friend SSMS! If you like SSMS and it does what you want, great!

---

Comments:

Justin Wignall

September 15, 2017 - 08:56

Thanks for the F5 tip -

Thanks for the F5 tip - exactly what I was looking for! I’ve mapped Ctrl-N in the query editor window to SqlServerObjectExplorerNewQueryOnToolbar too which was another little sticking point.