Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User-defined Data Type Basics


User-defined Data Type Basics

Author
Message
Scorpion_66
Scorpion_66
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
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.



Angie
Angie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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



jpipes
jpipes
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
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.



David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3726 Visits: 3121
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
jpipes
jpipes
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
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.



jbnv
jbnv
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 513
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
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