DaveWentzel.com All Things Data
The Other MD3.Create Procedures
Metadata Driven Database Deployments (MD3) is my build-and-deploy mechanism for SQL Server-based applications. Check out the other posts in my MD3 blog series. In the last few posts we covered MD3 and "state" which covered how MD3 deploys "stateful database objects" using The Four Rules. If you handle The Four Rules correctly (and MD3 can greatly help you with this) then your database deployments should be a breeze. No more hand-crafted, error-prone DDL. In this post we are going to cover the other MD3.Create* procedures which help with database deployments and The Four Rules.
The Other Stateful Database Objects
Here are the other stateful database objects and how MD3 handles their deployment. In most cases this is handled with an
MD3.Create* procedure. These procedures deploy your stateful objects "smartly".
|Stateful Object||MD3's deployment mechanism|
|Tables/columns||Tables are created using standard DDL and can be scripted using SSMS tools. Columns can be added or altered using MD3.CreateColumn, this will be discussed further in the post MD3 Script Idempotency and MD3.CreateColumn(source code).|
|Indexes||This was covered in the post MD3 and "state" using
|Primary Keys||We'll cover this below. Source code for
|Foreign Keys||We'll cover this below. Source code for
|CHECK constraints||We'll cover this below. Source code for
|DEFAULT constraints||Handled with
|Model Data (states/provinces, valid order statuses, other "lookup" data)||Covered in the post MD3 Model Data Patterns|
Each of the above referenced MD3.Create procedures handles The Four Rules correctly. Let's look at these routines.
Conceptually, primary keys are very similar to indexes. You can cluster them, give them a name, give them compound keys, put them on a filegroup, etc. Therefore it shouldn't be surprising that
MD3.CreatePrimaryKey takes the exact same parameters as
The difference is what each procedure does under-the-covers. If you change a PK you may actually have to deal with objects that depend on it. Here's a list of objects that are dependent on PKs and how MD3 handles PK changes. You can of course change this functionality to suit your requirements:
- If the table is replicated then the table must be removed from any subscribed publications. Currently MD3 merely throws an error if this situation exists.
- Removes all FKs that reference the PK.
MD3.CreatePrimaryKeysimply removes them. Later, when
MD3.CreateForeignKeyis executed (remember, MD3 handles the dependent ordering of objects for you) the FKs will be recreated properly with the new keys.
- If we are changing the PK to be clustered we first drop an existing clustered index that already exists on the table. As with foreign keys, MD3 handles the dependencies for you...meaning the dropped clustered index can be recreated later as a non-clustered index if required.
In the next post, MD3.CreatePrimaryKey in Action I'll show you specific use cases where MD3 can save you vast amounts of time and rework when you have to modify a primary key. Stay tuned.
FKs are stateful database objects because they require a properly-applied "parent" key to be in place first. FKs can also be very expensive to recreate if the associated tables are large. Here are the parameters (properties) for
Like the other stateful object creator routines, MDs3.CreateForeignKey properly implements The Four Rules properly. The parameters should be self-explanatory, except maybe
@AllowNoCheck. Constraints can be either TRUSTED or UNTRUSTED. When you create a FK using
ALTER TABLE...WITH CHECK ADD CONSTRAINT you are saying that existing data in the table must adhere to the constraint, as well as any future data. When you create a FK using
ALTER TABLE...WITH NOCHECK ADD CONSTRAINT you are allowing existing data to violate the constraint, it will be enforced for future data only. This is an untrusted constraint. There are 2 reasons why untrusted constraints are frowned upon:
- It means you may have data that violates your constraint and you should probably determine why that is, and fix it.
- Some query plans will be less-optimal with an untrusted constraint, leading to possible performance issues.
MD3 by default does NOT allow untrusted FKs to be created. However, the
@AllowNoCheck allows you to override that, if needed. MD3 will try first to create the constraint using
WITH CHECK and then will fallback to
WITH NOCHECK if you have
@AllowNoCheck set. MD3 throws a WARNING in this case that you can then research later.
You may wonder why CHECK constraints are stateful database objects. The reason is that recreating a CHECK constraint on a large table can be EXTREMELY time consuming as all data must be read and evaluated. We also need to take into consideration whethere the constraint is trusted or not, just like with foreign keys. Here is the set of parameters that
Defaults are also stateful objects only because they are declared very similarly to CHECK constraints and the code is very similar.
In nodetitle I covered how MD3 covers stateful database objects, specifically indexes. In this post I covered how MD3 deploys the other stateful database objects like PKs, FKs, CHECK constraints, and defaults. In MD3 Script Idempotency and MD3.CreateColumn I'm going to cover what is probably the most important stateful database object...the table. In the next post, MD3.CreatePrimaryKey in Action I'll show you specific use cases where using MD3 instead of hand-crafted DDL can save you a lot of grief.