Shortcut: Extended Properties for SQL Server Objects

I started working with SQL Server in 1992, but all through the 1980’s and 1990’s, I was also working with Progress 4GL. I thought it was the best of the character-based 4GLs but unfortunately, they did a poor job of migrating to Windows and we decided to stop using the product.

One thing that I used to love with Progress though is that the metadata for each column in the database was much richer than what is present in SQL Server. In fact, Microsoft Access was probably closer to it in that regard. It’s something I really missed when moving to SQL Server. In Progress, when I defined a column, I could also define things like:

  • The name that would be displayed as a prompt on an input screen for this column
  • The format that data in this column would be displayed in
  • Any non-default input format or masking
  • And so on

Having this type of information in the database and stored in a consistent form can greatly reduce the amount of boilerplate code that needs to be written in applications.

SQL Server does have a concept of extended properties but what I think is missing is a set of “well-known” properties such as “Description”. The SQL Server team is starting to use these properties now for things like classifying (or labeling) data for security purposes in versions where built-in engine support isn't available.

I’d like to see them used more often. Here’s an example:

I often come across indexes in databases and when I ask why the index was created, no-one knows why. That also means that they are scared to remove the index. How much easier would this type of thing be if we had standard properties for each index that described why it was added?

We can already do this, but I just wish there were agreed standards for this.
As an example though, I tend to name indexes that are just present to support foreign keys, with the same name as the foreign key. That then matches how primary key indexes are named. In the WideWorldImporters database, you can see the naming convention. For the Sales.InvoiceLines table, here are the keys:

And here are the indexes:

It’s pretty obvious which indexes are there to support foreign keys. (Note we made a conscious decision not to index the Application.People related foreign key).

But for other indexes, how would you know why they are there? We included that info in our code generation, by using extended properties. To see this, right-click one of these indexes, and choose Properties:

On the Extended Properties page, you can see a description of why this index was created:

I’d like to see much richer metadata for each object in SQL Server and I suspect we might have to do that with a set of extended properties. I’d like to see a standard set of these defined. Even better would be a richer metadata store within the database engine.

SDU Tools: Count Words in T-SQL

Some of the simpler tools that we've added to our free SDU Tools collection over the last year or so, have turned out to be quite useful.

A good example of this is CountWords.

This function takes a T-SQL string, removes the punctuation, excess whitespace, etc. and then counts the number of words contained. There are a few things that can still trick it (avoiding that would need very detailed langauge parsing) but it's quite good.

You can use it directly from our toolkit, or use the tool as an example of how to write the function in T-SQL.

You can see it in the image above, and you can see it in action here:

For more information on joining our insiders team to get all our free tools and resources, follow the link here:

Opinion: Forced updates and tempdb

One of the changes that has happened with Windows in recent years is the concept of forced updates. Basically, you're going to get updates from now on, like it or not. Generally that's a good thing. You can delay them for a little while but not for that long. In the Advanced options update Updates, you can see this:

So you aren't going to delay them for that long.

Now what does this have to do with tempdb I hear you ask?

Well, tempdb gets recreated each time SQL Server starts, and that's normally in two situations:

  • You shutdown and restart the computer that it's installed on
  • You restart the SQL Server service

Now Windows 10 out of the box changes that behavior. If you shut it down, and start it up again, you'll find that objects that you had in tempdb are still there. That's because a shutdown and power up are no longer the same as a restart. Choosing shutdown actually hibernates the computer and power up just brings it back from hibernation.

So SQL Server didn't get restarted.

I generally find this when I go to create a demo table in tempdb, just after restarting my machine, only to find the table is already there.

You can change that by this setting in the power options for Windows 10:

Note that I've chosen to not have fast start-up enabled. When you turn it off, shutting down actually does a shutdown.

OK, so we can see how this affects SQL Server, but what does it have to do with forced updates?

Well it's because after Microsoft applies a forced update, I keep finding settings like this "automagically" reset for me, back to the value that I didn't want. I really wish they would not do this.



SQL: Why ANSI_NULLS matters for SQL Server Tables

Recently, I posted a link to show how to turn on ANSI_NULLS for  a table. It's not normally easy to change that but we added a procedure to our free SDU Tools to make it easy.

But one of the comments I received was a question basically saying "OK, you've shown how to change it but you haven't mentioned why it matters in the first place".

Fair enough. So that's the topic of today's post.

Many developers (and database people) get confused about the handling of NULL values. When we talk about a value being NULL, what we're really saying is that it has no value. This is quite different to a number being zero, or a string being empty (or zero-length).

It's also why we can't compare a value to NULL by using an equals sign (or not equals), but by saying IS NULL or IS NOT NULL instead. Being NULL is a state that a variable or column is in, not a value that it holds.

It's worth noting that some database engines still don't even get this right. And SQL Server was one of those in its past.

Here's an example. In the WideWorldImporters database, there is a Sales.SpecialDeals table. It has many columns but when it was shipped, it had two rows. Let's look at those.

