So you might be wondering what use Extended Properties could have outside of the simplistic “this helps explain columns” approach that I mentioned?
Well actually then can be really quite useful if you happen to have applications (or just SQL) which wants to join datasets in order to perform data compares or just create ad-hoc statements to join tables.
In way of an example, let’s consider the following:
We might have an application which wants to generate its own dynamic SQL in order to join tables together and create a result set to place on screen for the user.
We could provide views or procs in order to satisfy a simplistic front end which queries certain patterns and in certain ways… but what happens if our front end is truly dynamic and may well require any combination of joins, columns, queries etc and we simply can’t provide an endless string of procs and views to satisfy the requirement? Well, the application could easily use Extended Properties…
We could have the application query the Extended Properties of a table in order to ascertain the primary and foreign key relationships and therefore build its own queries quickly and effectively.
Alternatively, what if we want to compare datasets between two disparate systems?
We could have similar looking tables…
if object_id('person') is not null drop table person
create table person
personID int identity primary key clustered,
createTime datetime constraint df_createTime default(current_timestamp)
if object_id('customer') is not null drop table customer
create table customer
customerID int identity primary key clustered,
exec sp_addextendedproperty 'Natural Key', 'NI Number', 'schema', 'dbo', 'table', 'person', 'column', 'NINumber'
exec sp_addextendedproperty 'Natural Key', 'NI Number', 'schema', 'dbo', 'table', 'customer', 'column', 'NINumber'
Now, it’s easy to see that the tables are actually quite different and that they will likely have different IDs on each record… therefore, for example, how could we compare these two tables directly to see if we have missing records? In SQL that’s easy… but in an application which has no knowledge of the data this can be really tricky.
BUT let’s have a look at the extended properties… in our application we could easily see that there is a common natural key and therefore the application would have no issue in generating a query that performs a join on the natural key and can ascertain whether or not there are data differences.
Providing this type of information and ability to developers can be invaluable.
I’ve used this myself between remote systems and at multiple levels and I would have been utterly lost without it.
In my situation we had separate processes loading the same data into different systems in a different order and therefore we simply couldn’t do a data validation across servers using EXCEPT due to the differences in ID columns. However, transposing IDs down through multiple levels to their base natural key allowed a simple dynamic script to perform this data compare with ease when, on the face of it, the concept itself seemed impossible.