Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


What Are Extended Properties
Published: Jul 09, 2017
Extended Properties are a feature of SQL Server which are, in my opinion at least, massively underutilized and should be embraced a lot more frequently.

These are basically meta-data that you can add to an object in order to provide information that would otherwise be hard to obtain or, at least, hard to obtain easily. And it can give you a level of information that you might not otherwise have access to.

As an example, you may have a table with the following information and schema:

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 default(current_timestamp)
)
go


(For any Americans out there, the NI number is basically the UK equivalent of your Social Security Number)

So, how can extended properties help us? Well, let’s presume we’ve just been handed ownership of the databases containing this table and we know nothing about it… what can we do?

Firstly, we could have a look at the clustered index, the primary key and, if we were feeling adventurous, we could have a look through some system tables and find that there’s a default value and what that value happens to be.

However, would we ever know that the NI Number is actually a “natural key” of the table? That could be a VERY vital piece of information. Well, if we created the table with extended properties then this is exactly what we could know. And I’m all in favour of providing that type of information at all opportunities.

I’m not going to explain the process of adding and removing extended properties here as I’ll leave that to my next post, but I’ll at least show you what I’m talking about for this very table after I’ve added relevant meta-data:

select object_name(major_id) tableName, e.name propertyType, e.value propertyValue, c.name columnName
from sys.extended_properties e
left join sys.columns c
on e.major_id = c.object_id
and e.minor_id = c.column_id
where major_id = object_id('person')
go


See… that type of output would be incredibly useful to anyone coming across a table for the first time and therefore I’m definitely an advocate for people including this type of thing as part of their database design.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.