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 «««23456

User Defined Data Types in SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, August 4, 2005 5:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:35 AM
Points: 31, Visits: 79
> How do you know that the DistanceFrom() operator
> can't communicate with a map server?

Now that would be fun, the first time the user does

SELECT * FROM AllHousesInUSA WHERE Address.DistanceFrom>120

Performance might be a bit, suboptimal...



Post #207278
Posted Thursday, August 4, 2005 6:28 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 20,584, Visits: 9,624
Poor bastard is the one who has to make the difference between the col with uddt and the 3/4 part naming of objects .
Post #207302
Posted Friday, May 25, 2007 2:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 3:55 AM
Points: 92, Visits: 223

Or you could just store it as a block of XML?

Nope, doesn't cut it for me yet.  Nice idea, but I still don't see a point in it, and have big concerns over the performance impact.

Craig

Post #368934
Posted Friday, May 25, 2007 10:42 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 5:29 AM
Points: 104, Visits: 721

Interesting discussions so far!

I guess I was swayed by the opinions of others in this thread over the usefulness of UDTs but now I can see why you'd want them. Basically a UDT is no different than say a DateTime type, for example. Internally this is stored as a number but because it's a specialized type it has its own operations, like DateDiff, and operators can act differently when there are DateTime operands involved. With a DateTime type if you add two together using the + operator you get a new DateTime. For instance if you execute this

select cast('1/1/2007' as datetime) + cast('2/1/2007' as datetime)

you get the DateTime 2/1/2114, but if you execute this

select '1/1/2007' + '2/1/2007'

you get the string '1/1/20072/1/2007'. Basically the + operator is overloaded in OO lingo.

So for a UDT you could have the AddressLine type mentioned earlier in the thread and it would have its own operations, like DistanceFrom, and operators can behave differently with it. Perhaps the + operator would be invalidated so if you tried to add two together you'd get an error. The value of a UDT is determined by the person inventing / using it. The built-in types of SQL Server are just types generally thought to be useful, that's not to say an application can't find a value in using another type. For instance, treating a string not as a string but as a date.

Post #369110
Posted Friday, May 25, 2007 11:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 7:59 AM
Points: 40, Visits: 49

I want to further validate comments made by a number of people. For demo purposes, this works fine, but it is a bad example of a real-world UDT. This point needs to be made very clear in the article, and it is not. It is even implied that this is a good real-world use, as shown here:

"The physical stock and system stock are stored separately in two different columns. Why do we not have a single column for this purpose? This thought came up as soon as I saw my own design."

This is not a reason for creating a UDT. The purpose of UDTs is to extend the type system, not combine existing types into a more complex type. It was also stated that:

"...this little scenario is going to be used for our first UDT, though a little simple."

Making a more complex type to represent 2 perfectly good types already in the relational design does not simplify, but rather, complicates the database design. Issues arise, such as querying against system stock. Based on the implementation, this would not be doable with any efficiency since the native serialization would serialize the physical stock first. The only way to efficiently query against the system stock value is to now create a computed column of the system stock and then index that computed column, which defeats the stated purpose of creating this UDT in the first place.

Don't get me wrong, I think CLR integration is a great feature, but must be used with prudence.

Post #369124
Posted Friday, May 25, 2007 12:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705

Agreed with Peter.  The top uses I see for UDTs are:

A) Extension of the type system with new types and associated operators (not too many of these)

B) Binding of domain validation rules to types (similar to rules in previous versions of SQL Server, but bound at the type level rather than the column level)

Creating complex types does not work well due to the inefficiencies they bring to the table... (pun slightly intended? )

 



--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #369138
Posted Friday, May 25, 2007 7:49 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

Points are taken. I agree with Peter too. The whole purpose of this article was giving an introduction of creating UDT with CLR components and I tried my best to explain it. But the sample I had taken is controversial that because I have considered only the requirements I had at that time. I tested this implementation with a sample database and had no performance problems though it has violated some general rules. I may be testing the implementation with VLDBs to check the performance. It may be worthwhile implementing calculated column for maintaining difference between stocks rather than maintaining an UDT. Let me do the load testing and see. Apart from this, hope I have shown the way of creating UDTs understandable.

I appreciate all of your comments on this.

Post #369211
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse