Finding a use for Extended Properties in SQL Server

“Extended properties allow you to add custom properties to database objects”, so says the official Microsoft documentation. However, very few DBAs make use of them, if at all. This is actually a good thing, because indeed the scenarios in which this feature could be useful are relatively few and rare.

On the other hand, I also see DBAs essentially jumping through hoops to implement something, which could in fact very easily be implemented using Extended Properties.

What are these peculiar use cases? Let’s look at a few examples.

First, let’s try to understand a bit Extended Properties in general and how they are structured.

What are Extended Properties Anyway?

Extended Properties in SQL Server are structured using a methodology called “Entity-Attribute-Value” (EAV for short), also known as “Open Schema“.

If we query from the sys.extended_properties system catalog view within a database that has at least a few extended properties, we may see something that looks like this:

The first thing that you may notice is the structure of the data:

The second thing you’re likely to notice is that there might already be several existing extended properties in your database whose name starts with the “MS_” prefix. Such as: MS_Description, MS_DiagramPane, MS_DiagramPaneCount, and more.

This is because SQL Server Management Studio uses extended properties to implement the little known “Description” property which is available in the Table Designer:

It also uses extended properties to save the location and visibility settings of the table diagrams in the View Designer:

In versions of SQL Server prior to 2019, SSMS would also store Data Classification details using extended properties.

Several widely known SSMS plugins may also make use of extended properties as part of their implementation. Such as Red Gate and ApexSQL plug-ins, used for adding documentation onto your SQL Server schema objects, or storing information related to source control.

Well, okay, but why would I care?

I bet you’re probably asking yourself why should you care about any of this, and how could this feature help you specifically in your day-to-day work as a SQL Server DBA?

Well, as promised, here are a few common scenarios in which extended properties could be very useful:

User-Defined Global Variables

You may already know that SQL Server does not have a “Global Variables” feature that you may use for your own needs.

I mean sure, the capability is actually there, evident by various system global variables such as @@SERVERNAME, @@SERVICENAME, @@IDENTITY, @@DBTS, and many more.

But, unfortunately, there’s no mechanism available to us to “DECLARE” our own “@@” global variables.

We can, however, utilize extended properties for this purpose.

Using a combination of stored procedures sp_addextendedproperty, sp_updateextendedproperty, and querying from sys.extended_properties, we could implement an easy-to-use mechanism to access and manage global variables:

And now, to use global variables, we would only need to do something like this:

DECLARE @LastCheckTime DATETIME

SET @LastCheckTime = CONVERT(datetime, dbo.global_variable('Last Check Date'))

IF @LastCheckTime IS NULL
BEGIN
	SET @LastCheckTime = GETUTCDATE()
	EXEC dbo.global_variable_set 'Last Check Date', @LastCheckTime
END

SELECT @LastCheckTime AS [@LastCheckTime]

Synchronization of Change Tracking

Let’s say that you’re using the CHANGE TRACKING feature in SQL Server for the purpose of synchronizing one or more tables from one database to another.

For this purpose, you would have to keep track of the value of CHANGE_TRACKING_CURRENT_VERSION() when you last synchronized your data, so that you’d know which value you should use with the CHANGETABLE function during the next synchronization.

Most DBAs would think of creating a dedicated table to manage this synchronization per each table.

But why should you, when you can simply use extended properties for this purpose?

And then, your synchronization code could be as simple as this:

declare @last_synchronization_version bigint, @current_synchronization_version bigint;

SET @last_synchronization_version = dbo.ct_get_last_copied_version('SalesLT.Product', null)
SET @current_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION(); 

-- Obtain relevant data set:
SELECT  
    CT.ProductID, CT.SYS_CHANGE_OPERATION,  
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT  
FROM  
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
 
-- Update the last synchronized version
EXEC dbo.ct_set_last_copied_version 'SalesLT.Product', @current_synchronization_version, null

Synchronization of Anything

Speaking of synchronizing data, it doesn’t have to be something complicated based on Change Tracking.

Perhaps you’re actually incrementally synchronizing data based on an IDENTITY column?

Or maybe based on a DATETIME column?

Or maybe it’s not necessarily “synchronization”, but any other business process that needs to be resumeable and tracked at the database or object level?

Whatever it is, you can use the same concept demonstrated above to store the last values you copied as extended properties, so that upon the next synchronization you’d know at which value you’d need to resume.

But… Why shouldn’t I create my own tables?

Some of you might be asking yourself: Why all this hassle? Why start using a little-known feature in SQL Server instead of creating my own management tables for similar purposes?

Well, you would be right to ask something like that indeed.

If the process in question is an important business process, it would be important that it’d be easily understood not just by yourself, but by other colleagues who may be working with you or after you’ve left, and who may start wondering why isn’t there a dedicated management table for storing the last synchronized IDENTITY or whatever?

I guess that, at the end of the day, it’s up to you to decide:

Would you rather design, create, and maintain your own set of management tables dedicated to a specific scenario?

Or would you rather use an already-existing mechanism in SQL Server that could be just as useful, although albeit not as widely-known, and without creating unnecessary tables in your database?

I suppose that, in a way, the very purpose of this blog post is to make a little-known feature in SQL Server more widely-known. So that you may find your own relevant uses for it, and to also be able to understand someone else’s code when they’re using it.

Either way, I hope you found this post interesting and useful.