Voice of the DBA

Tag: SQLNewBlogger

Quick SSMS Tip: Map Mode for Code–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In recent versions of SSMS, there’s been an addition to the query windows that I find helpful and useful. This is the map mode for code, and it means that when you have a large script, you can get a quick view of where you are in the file, and where you code is located.

First, turning this on. In the Options for SSMS, select the Text Editor section on the left, then expand the All Languages item. There is a Scroll Bars section to select. When you pick this, on the right you will see the Behavior section near the bottom. The default is bar mode, but you can switch this to map mode.

When you do that, if you open a script that is bigger than your query window, you will see a map of the code, along with a light colored bar that shows where you are in the code.

In the image below, Glenn Berry’s diagnostic script is open, and I have actual code near the middle of my screen, and on the right, you can see the pink just at the bottom of the oval that’s lighter in color than the map.

This is a little hard to describe, but if you turn this on and experiment, you’ll get an idea of what happens.

Let me add some blank links to this script. When I do that, I can see the map expand with some gray spaces near the top.

You can also get a preview of code by putting the cursor on the map to the right. Below I’ve put the cursor on the right side, above the current location in the query window. I see a preview of the code, while I see the locator bar below the preview, showing me where I am in the code file.

Map mode is an interesting way to manage large code files, which sometimes occur in stored procedures and complex queries. While I would hope that most people don’t have hundreds of lines of code in any one file, I do know some do. Map mode can help.

SQLNewBlogger

This was a question someone asked me in a presentation. It took me almost as long to type an explanation as it did to take a few screenshots. Adding a little text made this a 10 minute effort.

This is the type of thing that you can do, showing how this might help you with some code that is complex and harder to navigate. In addition, this is a nice way to show you are honing your craft and learning to use your tools.

August 5, 2020
Adding a FK to a Table–#SQLNewblogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One thing that helps ensure your data is intact and doesn’t get out of synch in a relational database is a foreign key. You ought to have these as a part of your design, ensuring that a linkage between a parent and child cannot be broken.

This post looks at adding a FK to an existing table. I’ve written about how to do this in the CREATE TABLE statement in another post.

I have two tables set up: Contacts and Status. Both of these have a StatusID column in them. The Status table contains the lookup values, and these are stored in the child table, Contacts.

To add the foreign key, I add a constraint with the ALTER TABLE ADD CONSTRAINT syntax. After this, I use FOREIGN KEY to list the column(s) and then the REFERENCES phrase to point out the parent table and column.

The example is shown here:
```
ALTER TABLE dbo.Contacts
   ADD CONSTRAINT FK_Contacts_Status_StatusID FOREIGN KEY (StatusID)
      REFERENCES dbo.Status (Statusid)
;
```
This will give me a FK that enforces the values in Contacts as existing in Status.

SQLNewBlogger

I had to do this recently and decided to quickly write this up as I had to look up the syntax to be sure I remembered it correctly. Then it took me about 5 minutes to produce this.

It took me almost as long to see if I’d already written about altering a table with a FK.

Do this for your career, and to show interviewers that you know how to handle common data referential integrity tasks.
July 29, 2020
Getting a List of Tags from the Microsoft Container Registry–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I needed to write this post because I keep forgetting this. I’m hoping this will help me remember.

I easily remember that the container registry is at mcr.microsoft.com. This isn’t helpful. On Firefox, I get a redirect.

In Chrome, I get less help

I know that the image path is under /mssql/server, but again, on Firefox, I get to to product home page. On Chrome, I get the same thing.

On StackOverflow, someone suggested a /v2 in front of the image path, and adding a /tags/list to the end. I did that and got redirected to this long URL: https://mcrflowprodcentralus.data.mcr.microsoft.com/mcrprod/mssql/server?P1=1594158334&P2=1&P3=1&P4=Aszj4JeNRzzbgvYq1xWs5CNXVSSIubeP%2FOko5OfNcpk%3D&se=2020-07-07T21%3A45%3A34Z&sig=ZVZdW1JcHuONk7pFh%2BGo%2FV8V6I7HKaLDsrAz0ru6dIA%3D&sp=r&sr=b&sv=2015-02-21

That works since it apparently gets me the Docker API with the v2.

The main problem for me was that I wanted to get the latest 2019 image, without using latest. I tried this:
```
docker pull mcr.microsoft.com/mssql/server:2019-CU1-ubuntu
```
However, that doesn’t work. Apparently, since there are now two versions for Ubuntu, I need to add a – with that. Either a 16.04 or an 18.04.

Maybe I’ll remember that.

SQLNewBlogger

Containers are going to be important at some point and knowing how to work with them will be a desired skill. As I try different things, taking 10 minutes to document some knowledge is good. In this case, I give an interviewer a good reason to ask me if I actually remember this.
July 8, 2020
Getting the Proc Code–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a question recently about getting the code in a stored procedure. I’ve become dependent on SQL Prompt, which gives me a nice preview of code when I see a proc:

The main way I’ve gotten code is right clicking a proc in Object Explorer and clicking Modify. That opens up an ALTER statement for the proc.

However, there’s one more way to do this: sp_helptext.

Not the best solution, but it gives me a quick look from SQL without futzing through a big list in the OE.

SQLNewBlogger

This is a quick example of some knowledge that I can use in my regular work. When someone asked the question, I knew the code for procs was stored somewhere, but I wasn’t sure what the proc to get this was. I tried sp_help, but that wasn’t right, so I had to go look up sp_helptext.

This took about ten minutes to write.

July 6, 2020