User Defined Data Types in SQL Server 2005

  • Comments posted to this topic are about the content posted at

  • You've talked alot about Serializable e.g. Declaring the UDT as Serializable allows the UDT to be Serialized. Well ur, yeah, obviously! But what does that mean? What can you do with a serialized UDT that you can't do with a non-serialized UDT? This needs expanding on!

    Other than that, not a bad article. A nice introduction.




  • Anyone looked at the performance implications of this? For example, if we had a few tens of thousands of records in that stock table, and needed the total of both stock level columns across all stock items (meaningless, I know, but for sake of example), how would it compare to summing the native columns?

  • This type of example is exactly the reason why I and many more or 'scared' (or at least concerned) about the CLR Integration in SQL Server 2005. I see absolutely no reason whatsoever to create UDTs that combine two scalar values (in fact I have yet to see any example of a good use for CLR UDTs). This gives no benefits at all, but a lot of problems will come from it. The complexity is much higher, for no reason. The portability is completely lost. Displaying the type will be difficult in many tools (probably will just show a binary). And it is a violation of 1NF, so one might wonder why a relational DBMS is used anyway?

  • The choice of example seems reasonable for demonstration, but does not seem at all reasonable for a real-world scenario.

    The author asks the rhetorical question, "Why do we not have a single column for this purpose?" I think there are a lot of reasons, including: normalization (and all of its implications), the meaning of null if one value is known and the other is not, performance in evaluating column expressions and where-clauses, difficulting in using these types in ad hoc query or reporting tools, and so on.

    These types, especially as defined through the CLR, should be used sparingly and only in situations where the values will be manipulated in an application where such structures are handled easily. SQL just isn't optimal for complex types and I have seen very very very few instances of user defined types in real applications in SQL Server, or even Oracle for that matter. Where they have been used, UDTs were not used in identifying rows or as basic attributes but represented whole objects and were processed in high level languages, not SQL, one at a time.

    Have others seen widespread used of UDTs? Oracle's had this capability for a long time, and it is still rare to see UDTs in applications so I doubt that they'll catch on too much in SQL Server, except that Visual Studio will make it too easy for developers to add these.

  • Peter,

    That last point is a good one. IMO developers should never be let loose near a relational database and that is truer with the advent of SQL2005 than ever before.



  • Can anyone think of any good example for UDTs to be used? Peter, what exactly might be an example of the "whole object" that would fit well in a UDT?

    Just trying to learn.



  • > Displaying the type will be difficult in many tools

    > (probably will just show a binary)

    I've not had a chance to install sql2005 yet - do the tools provide any support for working with these types of columns, or can you only access them through code?

    > only in situations where the values will be

    > manipulated in an application where such structures

    > are handled easily

    I think that's the key. I've been trying to think of anywhere we might like to use them, and the only one I've come up with in one of our apps so far is international phone numbers. We let the user select an international prefix from a drop down list of countries, and then have seperate entry boxes for area code and number. These are then stored as three seperate fields. From the SQL point of view there's little advantage to this (I suppose it would allow us to easilly update should an international dialling code change), so I might be tempted to use a UDT for such a field. Whether it would be worth any ensuing problems though is another matter. It doesn't appear to offer me any real benefits though - all our updates are done via stored procedures, so I'd presumably still have to pass three parameters into the proc. Hmmm

  • Here's an example...

    Let's say you are writing an application that presents a bill. That bill should not change over time and it's attributes won't be queried on for aggregation or any other ad hoc reporting scenario, because those queries will go to source data. However, you'll want to store a snap shot of that bill to be able to reproduce it later when the account holder requests it on the web site.

    A table that stores those snap shots might have an primary key identity column and maybe an owner column to allow getting that one bill but the bill itself may be stored as a UDT in a single column to simplify access and leave the data structure interpretation to the application. It could contain all the info that normally is in the bill, perhaps an image, and may even include an electronic signature, etc. to ensure that the bill's values haven't been changed.

    Certainly simple bills could easily be stored as an old fashioned row, but complicated bills with detail records, special rate plans, etc. might be better stored this way rather than with many tables that have been normalized and maintain referentially integrity through constraints.

  • Peter, that is an excellent example. I think I understand the concept now.



  • Kevin has asked a question that bothers me as well.

    Doesn't anyone have an answer to it?


    When using a large dataset ( 250,000 rows), and aggregating data, or comparing to the data, is there any difference in performance between SQL native datatypes or UDF's?



  • The best example I've thought of so far is a rational number datatype. But I haven't yet figured out how to write it, given the way UDTs sort (based on their serialized binary) -- a non-sortable rational number UDT would be relatively useless.

    Adam Machanic

  • I would argue that storing snapshots of a bill, separately from the actual bill data, is a poor design decision.  If a bill is to remain the same, then the underlying data should remain the same.  If adjustments need to be made to a bill, they should be made via debit or credit memo; or via the issuance of a new and updated bill that supercedes the prior bill.

    So far it sounds to me as if the new UDTs are simply a way for non-SQL developers to force a non-SQL solution onto SQL.

  • Hey Adam,

    What about base conversion or encryption UDT's?  It seems like they might be prime for something like that involving simple scalar data.  Just wondering...  Thanks.

  • For those of us (noobs) that had to look it up... a rational number is a ratio with a numerator and a denominator that you want to keep as such (and not divide to get a actual decimal value), right? Another good example, me thinks!



Viewing 15 posts - 1 through 15 (of 56 total)

You must be logged in to reply to this topic. Login to reply