Practical Applications of Extended Properties
Published: Jul 30, 2017
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
go

create table person
(
  
personID int identity primary key clustered,
  
firstName varchar(25),
  
lastName varchar(50),
  
NINumber varchar(15),
  
DOB date,
  
createTime datetime constraint df_createTime default(current_timestamp)
)
go

if object_id('customer') is not null drop table customer
go

create table customer
(
  
customerID int identity primary key clustered,
  
fullName varchar(50),
  
NINumber varchar(15),
  
addressID int
)
go

exec sp_addextendedproperty 'Natural Key', 'NI Number', 'schema', 'dbo', 'table', 'person', 'column', 'NINumber'
go
exec sp_addextendedproperty 'Natural Key', 'NI Number', 'schema', 'dbo', 'table', 'customer', 'column', 'NINumber'
go


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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron