User-defined Data Type Basics
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dfrazier/userdefineddatatypes.asp
-
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" -
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.
-
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.
-
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.
-
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.
-
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
-
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.
-
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.
-
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
-
When you say you script them, have you applied the script to a different server? One without UDT's defined on it already? And it worked?
And when you say,
"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."
I totally agree. However, as a contractor, I'm the guy who walks in to clean up this very situation once they decide the network admin wasn't the guy to do the database design after all. In almost all cases, there is no documentation, the database is in a state of failure in some way, it's a production system and mission critical to the business using it, and there are no standards of any kind established, and when you say "Structured Design Methodology" to the IT manager, he says "Method what?" The last thing I want to have to deal with is figuring out what the heck the dude was doing with UDT's. The first thing I want to do is set up a test enviroment, by scripting the database structure and re-building the database on a different server, generally, a Desktop Engine. Why not just restore a backup, you ask? Generally, disk space. Sql Server is great about optimizing its queries and such and using statistics, so the database has to be quite large to overcome this to the point of pain that causes the company to call me. And generally, companies supply the machine they have, and have never even thought about a standby server, development enviroment, Qa Enviroment, etc. Even in the ones who have, budget issues are universal, and generally you make do and do the best you can for them. I'm working for a well developed international company right now that has a massive workforce, on a P III, 128MB Ram, and 8 GB HD, and it has to be my development enviroment. And then lastly, once I get things in order, I'm done, my contract is over, and regardless of the advice I give, they don't hire a DBA, but turn it back over to whoever. Which means, that I am unable to say what will happen once I'm gone.
So, it's not that I'm particuarly against them, it's just that they couldn't possibly work in a lot of the enviroments I work in. And it cost me, finding out about the scripting thing the hard way. I wouldn't want others to suffer this, if they could be aware of it up front. I'm sure that in an enviroment such as you describe, where changes are rare and managed, they would be fine.
-
I'm new to this so please be patient. I read this article (and the related responses) and learned so much - thanks! I started looking into using them with my next task. And like several people here found that using something like udt_id or udt_user worked best for maintaining 'business' rules. Everything works great when creating new tables. The problem I've run across is that when I add columns to an existing table (in a script), the NOT NULL property and the default is ignored. Is there something I'm doing wrong or does this not work with, for example, ALTER TABLE address ADD COLUMN addr_created_by udt_user?
Thanks,
Angie
-
quote:
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!Some companies/industries have neither the luxury of semi-static schemas, nor the privelege of being able to ignore customer requirements - requirements that change, and change often. Say you make a UDT for a serial number, say CHAR(12) NOT NULL, and then 2 months down the road, you take on a customer that has 13-digit serial numbers. You might say, well, why didn't you just make the field a VARCHAR(20) to start with? a) Don't always have the choice (see scorpion's comments above), and b) What about 25 or 30 characters long? Sometimes there isn't a way to know all the details up front...
Having UDTs is, I believe by design, inflexible. Changing the underlying datatype of a UDT is a sever PITA (see below), and doesn't grant a DBA, or a programmer for that matter, any more benefit than an Excel spreadsheet with a list of commonly used business data types. Until the ability to *easily* change an underlying datatype is given, UDTs will go largely by the wayside.
As for ty_Today and ty_Yes, I say, isn't that going a bit far???
quote:
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.
In other words, boring, tedious tasks that should be programmed into the SQL Server environment by MS in the next release (see comments above by ckempste). Bread and butter, maybe, but for those of us who don't have the time to spend scripting up such tedium, an automated tool would allow for time to be spent on (much) more important things.
Another note: ckempste's point c) cannot be stressed enough.
-
I agree with your last point that there should be a tool to do the laborious stuff for you.
I agree that it would be handy to be able to do a mass application of a change simply by altering the udt.
I think it is a case of matching the tool for the job. If you need to be changing your field types then don't use udt's.
As a DBA I personally find that being able to wrap up a data type, default value and rule in one handy container is very useful.
Yes business rules and as a DBA I have to recognise that my users are my customers and let no man be unworthy of the angel that is sent him!
There has to be a caveat. George Bernard Shaw wrote that the reasonable man attempts to accomodate all changes and find compromises for all situations and therefore all progress is left to the unreasonable man.
I understand that you probably have a legitemate need to amend schemas and datatypes on a regular basis, I'm not criticising you!
In my situation this is the equivalent of a user saying "can you just do this..." which means scope creep, software rust, missed deadlines and lost profits.
I've seen the situation where accomodating the user requirements resulted in a contradiction between the functional requirement spec and the delivered system. The user (who had requested the changes) then criticised us for failing to meet the FRS, even though they had requested and designed the changes. That's working for government for you!
-
quote:
There has to be a caveat. George Bernard Shaw wrote that the reasonable man attempts to accomodate all changes and find compromises for all situations and therefore all progress is left to the unreasonable man.Bill Cosby: "I don't know the key to success, but the key to failure is trying to please everybody."
I hear you.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply