SQL Server Blog

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:

Now you know all about Extended Properties, I’ll provide a quick script that allows you to query them effectively and quickly.



To be honest you could get away with using the scripts that I’ve shown in the previous couple of posts, but they’re a little fluffy and it’s better to be thorough. It’s also useful when you find that sys.extended_properties uses column names that are, quite frankly, ridiculous in comparison to every other system table in SQL Server.

This is an obvious continuation of my previous post about Extended Properties in which I will show you how easy it is to add and remove Extended Properties from a table in SQL Server.



This is possibly one of the simplest pieces of code that I have placed on my website, but as it’s so important when designing a database I wanted to make sure that it was included. And with it being so simple there’s no excuse to not utilize this at every opportunity.

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 a follow-up to my last post about accessing synonyms via a linked server (you can’t), I stated that it was therefore better to use a view. However, that has issues of its own to consider.



Nothing’s ever easy, you can’t access synonyms via a linked server and therefore you want to use views, but they are just asking for trouble as well because SQL Server can’t gauge statistics on them.

This was new to me and something which has appeared as a major downside for me when considering whether to use synonyms or views within my databases.



Basically, if you are using synonyms within your database and you then attempt to access those synonyms via a linked server then you will receive an error. SQL Server cannot access synonyms via a linked server.



This seemed like utter madness to me, but it’s true. And it also doesn’t provide a helpful error message to troubleshoot either.

This is a discussion I’ve had recently and it’s a bit of a weird one because there are actually very few times in which I would ever use a synonym, more often than not I would favour the view.

Again, following on from my previous posts about Key Lookups, I wanted to dispel a myth that I see used in production systems everywhere… that single column indexes are useful. In the majority of cases they just plain aren’t. Get used to it.

Following on from my last post about Key Lookups I wanted to emphasize the performance problems that you can get when these appear in your execution plans.



To do this I’m going to use a similar example as per my last post, but focusing more on the IO involved and also showing how a Covering Index can alleviate these issues.