Mike asks:

Just curious if your metadata also contains validation rules or not?  Things like property is required or range of valid values.

It could include them in three possible ways – it currently uses one and I’ve had two others working in the past that may be resurrected.

The metadata that the database inherently knows is automatically transferred - this would be nulls and string length. How well nulls are handled is up to the architecture, but the metadata definitely knows what's nullable.

I've experimented with two additional approaches: using extended properties and parsing the TSQL of check constraints. The first would work for simple ranges and other predictable data sets, but it puts information in an unexpected place. I currently can’t justify it over placing validation in known places in the handcrafted code.

Using check constraints leverages existing information so is a "good" thing. Unfortunately, no one ever seemed to care about the months of work I put into that five years ago so I let it stagnate. Since I know more now, I could resurrect that work, but honestly I don't think I'll get time soon.

The problem is that most people just don't put check constraints in the database very often. I find that unfortunate for many reasons, but it becomes a chicken and the egg problem. People don’t put the constraints in the database because they’ll have to restate them in the business layer for decent usability. This initiative doesn’t get attention to solve that problem, because the check constraints aren’t already there. Perhaps the time is ripe now. I would love to include check constraint based validation in the Open Source version that we plan to start up on Code Plex this week or next (public within thirty days after) – at least a framework for it.

Check constraints are closely related to defaults because both require parsing TSQL. Turns out, over the years folks have been primarily interested in defaults of “now”, new guids, and raw values. Today or Now are pretty easy because it’s just a straight up translation between a SQL function and a .NET function. Any straight up translations like that can be defined in sort of a metametadata (hate that phrase) layer. I handle all three of these scenarios in my metadata extraction tool (a metadata extraction tool will be part of the CodePlex project).

I think validation should be stated in the business layer in rules. I wasn’t doing this five years ago so the whole process of incorporating validation from check constraints will be vastly simpler. Instead of code to code, you need to recognize a category – such as a bound range (the most important) and parse out the bounds into a structure usability by a specific rule. Then another rule is “there’s a check constraint and I think you need to validate based on it, but I can’t write it so you need to.” The architecture could enforce some code being written in response to that rule. To state the change from five years ago, the metadata wouldn’t contain code but the statement of which rule and its parameters.

Validation in triggers would seem, at least to my weak TSQL mind, to be exceedingly difficult.

So, the basic answer to Mike’s question is “some, but not all of the really important scenarios are covered, and I don’t think you’ll ever cover all scenarios”