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:

Column(s)Data Type(s)Purpose
class, class_desctinyint, nvarchar(60)These columns identity the item type (class) on which the property is defined. For example, DATABASE, OBJECT_OR_COLUMN, SCHEMA, INDEX, and so on.
major_id, minor_idintThese identifiers (both or one of them) are used for identifying the exact item on which the property is defined. Based on the class column(s), SQL Server would know how to interpret the major_id and minor_id columns.
namesysnameThis would be the property name, which could be whatever you wanna call it.
valuesql_variantThis would be the value of the property. Note that since it’s a sql_variant, it can contain any other data type within it.
More details here on sys.extended_properties

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:

Function to Retrieve a global variable value
don't forget to convert to the correct data type
CREATE FUNCTION dbo.global_variable(@VariableName sysname)
RETURNS sql_variant
AS
BEGIN
RETURN (SELECT [value]
FROM sys.extended_properties
WHERE major_id = 0 AND minor_id = 0
AND [name] = @VariableName)
END
GO
Stored Procedure to Save a global variable value
CREATE PROCEDURE dbo.global_variable_set
@VariableName sysname,
@NewValue sql_variant
AS
SET NOCOUNT ON;
IF NOT EXISTS
(
SELECT *
FROM sys.extended_properties
WHERE major_id = 0 AND minor_id = 0
AND [name] = @VariableName
)
BEGIN
EXEC sp_addextendedproperty @name = @VariableName, @value = @NewValue;
END
ELSE
BEGIN
EXEC sp_updateextendedproperty @name = @VariableName, @value = @NewValue;
END
GO

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?

CREATE FUNCTION dbo.ct_get_last_copied_version
(
@table_name sysname,
@target_identifier sysname = null optional parameter if you have multiple sync targets
)
RETURNS bigint
AS
BEGIN
RETURN ISNULL((SELECT convert(bigint, [value])
FROM sys.extended_properties
WHERE major_id = OBJECT_ID(@table_name)
AND [name] = N'CT_LastCopiedVersion' + ISNULL(N'_' + @target_identifier, N'')), 0)
END
GO
CREATE PROCEDURE dbo.ct_set_last_copied_version
@table_name sysname,
@new_version bigint = 0,
@target_identifier sysname = null optional parameter if you have multiple sync targets
AS
SET NOCOUNT ON;
DECLARE @PropertyName sysname, @PropertyValue sql_variant, @Level0Schema sysname, @Level1Table sysname;
SET @PropertyName = N'CT_LastCopiedVersion' + ISNULL(N'_' + @target_identifier, N'')
SET @Level0Schema = OBJECT_SCHEMA_NAME(OBJECT_ID(@table_name))
SET @Level1Table = OBJECT_NAME(OBJECT_ID(@table_name))
SET @PropertyValue = CONVERT(sql_variant, ISNULL(@new_version, 0))
IF @Level0Schema IS NULL OR @Level1Table IS NULL RAISERROR(N'Specified table "%s" was not found.',16,1,@table_name);
IF NOT EXISTS
(
SELECT *
FROM sys.extended_properties
WHERE major_id = OBJECT_ID(@table_name)
AND [name] = @PropertyName
)
BEGIN
EXEC sp_addextendedproperty @name = @PropertyName, @value = @PropertyValue,
@level0type = N'SCHEMA', @level0name = @Level0Schema,
@level1type = N'TABLE', @level1name = @Level1Table;
END
ELSE
BEGIN
EXEC sp_updateextendedproperty @name = @PropertyName, @value = @PropertyValue,
@level0type = N'SCHEMA', @level0name = @Level0Schema,
@level1type = N'TABLE', @level1name = @Level1Table;
END
GO

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.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.