The Bit Bucket

In a previous post, I was discussing how outlining can be helpful with navigating around within a large T-SQL script file.

If you were trying to do that within a Microsoft Word document, the most common thing to use is bookmarks, and SQL Server Management Studio (SSMS) has them as well.

Bookmarks are simply placeholders within a script. (They can also apply to other types of document within SSMS). Where I find them very useful is when I'm working in two or three places within a long script at the same time. Perhaps I'm working on a function, and also on the code that calls the function. By using bookmarks, I'm not flipping endlessly around the script file, and can jump directly from placeholder to placeholder.

A quick check of the Bookmarks submenu (under the Edit menu), shows what's available:

You toggle (enable or disable) a bookmark at a particular point, by using Ctrl-K and Ctrl-K. You can then navigate forwards or backwards using Ctrl-K and Ctrl-N (next), or Ctrl-K and Ctrl-P (previous).

Note that there are options available for both the document and the folder. The folder option can be particularly powerful.

Bookmarks are an often-ignored but highly useful part of using SSMS. If you don't currently use them, you might want to consider them.

I regularly find myself writing repetitive code in T-SQL. Some things are best done by just creating code snippets but we've also added several others to our free SDU Tools for developers and DBAs.

One straightforward one is DropTemporaryTableIfExists.

This just wraps all that's needed to remove a temporary table if it exists. The nice thing with this procedure is that you can call it before creating a temporary table, and call it again after you finish using the temporary table, as shown in the main image above.

In addition, to make it a little easier to use, you can reference the table with or without the # prefix, as shown below:

The outcome is the same in both cases.

You can also see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

I often see people creating databases in SQL Server and not specifying the name of defaults they are applying to columns. They define a column like this:

And there are general reasons why this makes sense. For example, a column can only have one default, so what does the name matter anyway?

There are two reasons:

Dropping columns

In SQL Server, you'll find that if you go to drop either of those columns, you'll see something like this:

SQL Server requires you to drop the default constraint on the column before you can drop the column, and unfortunately it requires you to do that by name. Notice the name that it chose for the default: DF__Customers__First__3A81B327. Life is far, far easier if you have a pattern that means you already know the name that will have been applied.

DevOps and Database Comparisons

As part of DevOps or other techniques, you'll end up wanting to compare two databases to find what's different. While some tools help with this, having the same table definition create defaults with different names isn't going to be helpful in this.

Naming

It doesn't matter too much what pattern you use for the names. I'd use this one:

I use the schema name, the table name, and the column name. That can't go wrong or end in duplicates. And I already know the names of my defaults, and they're easy to generate programmatically.

Alternatives

Keep in mind that this is a SQL Server specific thing. In other languages like PostgreSQL, you can't apply a name to a default constraint. But you don't need to because column defaults are automatically dropped when the column is. (No idea why SQL Server doesn't just do this). And you can drop a default with the DROP DEFAULT clause to ALTER TABLE without needing a name for the default.

These are things that SQL Server should copy but until they do, name your default constraints.

For some years now, SQL Server Management Studio (SSMS) has had the ability to use code outlining, the same way that other Visual Studio applications can.

This can be very useful when you are trying to navigate around a large script file.

The simplest usage is to collapse or expand a region of code. Note that in the following script, code regions have been automatically added by SSMS:

This allows us to click on the outline handles, and collapse the code:

Note that when the region of code is collapsed, the name of the region is shown as the first line of the code within the region, truncated.

If you hover over the ellipsis (the dot dot dot) at the beginning of the code region, you'll be shown what's contained within the region:

Now, what's missing?

I'd love to be able to just drag regions around.

I'd also love to be able to name the regions better. It's not too bad if the regions are procedures or functions but for other chunks of code, there's really no good option. Note that if I add a comment immediately above the code, it's not part of the same region. It might be better if it was like that, or if a specific comment could be treated as a region heading:

In the Edit menu, the Outlining submenu doesn't show anything else useful at this point, apart from bulk operations:

In our free SDU Tools for developers and DBAs, we have added many tools that help to manipulate dates.

When creating a date dimension (as part of dimensional modeling), you need to be able to get a list of all the dates between a start date and an end date. There are many other reasons why you might need to do this as well.

So we've added a table-valued function called DatesBetween to do just this. It takes a start date and an end date as parameters and returns all dates between. As well as the date values, it also numbers each of the dates.

In the main image above, you can see an example of it in use.

You can also see them in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com