Shortcut: Extended Properties for SQL Server Objects

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.

2018-03-22