Automating SQL Linting & Tuning

Next let's create a function in SSMS. Then we can see how to incorporate that into the SSDT project.

This changes a date string into the format of our DateSIDs.

To pull this into the VS project, click on Tools > SQL Server > New Schema Comparison.

We use the database on SQL server as the source, and we happen to already have that as a connection.

Select the connection, and click OK.

For the target, we can use the SSDT Project.

And click on OK.

Now the Compare button above the source is active. We can click that and see what happens. We can see the list of differences between the source and target. You can turn off the items to not make changes to by clearing checks from the boxes. To actually make the changes, we click on the Update button.

We confirm we want the changes made. Then we can drill into the project in solution explorer, and see that the dbo schema now has a function.

OK, now let's go the other direction and push changes to the database. We create a dapac file for our project by right-click on the solution's name, then click Build.

Then we can right-click on the solution's name, and select Publish. We need to select a target database. For now, I'm just reusing the same database, but it could go to a different SQL server for testing.

Now we can click on publish, and all the new items are placed into the SSMS database.

We need to set up some items so that eventually this project will actually run. First off, we need to set up a reference to an existing server where the project will be exported when we're finished.

Under the Solution's name, there is a entry for References. Right-click on that, then select Database References. Change the database reference type to System Database, and select the master database.

Just to be fair, I had trouble with adding the default as below. I kept getting an error SLQ17501, meaning the editor was unable to find the referred to item.

There is an equivalent of SSMS' Crtl+Shift+R, which in the Project menu then Reanalyze Project. And that didn't work. It's supposed to take a little bit like the refresh of the table data into IntelliSense does in SSMS, so I waited. Hours.

Then I deleted the offending item and tried to add it again. Still there.

Then deleted the entire project and started over. At that point, in the error listing, I noticed the project was set for "Build + Intellisense". Clicking the other options confirmed that the problem was in IntelliSense, not the Build.

Next day, I started off with a new and smaller build and planned on using VS to report the issue to Microsoft. And it never came back. Anywhere. Computer Science is in the next classroom, this is working with software, which is a whole different beast. End interruption.

The source table has the one constraint already, which is the clustered primary key. "Clustered" means that the data will be put in the specified order onto the drive. Primary keys have to be non-null and unique, and are often what is used as a foreign key in another table.

This is a table of dates, and there's already date functions and tools built in to SQL. Why add more? Mostly so that we have a simple table to join on for long calculations. You can figure out if a given day is a weekend by looking at the DatePart() function with the first parameter set to DW. But is it a holiday? That is a bit harder to work out. This table does the calculations so they don't have to be repeated endlessly.

First off, let's set the FederalHolidayFlag to default to the character 'N'. We do the right-click on the solution's name, then Add, then New Item. Under Tables and Views we can select a Default Constraint. And it wants a name again.

And VS is helping where it can.

There's a related Check Constraint. Everybody is going to have opinions about where each of the files should be one single step, or if all the related steps should stay together. Make a choice that appeals to you, and realize unless you're the project manager it doesn't matter. I want the practice using the different templates.

The blue squiggle is error SQL70588 - since there's already a data enforcement check on that column, the WITH CHECK part of the statement will be ignored. I'm making a note that the Test Suite needs to look at that.

Document it. Always. Or you will forget.

Why use 'Y' and 'N'? One byte is the smallest column size. The boolean columns do only take up one bit in some byte, but unless you have a lot of booleans (8 or more), it's not much of a savings in space and a tad more work in CPU - probably breaks even in the long run. We could have used 0 and 1 in a tinyint column. It was an aesthetic choice on the part of the Data Architect team.

So I'm trying this. It's worth a shot. First step, have a database project in VS. Uhm... I've never done that. I know why we should. But my work has a history of not requiring programmers to document what we do on production systems. Finally got the server admins doing it a while ago, but folks like me live dangerously. Grumble.

So - step 1, create a database. It's not a listed step, but apparently you don't do the creation in VS. There's no step for it in the template listing at least.

