Are User Defined Data Types in MS SQL Server something that a intermediate SQL user should know and use?
What are pros and cons of using UDTs?
+1
A:
Never use them is my advice. You are in a world of hurt if you ever have to change the definition. Perhaps this has improved since SQl Server 2000 and someone with more familiarity with the newer versions can tell you whether it is now safe to get in the water, but until I had confirmation of this and had chcecked it out myselft with a test, I wouldn't put it on my production system.
check out this question for details http://stackoverflow.com/questions/349049/how-to-change-the-base-type-of-a-udt-in-sql-server-2005
HLGEM
2009-03-18 14:08:01
+1
A:
I do not use code-based UDTs because I don't think that the extra complexity warrants the advantages. I do use T-SQL UDTs because there's very little extra complexity so that the advantages are worth the effort. (Thanks go to Marc_s for pointing out that my original post was incomplete!)
Regarding Code-based UDTs
Think of it this way: if your project has a managed code component (your app) and a database component (SQL Server) what real advantage do you gain from defining managed code in the database? In my experience? None.
Deployment is more difficult because you'll have to add assemblies to your DB deployment and alter these assemblies, add files, etc. within SQL Server. You'll also have to turn on the CLR in SQL Server (not a big deal but no one's proven to me that this won't have a performance/memory penalty). In the end, you'll have exactly what you would have had if you had simply designed this into your application's code. There may be some performance enhancement but it really strikes me as a case of premature optimization - especially since I don't know if the overall performance suffers due to having the CLR on versus off.
Note: I'm assuming that you would be using SQL Server's CLR to define your types. HLGEM talks about SQL Server 2000 but I'm not familiar with 2000 and thought it only had UDFs and not UDTs in externally-defined dlls (but don't quote me...I really am not familiar with it!).
Regarding T-SQL UDTs
T_SQL UDTs can be defined in SQL alone (go to "Programmability | Types | User-defined Data Types" in SQL Server Management Studio). For standard UDTs I would in fact recommend that you master them. They are quite easy and can make your DDL more self-documenting and can enforce integrity constraints. For example, I define a "GenderType" (char(1), not nullable, holding "M" or "F") that ensures that only appropriate data is permitted in the Gender field.
UDTs are pretty easy overall but this article gives a pretty good example of how to take it to the next level by defining a Rule to constrain the data permitted in your UDT.
When I originally answered this question I was fixed on the idea of complex, code-defined types (smacks palm to forehead). So...thanks Marc.
Mark Brittingham
2009-03-18 14:21:58
UDT (user-defined type) doesn't necessarily mean "managed code". You can define a straight T-SQL based UDT and use it. CREAET TYPE ZipCode FROM varchar(10) NOT NULL ;Not saying it's great and recommended - but it's not necessarily managed code....
marc_s
2009-03-18 19:05:43
A:
How cool is using an Object-Relational Mapper and not having to deal with the database at all? Once you try it, you won't go back.
Justice
2009-03-24 13:20:34
@Justice I really dislike ORMs :( While more work upfront I like to design a DB using less surrogate keys (although I am not a "no-surrogate"/"all natural key" advocate) *in context of the well-defined data-access which can be performed on it*. Or, alternatively, I like to use "No SQL" DBs.
pst
2010-05-29 15:59:52