Note that if I try to select the rows where StockItemID = NULL, I get no rows but if I compare where StockItemID IS NULL, I get the rows.

This is because a WHERE clause returns rows where the value of the predicate is TRUE. But with ANSI_NULLS on, NULL does not equal NULL. A comparison of NULL and NULL returns NULL.

You can see how the comparison works with and without ANSI_NULLS ON here:

SET ANSI_NULLS OFF takes us back to the bad old days whereas SET  ANSI_NULLS ON gives us ANSI standard SQL behavior on this. We don't want our tables declared with ANSI_NULLS OFF. (Or worse, I've seen some tables declared that way and others not).

You can read more here:





DevOps: SQL Server and Unit Test Challenges

I had a previous life as a developer and ran a software-development house. Even then, I was very focussed on data. I don’t think I’ve ever had a BD (before data) period. I see almost everything I’ve ever worked on in data-related terms, so perhaps it’s the time before I focussed on data.

But what this does mean is that whenever I get together with other data-related people, I’m one of the people who is asking why things that are taken for granted in the developer community, aren’t present in SQL Server and its T-SQL language.

Testing is one of these challenges. I’ve had old wise colleagues in the past who see this simply:

It’s pointless to write code that you can’t test

And yet, T-SQL development is still one of the areas where that’s a challenge. Over the years, I’ve had many discussions with SQL Server product team members about this, and still haven’t been persuasive enough to convince them to do something about it.

I should note at this point though, that if you’re someone who sees the database as just a storage for your objects, that no code should exist in the database at all, and that the performance you’re currently seeing is good enough, then read no further.

But I currently live in a world where performance matters and many operations need to be done as close to the data as possible, with as little data movement as possible.

