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 Friday, December 20, 2002 6:27 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
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.




Post #49487
Posted Monday, January 13, 2003 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 22, 2004 1:20 PM
Points: 3, Visits: 1
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




Post #49488
Posted Thursday, January 16, 2003 5:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 21, 2003 12:00 AM
Points: 728, Visits: 1
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.




Post #49489
Posted Thursday, January 16, 2003 6:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 2,865, Visits: 1,705
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!



LinkedIn Profile
Newbie on www.simple-talk.com
Post #49490
Posted Thursday, January 16, 2003 7:02 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 21, 2003 12:00 AM
Points: 728, Visits: 1
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.




Post #49491
Posted Wednesday, February 01, 2012 10:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 23, 2013 8:15 AM
Points: 63, Visits: 428
For those who take offense at the resurrection of old threads, I apologize, but there is an important issue that has arisen with the passage of time. Microsoft now says that the DEFAULT and RULE constructs are deprecated and should not be used. Seems to me that doing that invalidates a lot of the value of UDTs. Also, you can't use IDENTITY with a UDT.

Jay Bienvenu | http://bienv.com | http://twitter.com/jbnv
Post #1245212
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse