SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User-defined Data Type Basics


User-defined Data Type Basics

Author
Message
don1941
don1941
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dfrazier/userdefineddatatypes.asp



ckempste
ckempste
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1813 Visits: 1
Hi

I hate UDT's, they are hopessly deficient, why?

a) i cant easily change the "real" type for the udf once bound to N tables
b) they can be a nightmare to manage when moving data between dbms versions in terms of compatibility and debugging truncation issues etc.
c) can cause future issues, namely, a business rule change may result in the name used for the UDT to become confusing and not reflect its actual purpose (although this is a standards issue more than anything).

To be truely helpful, you must be able to change the type and for the change to dynamically cascade through the dbms. If it doesnt happen automatically, then I see no benefit from them.

Try harder Microsoft ! Smile

Cheers

Chris.


Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
jcool
jcool
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 10
quote:


I hate UDT's, they are hopessly deficient, why?

a) i cant easily change the "real" type for the udf once bound to N tables
b) they can be a nightmare to manage when moving data between dbms versions in terms of compatibility and debugging truncation issues etc.
c) can cause future issues, namely, a business rule change may result in the name used for the UDT to become confusing and not reflect its actual purpose (although this is a standards issue more than anything).

To be truely helpful, you must be able to change the type and for the change to dynamically cascade through the dbms. If it doesnt happen automatically, then I see no benefit from them.

Try harder Microsoft ! Smile




I agree. Not only you can't change it after it has been bound to a table, there are other problems. I've had problems using it inside UDF (function). You can't create temporary tables (#table) if UDT doesn't exist in tempdb. If you create it in tempdb, you loose it everytime SQL Server is restarted and tempdb is initialized.
Enterprise Manager needs some more functionality to support UDT change and modify all tables that us it.



wodom
wodom
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 256
Yeah, back when I found out about UDT's I was all hot to use them, until I wasted a lot of time searching through the docs for how to CHANGE them. Practically the WHOLE POINT of user-defined-anythings is so you can CHANGE them at a single point and make it affect the whole application.



Scorpion_66
Scorpion_66
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 26
I learned my lesson with them while scripting the database. Ha, that was a joke. One that took me most of a weekend to pinpoint and correct, on a then live database. Do I need to say that it wasn't funny. Once I was aware of them, it proved to be less of a problem, but gimme a break. I should be able to re-create a database schema by scripting it out and running the script on the new server.



mromm
mromm
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 1
Friends, you are all right about problems with UDT. However I always use those and even made it a standard because this is the only reliable way to maintain data domain integrity in SQL Server. Otherwise you will end up with, say, money stored as float, int, varchar, etc. in different places throughout your enterprise. In addition I like the fact that you cannot drop a UDT if it has dependencies.



Scorpion_66
Scorpion_66
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 26
Sorry mromm, I don't mean to pick, but quite honestly, If you cannot enforce data domain integrity reliably in your SQL Server, what makes you think you can enforce the usage of a UDT. From my perspective, its just as easy to enforce the fact that a money field is created as a money field as it is to enforce that a money field is created as a UDT. I don't see that as a benefit.

It could actually be viewed as a deficit if the dba's get to relying on it rather than researching their field definitions against the data library.

Edited by - scorpion_66 on 12/16/2002 9:21:05 PM



lambje
lambje
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 1
My experience with UDTs has been in Sybase but I won't use them anywhere again.

I use to think UDTs where pretty cool about 9 years ago when I was told that was the standard at the company I was contracting with. Then the nightmare began.

The DBAs were always fighting battles when new UDTs were created to make certain they were proliferated to all databases on all servers. Quite a heroic effort. Then problems were encountered because what-you-see-is-not-what-you-get, that is, you may specify the data type in creating a table just to have it fail on some other database you replicate to because some how the object IDs of the UDTs are off.

Well, I'm still contracting with the same firm and they have come full circle to the extent of taking the hit on updating their extremely vast amount of schema to replace all UDTs with native data types. I like this a lot better. We do still use column naming conventions, though, which can help one to figure what general data type is used (e.g., col1_id, col2_nm, col3_amt, col4_tms...). This is most helpful when writing stored procs as you can pretty much figure out where you need to do a CONVERT without having to look at the data type.

I also must say I agree with Scorpian_66. If your programmers can't figure out how to keep the native data types in line, what makes you think they will use the right UDT? They're actually more apt to get things totally wigged out and you'll end up with a total mess with a rash of UDTs with the same native data types and no one will be able to figure out which one they should use.



David.Poole
David.Poole
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10104 Visits: 3341
I'm surprised that people are so against UDTs.

I tend to regard my database servers as flexible when they have to be, but I don't encourage changes to schemas unless absolutely necessary.

Frankly if your schema is in a regular state of flux and you are not in a development environment then there is something seriously wrong with the design or analysis behind the database. Possibly the DBA has been given the title because it is one he can spell!

I have a standard set of UDTs that I place in my MODEL database, hey presto, no more worries about UDT propogation when new databases are created. This included TEMPDB.

In addition, all my UDTs are created in a script, so it is easy to propogate them to every existing database on the server.

I tend to have types such as
  • ty_int an integer that defaults to zero and does not allow nulls.

  • ty_reqdDesc a 50 character NVARCHAR field that does not allow nulls or allow zero length strings
  • ty_Yes a bit field defaulting to Yes
  • ty_No a bit field defaulting to NO.
  • ty_Today smalldatetime field I use for timestamping.


As for changing UDT's, what is so hard about ALTER TABLE tbl_section ALTER COLUMN SectionDescription ty_reqdDesc?

A schema change, which is implied by modifying UDTs, should be a rare and managed (planned) event.
You need scripts:-
  • To propogate any new types.
  • To drop any indices on the columns being changed.
  • To drop any DRI using the columns.
  • To apply your extra UDTs or refresh the old ones within the table
  • To recreate indices
  • [*]To reinstigate DRI

In other words bread and butter DBA stuff.

LinkedIn Profile

Newbie on www.simple-talk.com
mromm
mromm
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 1
My compliments to David Poole for his last comment on the topic.

I also would like to add the following. UDT similar to ty_int does not serve the purpose of enforcing domain integrity. My preference is UDT similar to udt_user_id, udt_yes_no_bit, udt_phone, udt_address, udt_client_code, etc. In other word, UDT should be business oriented.

Any opinions?

Michael



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search