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 ««12345»»»

User Defined Data Types in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, August 2, 2005 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:57 AM
Points: 133, Visits: 89

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

 

Post #206510
Posted Tuesday, August 2, 2005 11:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 11:06 AM
Points: 1,113, Visits: 706

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
Post #206572
Posted Tuesday, August 2, 2005 11:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #206573
Posted Tuesday, August 2, 2005 11:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #206577
Posted Tuesday, August 2, 2005 11:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 5:54 PM
Points: 99, 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

Post #206581
Posted Tuesday, August 2, 2005 12:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

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?

Post #206592
Posted Tuesday, August 2, 2005 12:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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?
Post #206595
Posted Tuesday, August 2, 2005 12:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:57 AM
Points: 133, Visits: 89

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

 

Post #206598
Posted Tuesday, August 2, 2005 12:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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??
Post #206599
Posted Tuesday, August 2, 2005 12:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:46 PM
Points: 34, Visits: 17

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



Post #206607
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse