SQL Clone
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 (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1613 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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2741 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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2741 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 (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 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 Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29877 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 (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29877 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 Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

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