If you are developing in T-SQL today though, writing unit tests and test harnesses is just way more challenging than it should be. In many cases, you simply can’t do it in any practical way, at least not within T-SQL. There have been some great attempts like TSQLUnit (, and tSQLt ( but these aren’t really where I want to be. It's not for lack of trying, but it's for a lack of support within the product itself.

A simple example might help.

I can write code in T-SQL to trap and handle errors, including system errors (ie: those with error numbers less than 50,000). But if I want to test that error handling, I’m fresh out of luck. The “normal” way to do that in high-level languages is to write code to just throw that error and check what happens. But the product group have decided that we’re not allowed to raise system errors.

When I’ve discussed this with them, I get responses like “ah but then we’d get errors reported and we’d have no idea where they came from”. I get that they’re thinking from a conservative product support point of view, but that sort of thing can be handled. There would be a way to handle this if there was a desire to do so. And what I’ve never understood is the lack of interest in doing so. Product group members are, by and large, developers whose lives must take testing as part of their own routine.

Error handling is just one example issue though.

So we’re left with two options really:

  • Spend our time asking the product group for changes to support better quality development in T-SQL
  • Wrap all our unit tests in other languages, accept that we just can’t test some things, and deal with language limitations.

As we move into a DevOps-centric world, this is going to be more of an issue. I’d love to see us pushing for the first option and having the expectation that people developing with T-SQL should have access to similar developer-focused tooling to those building SQL Server itself.

Shortcut: Add columns to Object Explorer Details window

I’ve mentioned in an earlier article about scripting multiple objects at once, how useful the Object Explorer Details window is, and how little understood it is.

Another useful option in it, is that the displayed columns can be changed. In particular, you can add columns that would be useful. Let’s look at an example.

In Object Explorer, I’ve expanded the WideWorldImporters database and clicked on the word Tables:

Next, I hit the F7 key, and the Object Explorer Details pane opens showing this:

I get a list of tables showing Name, Schema, Create Date, and Policy Health State. Policy was an interesting concept that I thought never got fully baked into the product, so it’s not of great interest to me, and really is just clutter. However, the number of rows in the table would be much more interesting.

If I right-click on the heading row, I get these options:

Now we’re talking. I’ll remove the Policy Health State, and Create Date (I don’t value it too much most of the time either), and add Data and Index space used, and Row Count. And we’re left with a much more useful result:

And, I hear you ask: “do I have to do this every time?” The answer is no. If you close the Object Explorer Details Window and hit F7 on the Tables node again, the same output appears.

SDU Tools: Set ANSI NULLS on for SQL Server Table

When you create script out a table in SQL Server using SQL Server Management Studio, you’ll notice that it scripts more than just the table itself. Before the table, it scripts the values for ANSI_NULLS and QUOTED_IDENTIFIER.

A common problem that I see is that someone highlights the CREATE TABLE statement and runs it, without highlighting the SET options above it. That has the chance of leading to the wrong values. I don’t run into big issues with QUOTED_IDENTIFIER but I certainly run into issues with ANSI_NULLS.

At a client site a few weeks back, I saw some odd behaviour, and checked the settings for all tables. They had a mixture of ON and OFF.

The question is how to then fix it.

The general advice is to copy the data off to somewhere else, drop and recreate the table, and copy the data back. Doing that might be awkward for a number of reasons.

Let’s create a table with what I consider the wrong value:

Notice that it shows uses_ansi_nulls as 0.

One of the tools that we added in our free SDU Tools for January 2018 was SetAnsiNullsOnForTable. You can see an example of using it in the main image above.

Notice that when it’s complete the table has the ANSI_NULLS value ON. The tool uses a trick involving a partition switch with a single partition.

Note that you’ll need to recreate constraints (including foreign keys) and indexes when it’s finished, but we hope you’ll find this useful.

You can see it in action here:

To join our SDU Insiders and get our free SDU Tools for Developers and DBAs and to get our other free resources, visit here:

SQL: How many tables is too many in a SQL Server database?

I spend a lot of time with developers and with ISVs (Independent Software Vendors). Often, I come across really weird table designs. A common problem is the creation of tables that hold many different types of objects.

If I look at one of your tables, and ask you what it holds, your answer shouldn't start with "It depends".

A worse design is one where the same discussion happens about columns. If I ask you what is held in the ObjectID column, and you say "that depends upon what's in the ObjectType column", you'd better be building some sort of utility, not a relational database to support an application. There are so many downsides to this type of design but that's a topic for another day.

Today, I want to talk about the table to rule them all.

Image by Andy Omvik

This is a table that holds many different types of entities. When I ask developers why they've done this, there are many reasons, but the one that I dislike the most is:

"We wanted to reduce the number of tables"

There's usually no great answer to the next question, which is why?

Image by Ken Treloar

If the reason is that you can't find them easily, I'll bet you aren't using schemas effectively. Schemas can work just like folders in Windows. In the same way that you wouldn't keep all your files in one folder in Windows (or I presume not), you shouldn't keep all your tables in the same schema unless there aren't many.

An easy test for this is the presence of prefixes. If you have a bunch of tables with the same prefix, chances are you're using the prefix to group them. That should be a schema instead. (BI people: I'm looking at you with all your DimSomething and FactSomething tables too).

If the reason is that you think the limit on tables isn't that high, you're just wrong. The number of tables is limited only by the number of database objects, currently 2, 147, 483, 647.

A couple of hundred tables isn't going to make a difference to anything except the clarity of your data model.



DevOps: Avoiding SQL Server Clone databases growing way too large

I’ve recently been making use of SQL Clone from Redgate, at various client sites. I have to say that I really like it.

The basic concept is that you take an existing SQL Server database, you create an “image” from it, then you create database clones from that image.

Under the covers, it’s basically a differencing technology. The initial image is like an initial set of database files, and each clone is then files that are differenced from that. That makes it very easy to spin up clones, and to break them down again. While this isn’t really suitable for much in the way of performance or load testing, as everyone is sharing the same base, it’s perfect for general development.

It’s also a really good fit for use with source control systems like Git that developers endlessly create branches in. You can just create a clone for use with each branch, and avoid developers trampling on top of each other all the time. Each developer gets to have separate databases (or even multiple databases) and gets to test on full data volumes, without all the storage space that would otherwise be required.

What I have run into lately though, is a site where all of the clone databases were getting bigger and bigger constantly. Instead of being 40 or 50 MB as they started out, each was now taking hundreds of gigabytes.

That was pretty easy to track down, and it was caused by routine database maintenance tasks that the customer was scheduling. In particular, they were regularly rebuilding the indexes in the database. In a clone database that’s based on differencing technology, that’s not going to make sense at all. Rebuilding 200GB of tables or indexes could easily end up with a 200GB clone fine. And if you do that to all your clones, well you can see the problem.

What is needed is to avoid performing index maintenance on the clone databases. Given that to SQL Server, they just look like any other database, I’d suggest having a naming scheme for the clones, to make it obvious which they are, to exclude them from these operations.

Shortcut: Cleaning up the Scroll Bar in SQL Server Management Studio

It's great that SQL Server Management Studio has moved into the latest Visual Studio shell. Unfortunately, there are one or two things that are a little harder at first for people who want to use SSMS to write T-SQL. One that was driving me crazy was the scroll bar. Visual Studio tries to give so much information on that bar, about what’s changed, where the insertion carat is, etc. The problem with this is that I often now can’t even find the handle when I want to scroll the window. For example, how do you grab the handle with your mouse and slide the window when it looks like this?

I was starting to get resigned to this when I asked in the MVP email list. Erik Jenson pointed out that the scroll bar itself had properties. I should have thought of that. If you right-click the scroll bar, you get these options:

Choosing “Scroll Bar Options” then leads to this:

For me, the ones that I’ve highlighted are the real offenders. However, note the warning at the bottom. You really don’t want to remove these for all languages. Some might be helpful to you if you use other languages. So if you do decide to change them, click on the option further down the list, to set them for T-SQL only:

I hope that helps you make SQL Server Management Studio a bit more useful.