Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

User-defined Data Type Basics Expand / Collapse
Author
Message
Posted Thursday, December 12, 2002 12:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2003 12:00 AM
Points: 299, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dfrazier/userdefineddatatypes.asp


Post #8782
Posted Sunday, December 15, 2002 6:31 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, 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 ! :)

Cheers

Chris.




Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #49478
Posted Monday, December 16, 2002 2:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 21, 2010 7:15 AM
Points: 77, 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 ! :)




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.





Post #49479
Posted Monday, December 16, 2002 2:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 134, Visits: 192
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.




Post #49480
Posted Monday, December 16, 2002 5:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 11:06 AM
Points: 593, 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.




Post #49481
Posted Monday, December 16, 2002 5:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 30, 2004 8:52 AM
Points: 253, 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.




Post #49482
Posted Monday, December 16, 2002 9:13 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 11:06 AM
Points: 593, 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



Post #49483
Posted Thursday, December 19, 2002 9:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 15, 2003 12:00 AM
Points: 91, 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.




Post #49484
Posted Friday, December 20, 2002 2:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,908, Visits: 1,834
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
Post #49485
Posted Friday, December 20, 2002 11:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 30, 2004 8:52 AM
Points: 253, 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




Post #49486
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse