Entities and SQL Server 2005 Metadata. Really.

After having a go at the information on the Entity Framework and other ADO.NET 3.0 papers that were posted about a month ago, I was trying to come up with a good example of relational tables or views that are designed with entities in mind. Or perhaps from entities. Concepts like inheritence, for example. Unlike my good friend Ted Neward, I'm not about to make a blanket statement like "entities good" or "entities bad" (I believe Ted has a Political Science degree of some sort in his background), just looking for examples of an entity based design "in nature".

Then, someone sent me, out of the blue, a request for a diagram of the new system metadata views in SQL Server 2005. There's a PDF of a E/R diagram for them out on the web, and the more I looked at it, the more "interesting" the design looked. I happen to be doing some work with the system views for an upcoming paper, and came across the word "inherited" in SQL Server Books online description of them. Here's some excerpts:

-For a list of columns that this view inherits, see sys.objects (Transact-SQL).

-<inherited columns> – inherits from sys.xml_schema_components

-Some catalog views inherit rows from other catalog views. For example, the sys.tables catalog view inherits from the sys.objects catalog view. The sys.objects catalog view is referred to as the base view, and the sys.tables view is called the derived view. [base view and derived view? really?]  

-This can be summarized as follows:

-The base view contains a subset of columns and a superset of rows. The derived view contains a superset of columns and a subset of rows.

It's almost as though some of these follow one of the traditional patterns for mapping object/entity models to relational. But I'll leave this to you to decide. Entities are never a subject without controversy.

I wonder how I'd query these in EntitySQL. Or DLINQ.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.