This is the first post in a series named One Way to Build an SSMS Database Solution. Please find additional posts:

1. One Way to Build an SSMS Database Solution – Introduction
2. One Way to Build an SSMS Database Solution – Add a New Query
3. One Way to Build an SSMS Database Solution – Add a Table and a View
4. One Way to Build an SSMS Database Solution – Add a Stored Procedure and the Build

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.

SQL Server Management Studio Solutions

Many developers are unaware of the existence of SQL Server Management Studio (SSMS) solutions. There are a lot of Visual Studio features included in SSMS – solutions are one such feature.

I am a fan of learning by example, so let’s use an SSMS solution to create and maintain a new database named DemoDB!

Creating a DemoDB SSMS Solution

To create an SSMS solution, open SSMS, click the File menu dropdown, hover over New, and then click “Project…”:

When the New Project dialog displays:

1. Select “SQL Server Scripts” from the middle pane
2. Enter or browse to a project directory
3. Enter a name for the SSMS project (this entry is duplicated in the “Solution name” property)
4. Click the OK button to create the SSMS solution

SSMS has a Solution Explorer just like Visual Studio, and SSMS’ Solution Explorer displays once the solution has been created. As in Visual Studio, SSMS solutions surface three tiers:

• Solution
• Project(s)
• Artifacts (Connections, Queries, and Miscellaneous)

An Aside: Multiple Projects

As in Visual Studio, a solution may contain one or more projects. If you right-click the solution and hover over Add, you may or create another SSMS project in the same solution, of add an existing SSMS project to the same SSMS solution:

Although multiple SSMS projects per SSMS solution are permitted, I practice one project per solution.

Connections

You may right-click the Connections virtual folder and then click “New Connection” to create a new SSMS project connection:

 

Creating a new SSMS project connection is not required. A connection will be automatically created when you connect to an SQL Server instance.

Queries

There’s more than one way to add a query to an SSMS project. One way is to right-click the Queries virtual folder and then click “New Query”:

Other options include:

1. Right-clicking the SSMS project, hovering over Add, and then clicking “New Query”
2. Right-clicking the SSMS project, hovering over Add, clicking “Existing Item,” and then navigating to an existing script file:

Miscellaneous

Similar to Visual Studio projects, the Miscellaneous virtual folder may be used to hold, well, almost any document. If there are requirements and / or specifications for the database, one may add a copy of those documents by right-clicking the SSMS project, hovering over Add, clicking “Existing Item,” and then navigating to the document. One advantage of storing documents with the project is source control. When the solution is checked in, the documents in the Miscellaneous virtual folder are checked in with the other artifacts.

Conclusion

There’s more. Lots more! But this is enough for one post. The next step is Adding a New Query.