… the data describing the data describing the data described described data described by data… (and so on)
The charm of SQL Server is that it comes with a good set of metadata. Even in SQL 2000 there were plenty of system objects containing data about the instance and all objects contained in it.
In SQL Server 2000 there were many system tables, which were the only way to understanding the internal workings of the instance with regards to health, tuning, performance etc.
SQL 2005 and 2008 offer more advanced techniques for accessing the information about the SQL Server internal workings.
What it really boils down to is that metadata is data describing the data, and in SQL Server since the instance installation there is already some metadata created; the SQL Server metadata is well structured and contained in database tables.
There are several (4 of them in SQL Server 2000 and 5 of them in SQL Server 2005 and later) system databases which come with every SQL Server installation:
master: bears the information about the server’s databases; it can also contain other objects which can be globally available to other databases (stored procedures, for example)
msdb: contains a list of activities, database backup history, agent jobs information, and so on.
model: The template database for every new databases. Any object placed in the
model database as well as any settings of it will be applied to any new database.
tempdb: this database is ‘initiated’ every time the SQL Server instance is started. In other words, the tempdb contains only temporary adhoc objects which are used by database users and the SQL Server engine itself. Tempdb is used for any temporary operations needed during query execution (sorts, hash matches and so on). Again, the entire tempdb is recreated on SQL Server instance restart, so do not rely on it for storing any permanent objects. Later on I will write about some pitfalls of tempdb.
resource: You will not see this database in the list of databases in the Management Studio when you connect to the SQL Server instance, however this database exists on disk within the SQL Server instance installation. The database is hidden and it contains information about service packs installed. The data in the resource database used to be kept in the master database in SQL Server 2000, but starting with SQL Server 2005, the resource database was created in order to make service pack installations easier.
Do I need to mention how important it is to backup the system databases?! (well, do not try to backup the tempdb! )
Also, metadata is formed during the creation of every new user database. Each database has system objects within it, which contain data about the database (state, filegroups, files, security settings and so on. )
Here are the types of metadata in SQL Server:
Catalog views: contain information about static metadata (tables, security, and server configuration)
Dynamic management views (DMVs) and functions: some of them contain data about the current state of the server; others provide cumulative dumps of data since the SQL Server instance start. The DMVs and functions will be discussed in detail in a later post.
System functions and global variables: they return a scalar value (not tabular) which in some way describes the server, the database, the connections etc.
Compatibility views: used as a backward compatibility tool which simulates the system tables
from previous versions of SQL Server 2000 and earlier. The compatibility vews are deprecated and they will not be in SQL Server 11 (Denali).
Information schema views: they are the ANSI SQL-92 standard nonproprietary views used to examine
the schema of any database product. Keep in mind that only certain objects are part of the standard and others are not (you will not see information about indexes in the schema views).
So, to round up all this data about the data, let’s just say that SQL Server provides plenty of metadata; so much, that it is not so hard to get lost in it.
The most important thing to remember, though, is the ‘time factor‘ relation between the SQL Server and the metadata. Some metadata is retained past the instance restart (and represents historical information), other metadata is a snapshot of a current state or events, and another kind of metadata is a cumulative blob of data collected since the instance restart.
One more thing: what do you think about the extended properties? Are they also metadata?