Database Consulting Group LLC Blog

I've always been dissatisfied with the level of user-defined type support available in existing SQL-based DBMSs, namely very little. Take Microsoft SQL Server, for example, which at least allows for a pretty close approximation of a user-defined scalar type. Or at least they used to. In the interest of clarity, I will describe the existing support:

Microsoft SQL Server allows the developer to create an alias for a system-provided type. For example, one could define a type called Phone as an alias for varchar(20). In addition, the type alias is allowed to specify a default nullability for columns defined of this type, and a 'rule' expression can be applied, similar to a column-level check constraint, except that the developer only has to provide the definition one time, on the type alias. Then all columns declared to be of that type (loosely speaking), are subject to that rule.

Of course, it is not an actual data type, because local variables declared using these type-aliases are not subject to the rule defined for the type. The documentation even has this little gem:

"Because rules do not test variables, do not assign a value to an alias data type variable that would be rejected by a rule that is bound to a column of the same data type"

But hey, I'll take what I can get. So I tend to use this functionality rather extensively in Microsoft SQL Server applications, as they provide the closest approximation to the full-fledged type support that I consider indispensible. In Microsoft SQL Server 2005, they even introduced syntax for them to make it easier to use these beasties, the create type and create rule statements. It's not complete though, because you still have to use the sp_bindrule procedure to actually enforce the rule, but at least they tried, right?

At any rate, one of the projects on which I am currently working uses one of these type-alias bound rules to enforce a particular constraint. Recently however, the DBMS began rejecting inserts for a table with one of these rules bound to a column. So I determine that the error is occurring inside a stored procedure generated by netTiers for the purpose of inserting data into the table. However, when I attempt to insert the data myself by copying the insert statement out of the procedure, it works!

How is this possible? Well, according to the DBMS, the error is

'insert or update statement conflicted with a rule previously imposed by a create rule statement'

But the insert has a null for the column that is causing the violation! So if you have a rule defined on a column and you have an insert statement that attempts to insert a null into that column and that insert statement is compiled into a stored-procedure, you will get an error, even though the same insert statement executed in a batch will work fine. Great. So I go looking for the bug report, but find nothing. So I verify my database version. By the way, if you don't have this link on your top 10 list, you should, this site maintains an excruciatingly detailed list of build numbers for SQL Server and what the officially sanctioned Microsoft title is for them:

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

But I have the latest version, so no help there. And now we come to the most painful part. Other people have encountered this problem, but the official line is that rules are a deprecated feature so don't count on getting this defect resolved. Aaarrrggghhh!!!!

So I workaround the issue by redefining the rule to explicitly test for and allow the column to be null. But what is going on here? Why is Microsoft deprecating the best reason for defining type-aliases in the first place? Without rules, they're just names, and not terribly useful. The rules actually allowed the developer to approximate first class types, which are a crucial part of enforcing the integrity of the database. I've said it before and I'll say it again, 'A Name Does Not a Data Type Make!"

Bryn Rhodes
Database Consulting Group LLC