So, a while ago I posted a blog about extended properties. In this blog I have used extended properties to dynamically UNPIVOT data. In this article I want to give another example on how to use extended properties. For instance, how to create view definitions with calculated HASH columns. In this case, a HASH based “key column”, but also, a HASH based column based on a set of columns to detect any changes in the data in a later stadium. So let’s start. In this demo I have a simple table called “heroes_information” and it contains data about superheroes as shown below:
Let’s say, for demo purposes, The columns ID and name together makes the row unique. But we want to check for changes over the other columns. We can set the extended properties of the columns as show below. This can be done by hand, or with dynamic SQL(metadriven of course) or maybe you have tooling for this to set these settings.
When I then start the generation I want the output to be a generated view with two extra columns. For instance, I want the “key column” to be as followed:
In this blog I will not be explaining how I generate the views. If that is something you are interested in, you can contact me. Or maybe I will write a new blog about it in the future. What I will say for now, is that all of the scripts that I generate are stored in a table by using dynamic SQL and they are executed of course. All of the sources(tables/views) are defined in a table with some metadata. This table is also the place where I can set my extended properties on different items.
Now, for this blog, the view will be defined as followed:
The end result:
That is it! There are a loads more examples to be done by using extended properties. This is just one of the use cases that I am using them for. If you have any questions/comments/remarks, please let me know by contacting me. Until next time!