So instead I'm doing https://medium.com/hitachisolutions-braintrust/create-your-first-visual-studio-database-project-e6c22e45145b

Step one: in SSMS run the command:

CREATE DATABASE TCommon

T for temporary, and Common is a database I've already got going. It's for non-secure tools/programs/etc. that any of the other databases should be able to access.

Now to start up VS 2022. We begin a new project and search for database templates.

Clear the checkbox for putting the solution and project in the same directory, and give an overarching name to the solution. That way you can have multiple database projects worked on inside of one solution.

Next, we import the blank database so we have a test bed based off what is in production. Right click on the solution name, select Import, then Database.

The import database wizard looks like this after the connection is set.

Blackburn suggests that you turn off the importation of referenced logins so you don't accidentally alter permissions. Sound strategy.

Then you can click on the "Select Connection" button.

On my workstation, I have to Show Connection Properties, then change the default for Trust Server Certificate to True for it to make a connection. I'm running a test version of SQL Server and didn't set up the certificates.

Click on Connect. Then on the Import Database window, click Start.

With a blank database, it's fairly anticlimactic, but there really is a connection now to the database, and the properties are copied to your work area. The summary tells you where the log is stored. Then click "Finish" to continue on.

Next, we'll add some objects in. Right click in the Solution Explorer pane, then click Add, then New Item. Lots of little goodies to play with. Since I've been trying to match a project from another site, I need to create a schema to store the objects in. Schemas are part of Security, and there's my little object. I select the schema, give it a name down below, and click Add.

Well, not quite what I expected to happen: CREATE SCHEMA [AddSchema_Dim]

But that's changeable. And in making that change, the solution's object has the name I wanted, and the code has the actual name of the schema I want.

Now, lets add a table.

If you're like me, you've used a few of these, but not all of them. Time to do research if you're unsure, but I'm going to go with a simple table for this demonstration. Since I know the name of the solution object will take the name I put in the bottom, I'll name this one AddTable_Dim.Date, and know that I need to edit the actual code.

You have choices. If you're used to creating tables using the upper part of the pane where there is a GUI type of set up, go for that. If you're used to typing things out, go to the lower part. Or mix and match! VS will keep the two in sync.

Instead of 'ID' we use 'SID' for Surrogate Identifier. The intake process sets up the unique (across a table) SID values and follows rules that help us track issues backwards to the original location where the data came from.

Second, there's a version number in there. We have the same tables across various enclaves (groups of servers), and we keep the versions the same between all but our development enclave. But instead of forcing our developers and end users to keep up, we use views that are in the databases they work from to expose the data. Many times we don't need to change the views at all which is easier on people that don't need to memorize a few hundred tables and variations.

Eventually, I want to get to the point where I can write an extension for SQL Server Management Studio (SSMS) that will do a few things:

Pretty up what's been entered, or at least get consistent spacing.

Find from a standard place or a specified one and use a list of antipatterns, and show where it's happening in file of SQL commands.

Find (standard or ad hoc) and use tuning hints that would make it easier to tune the stored procedures people write.

Be able to run 2 or 3 across an entire server's worth of functions and stored procedures. Bonus if I could get it to then look at what's running, determine if it's been looked at, and if not check it out (for people that don't store their code on the server).

I've been wanting to try to do this for a while. I know what has to happen, but I haven't used tools to do this since I was in college, and the state of the art is much more advanced. And in some ways, a bit more backwards.

I'm working in SQL, but actually we use T-SQL (aka Transact SQL) which is Microsoft's variant of the language. And is there documentation of T-SQL? Of course not. Why would anything change in their corporate wisdom? They started putting out something called the ScriptDOM (DOM = document object model), and that will create an Abstract Search Tree that we can then process to find problems.

Does it work on it's own, using unspecified scripting languages? Sure. Does it work in SSMS the most used tool for SQL at work? Nope. Does it work natively in Visual Studio (VS)? Nope.