This is the second 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

Add a New Query

When you add a new query:

1. A new query file is created in the SSMS project folder and displays in the Queries virtual folder
2. If an existing connection to the SQL Server instance to which SSMS is currently connected does not exist, a new SSMS project connection is added in the Connections virtual folder
3. A new query window opens for the new query:

Idempotent Scripts

When I read “Commands completed successfully,” I have questions.

• Which commands?
• How many commands?
• What happens if I execute the same command (or these same commands) again?

“Waiter, May I Have a Side of State + Instrumentation?”

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 idenpotent 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)

Create the DemoDB Database

Let’s create the DemoDB database, including status and instrumentation:

Let’s take a closer look at some of the features of this T-SQL script that creates the DemoDB database:

1. What is the object of this script? A heading helps.
2. Does the database NOT already exist?
   If the database does not exist, goto 3
   If the database does exist, goto 6
3. Communicate the intent to create the database. If you’ve ever attended my Azure Data Factory, SSIS, or SSIS Catalog training courses, you’ve heard me say (a lot), “Communicate intent.”
4. Create the database.
5. Communicate feedback that the database has, in fact, been created. Why this? Why here? If something “bad” (technical term…) happens with the execution of the Create Database statement, we want the person executing this script (“the executioner”) to be able to pinpoint the offending line of T-SQL.
6. Communicate the database already exists.

When executed, this script not only creates the DemoDB database, it also provides feedback about each step of the operation:

When re-executed – and for subsequent executions after the DemoDB database has been created – the messages display:

In Solution Explorer, rename the file – currently named “SQLQuery1.sql” to “0-Create_DemoDB.database.sql”:

I can hear some of you thinking, “Must we use that naming convention, Andy?”
Nope. But – please – pick a naming convention for the T-SQL files included in the SSMS project and then stick with that naming convention.
Future you will thank you.

Regardless of the file name / format you chose, please save your script. Future you will thank you for that, too.

Create a Schema Named S

Click the “New Query” button in SSMS. Note a couple things:

1. A new T-SQL query window appears
2. Solution Explorer shows a new file in the Miscellaneous virtual folder named “SQLQuery1.sql”:

Enter the following T-SQL into the new T-SQL query window:

The T-SQL in the new windows appears as shown here:

There is an error. Hovering over the error reveals the error message “Incorrect syntax: ‘CREATE SCHEMA’ must be the only statement in the batch.”:

In T-SQL DDL (Data Definition Language), there are some places where the DDL statement must be the only – or first – statement in the batch. Why? You’ll have to search elsewhere to lean the answer to that question. Let’s just push the “Trust Andy” button and move on, ok?

One way to resolve this condition is to edit the T-SQL script so that it reads as follows:

The T-SQL now declares a varchar(100) parameter named @schemaSql. the value of @schemaSql is initialized with the DDL command “Create Schema s”. Next, the @schemaSql parameter is executed via a call to the exec command. Once this edit is complete, the T-SQL appears as shown here:

The T-SQL script no longer reports an error, so that’s good. Does it execute successfully, though? There’s only one way to knw for sure, and that is to…

Test It!

If all goes as hoped, the first execution of this T-SQL script should generate the following messages:

Subsequent executions of this T-SQL script should generate the following messages:

Before we leave this T-SQL script, save the script as “1-Create_s.schema.sql” using the SSMS File>Save As menu item:

Note the file name is updated in the SSMS project’s Miscellaneous virtual folder:

This is somewhat deceptive because although the file technically is part of the SSMS project, it’s stored in a completely different location.

One way to add the file to the SSMS project is to right-click the SSMS project name, hover over Add, and then click “Existing Item…”:

When the Add Existing Item dialog displays, navigate the T-SQL script file, select it, and then click the “Add” button:

If all goes according to plan, the T-SQL script should now appear in the SSMS project:

Conclusion

In this post we examined scripts used to create the DemoDB database and a schema named “s.” In the next installment, we continue by adding a table and a view.