August 10, 2005 at 2:09 pm
Ok, I hate to open a can of worms here, but our group is trying to design standards and to explain the purpose of each standard. Most of the time, the purpose is for consistency, maintainability, etc.
But now we've come to User Defined Data Types. Our first inclination was to say "Don't use UDTs". 2 of us who have worked with SQL Server the most both thought that UDTs were a bad idea, but neither of us could remember why. We just remember that we don't do them. So I started researching the reasons, and I came up a little short. I found some discussions from 2002 and 2003 about some of the problems of using UDTs, like the trouble caused if you ever want to change a UDT, but I found little discussion since. Maybe I missed it.
So my question is, what is the current wisdom on the use of UDTs? It the debate still raging?
Also, does the inclusion of the CLR in SQL Server 2005 change the perception of using UDTs?
Thanks for any advice you can give.
August 10, 2005 at 2:25 pm
Well this could answer a few of your questions, but not all of 'em :
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=108&messageid=199531
August 11, 2005 at 12:03 am
As the discussion that Remi pointed to shows, yes, SQL Server 2005 CLR Integration does provide quite a log of new things regarding UDTs. My thoughts on CLR UDTs are in that discussion, so I won't reiterate them here. Regarding 'normal' SQL Server 2000 UDTs though, I think the answer is extremely simple. Never use them. Why would you? What do they add?
Sure, you get a common name and can also put some constraints to go with that name, so that you know that for instance your phonenumbers are always stored in the same way in all of your tables. But does this give enough value to accept the negatives? I think not. For instance, you get troubles when a) you are migrating to other products, b) your foreign customers have different styles of phone numbers, c) you are using features that do not support UDTs, like table variables. There are lots of other problems I can think of mainly regarding manageability, so again, since UDTs add nothing of value I see no reason to use them. If you need common types and/or formats for some kind of data enforce that using a standard.
August 11, 2005 at 7:41 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply