A Love Hate Affair with User Defined Types
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/3104.asp
-
Most of the issues encountered in the article are related to binding rules and defaults to the types, not to the use of types themselves. Binding rules and types has been deprecated for some time now anyway.
-
I have bad experience with UDF. Once I had to change the length of a UDF VARCHAR and it was a nightmare. Solution? I change clear my database of UDF!
Josep.
-
I think they have the potential to stop the madness, 10 different email columns all with different sizes. A couple three notes on your notes:
- I much prefer getutcdate over getdate, sooner or later you'll have a branch offer, replicate, etc, and since SQL doesnt support time zones very well this can save some headache if planned up front
- I hear you about standardized data, but I've found null/M/F to work nicely for gender!
- Dont know that I'd use the @ check constraint on email, since its only a basic check and not really good enough, I'd rather leave it to the business tier, or really check it via CLR
But those are incidentals. The real feature missing is the ability to modify the type and have it propagate to all the tables. With that one change they go from ugly to interesting.
-
where i work they went crazy with UDT's a few years back and now almost every column in almost every table is a UDT
reason is to enforce business rules. sales people are the worst enemy and will submit new customer paperwork with all kinds of mistakes and whatever. a lot of angry people have to pick up the pieces on a daily basis. UDT's help things out because they force people to input certain data
-
Another major drawback to UDTs that severely hinders their effectiveness much the same way that not being able to use them in DataTables does is that you cannot use them with the CAST or CONVERT functions to deal with rogue data types in your schema. In my opinion SQL UDTs are more trouble than they are worth and I do not use them in my schemas at all.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I thought the article did a great job of explaining UDTs, and then explaining the pros and cons. I was aware of the pros, but not so aware of the cons.
I wanted to reply to the comment: "The real feature missing is the ability to modify the type and have it propagate to all the tables. With that one change they go from ugly to interesting."
My experience doesn't negate the above comment, but it does apply. I use ERwin (data modeling software capable of creating database-generating scripts) to generate my databases. I use ERwin not just the first time an application goes live, but most of the time I make changes to the database. The entire database is re-created from scratch, and the data is copied from the old database to the new.
I use the equivalent of UDTs within ERwin (in some situations, not for all columns). So, when I change a definition of a UDT within ERwin and then re-generate the database, the change is effectively propagated to all the tables. After reading the article and the comments, I think of my approach as a way to get the best of both worlds--assuming you can have the luxury of creating a production database from scratch when changes are needed. (Our databases are not 24x7. They are needed strictly during normal business hours.)
-
I *wish* that UDTs could be changed (redefined). I once wanted to redefine a UDT, and was really surprised that the type definition couldn't be changed. That would SEEM to be one of the great advantages to user-defined types.
You want all occurrences of data item "x" to have a different type (bigint, or more characters, or whatever)? Just change the user-defined type!
I can't believe that user-defined types aren't changeable if they are in use in ANY table in the database. Now THAT would be a terrific advantage.
It's as if you couldn't change a TYPE in your C# or VB code without removing all references to that TYPE first.
Bleah. This seems like a huge oversight. (When I first created the UDTs, I thought to myself "Great, this encapsulates the definition, so if I later want to change the definition, I can do it easily". Silly me.)
David Walker
-
Always a pleasure to read Mr. Poole's articles
One thing I noticed a while back is that Rules are deprecated. From the BOL entry on sp_bindrule:"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CHECK constraints instead. CHECK constraints are created by using the CHECK keyword of the CREATE TABLE or ALTER TABLE statements."
Non-CLR UDTs are looking less and less useful
-
It would be nice if there was a simple document that listed all the features that are going to be deprecated and all those that are to be replaced. It would make the job of planning for an upgrade much easier.
I feel that shifting all the constraints into the table is a case of 3 steps forward and 2 steps back. Imagine you need to use the same check constraint in many hundreds of places. If each table has its own check constraints then updating them all becomes a major headache. In a large database with many thousands of tables there has to be some overhead in having tens of thousands of objects in sysobjects?
-
UDT's make things difficult for the application developer, because you can't call them by name from .NET so the app dev has to go look up the true data type and refer to that.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply