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


User Defined Data Types in SQL Server 2005


User Defined Data Types in SQL Server 2005

Author
Message
sara karasik
sara karasik
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 95

Kevin has asked a question that bothers me as well.

Doesn't anyone have an answer to it?

Again:

When using a large dataset ( 250,000 rows), and aggregating data, or comparing to the data, is there any difference in performance between SQL native datatypes or UDF's?

Sara


Adam Machanic
Adam Machanic
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: 1141 Visits: 714
The best example I've thought of so far is a rational number datatype. But I haven't yet figured out how to write it, given the way UDTs sort (based on their serialized binary) -- a non-sortable rational number UDT would be relatively useless.

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

I would argue that storing snapshots of a bill, separately from the actual bill data, is a poor design decision. If a bill is to remain the same, then the underlying data should remain the same. If adjustments need to be made to a bill, they should be made via debit or credit memo; or via the issuance of a new and updated bill that supercedes the prior bill.

So far it sounds to me as if the new UDTs are simply a way for non-SQL developers to force a non-SQL solution onto SQL.


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

Hey Adam,

What about base conversion or encryption UDT's? It seems like they might be prime for something like that involving simple scalar data. Just wondering... Thanks.


N Cook
N Cook
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 22

For those of us (noobs) that had to look it up... a rational number is a ratio with a numerator and a denominator that you want to keep as such (and not divide to get a actual decimal value), right? Another good example, me thinks!

Thanks,
Nate


Dinesh Priyankara
Dinesh Priyankara
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 115

OK, It seems that the example I have taken has caused you all to think about UDTs in different way. Actually, I just want you to show the way of developing the UDT.

But, Why can't I use "ItemStock" if it is really need for the business? Though it can be simply implemented with two columns, if the business really care this as a single unit, I think we should use our own type. Can I compare this scenario as having a datetime type for Date instead of having seperate columns for d, m, and y? Or should not I campare?


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
I still have to see a place where it's advantageous to split those 2 infos. Can someone think of an exemple where it takes less space and is faster to query in 2 different columns?
sara karasik
sara karasik
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 95

If you must keep the data as one column, you can always create a calculated column on the table.


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
I think this is the main problem at the moment. We already have many correct ways of making the model work, so why would we have to switch to this one??
DavidMcAfee
DavidMcAfee
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 19

I deal with a system called Ramco. Ramco uses SQL Server 2000 as its BE. The designers of this system, really need to be given a lesson in normalization and to not use UDTs as they do. Just about every field is a UDT.

Fields such as Address are not Varchar(40), but rather CMN_DESCRIPTION (which when looked up is a Varchar(40). This one database alone has 962 UDTs!!!

Merging to numeric values (assuming stock numbers are numeric) into one concatenated text field, is IMO not a good thing to do. This reminds me of Access developers that use lookup fields, rather than storing an int. too confusing later on.

Another reason I dont like UDTs is that you cannot use them in table variables. I don't remember if you canuse them in temp tables, but you can't in table variables (at least not in SQL Server 2000)



David



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