Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: User Defined Types

    I've been doing some reading on UDT's and have a question...

    It was suggested in one of the pieces I read that you could create a UDT for, in their example, cities. And every table that had a reference to city could share this datatype. Now, my initial thought was "wow, what a great idea! I wouldn't have to remember the exact datatype for my primary keys (was it a char(5) or char(6)?) and have a "central depository" for my key datatypes.

    So the first question is; what are the disadvantages of such a design?

    And the second is; How do you update a UDT? If business requirements change and udt_city needs to be changed from varchar(30) to varchar(60), for example, what would be the way of echoing the change thoughout your database?

    My gut reaction for the answers are
    1) performance will decrease as effectively the dbms has to "parse" every insert/update to a UDT field in a different method.

    2) create a new udt and alter any tables referencing the old one before dropping it.

    What do we think?
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    UDTs are great.... in theory. Changing a UDT that is in use (especially as a clumn that participates in a relationship) is a nightmare. Your step 2 needs to include removing all foreign key constraints before making the changes since they are changed serially and SQL Server won't allow that.

    Blindman hates the way they script....

    I think they are really powerful when combined with rules BUT rules are depricated UDTs combined with check constraints would rock and be worth the effort but as they stand I don't rate them highly enough to use them.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - once again you are caught not reading Jeff's blog:
    http://weblogs.sqlteam.com/jeffs/arc...ata-types.aspx


    Seriously - this guy has blogged almost everything you have ever asked

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yet another level of obfuscation.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots, I have that link bookmarked already
    But it doesn't exactly answer any of the question I mentioned above.

    Thanks for the explanation though.
    So they're not in 2008... What's the deal? I will probably leave them from now if they're deprecated.; hey just seemed like a cute idea!

    Have any of you ever used them in a producton database? If so, what for and why?
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    UDTs are useless at best, misleading at worst.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - they are in 2008. It is Rules that are deprecated. You can associate rules with UDTs. In combination they are excellent IMHO. For example, specifying a telephone number field - VARCHAR(20) with some associated rule that specifies numerics and spaces only - no alphabetic characters. Obviously there could be more complex types, including binding business rules to types.

    In my hazily recollected studies I think I learned these as Domains in relational theory (Rudy - are Domains ANSI?). I am fairly sure that Domains as I learned them would be awesome if they were fully incorporated into SQL Server... but they are not.

    And no - I have none in production. Or development for that matter.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    gee sounds like a check constraint to me. i do not use them but the one thing they can buy you is consistency across different tables.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    gee sounds like a check constraint to me. i do not use them but the one thing they can buy you is consistency across different tables.
    Yes - but the UDT (in theory) would give you consistency in your check constraints across tables.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    anybody hear an echo?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Yes - but the UDT (in theory) would give you consistency in your check constraints across tables.
    "In theory, practice and theory are the same. In practice, theory and practice are different."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by pootle flump
    ...For example, specifying a telephone number field - VARCHAR(20) with some associated rule that specifies numerics and spaces only - no alphabetic characters...
    But my phone number is Klondike 5-2856

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley
    But my phone number is Klondike 5-2856
    I don't have to deal with "Americanizations" over in good old you-know-where-you-are-because-when-a-field-says-phone-number-it-means-phone-number Blighty.

    In any event it was an example.

    Blinking pedants

  14. #14
    Join Date
    Jul 2007
    Posts
    96
    I've used UDT twice and I don't really regret it. However I must confess that it was for things that are not likely to change in my life time and the next.

    In my MCTS: SQL Server 2005 classes a couple of days ago the trainer pretty much agreed with blindman ^^

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I start mine on Monday - how're you finding it?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •