One thing that I have found especially useful in my career working with databases is keeping up with the structure of metadata available in system databases, and with metadata stored in user database system catalogs.
For those of you unfamiliar with the term, metadata is often described as “data about data”. I prefer a slightly less simplistic explanation: “Metadata describes the format, structure and behaviour of data and code in computer systems.” The first definition catches situations where metadata reflects table and index structures, but fails at a semantic level once you start looking at definitions for stored procedures, triggers, functions, service end-points. There are also more ephemeral forms of metadata that we can mine for information – SQL Server query plans are an example of ephemeral metadata that can have a huge influence on the performance of a SQL Server system. A lot of DBAs forget that other systems can also have metadata as well – code compilers and intermediate language runtimes (e.g. C compilers, Java Virtual Machine, .NET CLR) use metadata to decide what optimization choices to make around memory management and garbage collection.
As I delve deeper into C#, I’m starting to realize there are huge stores of metadata awareness baked into the .NET framework language compilers. Decorations on class/method definitions in imperative languages, population of attribute properties in declarative languages (e.g. XAML) and the use of extended properties in SQL Server are all examples of metadata being used to provide richer semantic and functional descriptions of what our code is expected to do.
In the next few posts, I’ll discuss the kinds of things we can do with SQL Server metadata. I’ll cover a number of reporting tasks that we can perform using native SQL Server metadata. I’ll also discuss some of the programmatic things we can do that support broader data architecture initiatives, such as schema versioning, software release management, security auditing and code generation.
In the meantime, code well, and code often!