Press "Enter" to skip to content

Category: SQL Server Management Studio

Building an SSMS Database Solution

Published 2022-01-04 by Kevin Feasel

Andy Leonard has a four-parter four us on database solutions in SQL Server Management Studio. Part one provides an introduction:

I like Microsoft Visual Studio a lot. I know some members of the team that developed Visual Studio, and they are scary-smart individuals who have forgotten more about developing software than I will ever know.

For some reason, I am not fond of SQL Server projects in Visual Studio. I believe the reason is that I am not familiar with the template. Please note I used the word fond intentionally. It’s an emotion. In this case, it’s all about me. I believe my emotion would change if I took the time to learn more about the Visual Studio SQL Server project template.

I continue to attempt to learn VS database projects. In the meantime, I prefer SQL Server Management Studio solutions.

Part two shows how to add a new query:

One solution is to add instrumentation to T-SQL scripts. I personally like to write T-SQL scripts that idempotent (a fancy way to describe “re-executable with the same results”). One way to write idempotent T-SQL is:

1. First check for the current state

2. Provide feedback (instrumentation) on the status

3. Provide more feedback on actions driven by the status (yep, more instrumentation)

Part three includes tables and views in the mix:

Click the “New Query” button in SSMS and add the following T-SQL:

Part four includes stored procedures:

Note the DDL to manage stored procedures is very similar to the DDL for managing views.

If all goes according to plan, the first execution of the s.i DDL T-SQL statement should generate the following messages:

Andy also shows how to use SQLCMD to create a proper deployment script.

Comments closed

Changing Case in SSMS

Published 2021-12-15 by Kevin Feasel

Steve Jones has a quick tip for us:

I never knew I could change case for objects in SSMS easily. This actually was something that another individual pointed out to me, but once I tried it, I liked it and know I’ll use it at times.

Click through to see how to change your code to lower-case or upper-case in a single command.

Comments closed

Page Allocation Reports in SSMS

Published 2021-10-26 by Kevin Feasel

Eitan Blumin has updated an open source project:

Back in April 2020, I created an open-source project called “SQL Server Page Allocation Reports“. It consisted of a set of SQL queries and some Power BI reports that can be used for visualizing the size and locations of your data and transaction log pages.

Well, recently I also added SSMS Custom Reports into the mix. So, it’s time to revisit this project and see what’s new!

Click through to see what’s new.

Comments closed

The Alt Key in SSMS

Published 2021-09-29 by Kevin Feasel

Barney Lawrence shows off vertical selection in SQL Server Management Studio:

I went years without knowing how useful the Alt key was, it’s not well documented but I can guarantee that once you know about it and give it a little practice you won’t be able to live without it.

While I’m filing this under SSMS Tips and Tricks but it works equally in Visual Studio, VS Code, Azure Data Studio and even Notepad ++ (but not plain old vanilla notepad). It’s worth a test in other places too.

As a quick note, it does not work quite the same way in Visual Studio Code or Azure Data Studio as in Visual Studio or SSMS: Alt+Shift+Down arrow copies the current row and pastes it in the row below. Holding Alt and Shift while using the mouse does work, but if you’re big on keyboard commands, you’ll be a bit disappointed.

Comments closed

Toggling Word Wrap in SSMS

Published 2021-09-27 by Kevin Feasel

Ronen Ariely shows how to enable word wrap in SQL Server Management Studio:

Line breaking, also known as word wrapping, is breaking the displayed of a section of text into lines so that the text will fit into the available width of the editor. When writing queries this feature is not so useful as breaking the script line may make the query less readable, but when writing long comments this feature become one of the most useful feature. 

This post simply shows you how to use word-wrap by default or add a command button to Toggle Word Wrap – it’s a built-in feature which is less known and if you did not used it yet, then it is time to use the power of word wrap

Because T-SQL is not line or whitespace sensitive, my preference is to break lines well before they hit the point where word wrap makes sense. But if you’re working with some lengthy lines of code or on a low-resolution laptop, this can help a lot.

Comments closed

Using the DAC from SSMS

Published 2021-08-16 by Kevin Feasel

Chad Callihan shows how you can configure the dedicated administrator connection and connect to it via SSMS in a time of need:

Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and know how to use it…just in case. I haven’t needed it too often in my career but it was helpful in instances when SQL Server wasn’t being very responsive. If there are problems connecting to a server, the DAC can be used to connect and troubleshoot issues.

Read on to learn more.

Comments closed

SSMS Templating

Published 2021-05-19 by Kevin Feasel

Kenneth Fisher shows off templating in SQL Server Management Studio:

Several times over the last few weeks I’ve gotten a request to create a new work database. The individuals from this team each have their own database that they can use as a type of scratch pad and I guess they’ve been hiring. It’s simple enough to create the database and then grant the necessary permissions, but let’s face it, after the first time I was already tired of the GUI and scripted the process out. Running the script was better but I quickly became annoyed at having to make changes to the script. User names etc. So I decided to create a more permanent script. My first thought was to use dynamic SQL. While that would work, and I’m certainly comfortable with dynamic SQL, it just didn’t feel right for this. I decided in the end to use a trick from templates. If you set up parameter(s) in the file you can use Ctrl-Shift-M to scroll through them and make changes. In an odd twist these are called template parameters. 

Click through for a demo.

Comments closed

No Respect for NOCOUNT

Published 2021-03-30 by Kevin Feasel

Thomas LaRock notes an oddity in SQL Server Management Studio and Azure Data Studio:

Anyway, I spend time trying to debug what is happening. I am able to manually set NOCOUNT on and off inside of T-SQL and see a count of rows affected returned (or not). I check and recheck everything I can think of and feel as if I have lost my mind. I’m starting to question how I ever became certified in SQL Server.

I mean, it’s a simple configuration change. This isn’t rocket surgery.

So I do what anyone else in this situation would do.

I turn off my laptop and forget about everything for a few days.

I’d never used this particular style of setting NOCOUNT on for a user (I would always enable it by session using SET NOCOUNT ON), so I’m not sure when certain tools started ignoring the user-level setting, but read the whole thing for maximum intrigue.

Comments closed

Comparing SSMS and Azure Data Studio

Published 2021-02-10 by Kevin Feasel

Deborah Melkin contrasts SQL Server Management Studio with Azure Data Studio:

Honestly, the vast majority of my time is split between Management Studio (SSMS) or Azure Data Studio. I’m pretty simple\straightforward this way. I started playing a lot more with Azure Data Studio over the past year, but I find I’m not able to make the switch to using it full time. It really depends on the task that I need to do.

So what tasks do I do often and which tool do I use?

The plus side for Azure Data Studio is that it’s far enough along that some of these choices are difficult to make. The minus side is that it’s still often on the losing end. I’d expect that shift to continue over the next couple of years as the product matures and becomes a good product for database developers.

Comments closed

December 2020 SQL Tools Releases

Published 2020-12-18 by Kevin Feasel

Drew Skwiers-Koballa gives us an update on where SQL Server tooling is at:

The December releases of Azure Data Studio 1.25 and SQL Server Management Studio (SSMS) 18.8 are now generally available.  Additionally, the mssql extension for Visual Studio Code has recently been updated to version 1.10.0. Read on to learn more about each of these updates and grab the latest versions of SSMS, Azure Data Studio, or the mssql extension for VS Code.

Read on to learn more.

Comments closed