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 Wednesday, August 3, 2005 9:50 AM


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

What's infeasible about having a class called Address in an OO language? I don't see any problem with that. You would have properties like StreetNumber, StreetPreDirectional, StreetPostDirectional, StreetName, City, State, PostalCode, etc.

There aren't too many operators, but I can think of at least one:

DistanceFrom(Address other)



--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #206926
Posted Wednesday, August 3, 2005 10:03 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

What's infeasible is performing scalar operations on it:

Address1 = "12 West Broadway, New York, NY 10036"
Address2 = "941 Market Street, San Francisco, CA 94109"

Address3 = Address1 + Address2

Address4 = Address1 - Address2

IF Address1 < Address2 THEN ...

As you pointed out, I don't see any advantage to a DistanceFrom "operator" over creating it as a UDF.

Post #206933
Posted Wednesday, August 3, 2005 10:24 AM


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


Are + or - scalar operators _on the Address type_? Or are they scalar operators on OTHER types?

What is or is not a scalar operator depends on the type. DistanceFrom is a scalar operator on the Address type -- but is not an operator for the integer type.


Remember, scalar != primitive.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #206944
Posted Wednesday, August 3, 2005 12:42 PM


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

So, how exactly do we implement a DistanceFrom() operator without encoding our Addresses using a standard UOM?  (like lat/lon for instance)?  There is nothing intrinsic to this particular type, as presented, that tells us we can perform any useful operations of any type on it, without first converting to a standard UOM (see previous post).  This type so far consists of:

StreetAddress
City
State
ZIP Code

And of course the DistanceFrom() operator that uses the information above to calculate the distance between addresses.

Just like with the InventoryItems, etc., what type of useful calculations can you perform with the above without first converting it to a standard UOM and performing the calculations on that encoded data?

I think I already mentioned the need for these UDT's to have some sort of encoding ability as well as some type of conversion ability (either implicit or explicit) to standard UOMs in order to get any type of useful result.

The distance from '123 Sesame St' to '1600 Pennsylvania Ave' is 17.

 

*Levenshtein Edit Distance

Post #207048
Posted Wednesday, August 3, 2005 12:50 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
Who says there's nothing on the type but those properties?

How do you know there aren't private members that hold the geocode?

How do you know that the DistanceFrom() operator can't communicate with a map server?

The key here is encapsulation. You don't know -- and as an end user, you shouldn't have to know -- how it works. It just works. As a DBA or developer, you know the details. But you don't need to expose them to the world.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #207058
Posted Wednesday, August 3, 2005 1:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1

As you pointed out, I don't see any advantage to a DistanceFrom "operator" over creating it as a UDF.

 

The advantage for the UDT is that it is a CLR, which means it's accessible in the middle tier.  You don't have to connect to the SQL Server to get access to the business rules tied into the UDT.  This is right up the alley of the DBA who believes that business rules should never be written in stored procedures.  This is extremely threatening to the DBA who believes that business rules should never be written anywhere else.  This might be a bridge solution where the developer can design what he/she wants stored and the DBA can keep the schema away from the ignorant developer while still being able to see what is being stored on "his" DB.

 

Never underestimate the capacity for idiotic behavior in any human.  I've been on the programmer side, where I know what data I want stored, but have to pass the request on to a DBA who can't do what I want done unless I correct his "solution" 15 times.  I've been on the DBA side where I've seen the devastation a programmer can do to a DB because they don't understand what they are doing and are allowed to modify the schema.

 

I can see it now, the DBA doesn't allow direct access to the table and will only allow the developer to send/receive the properties of the UDT in sprocs.  And of course the DBA screws up what properties are allowed in the UDT.
Post #207079
Posted Wednesday, August 3, 2005 1:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:51 PM
Points: 1,244, Visits: 3
These UDTs won't be terribly useful if they exist just in the database. They are a liability in that case. The real value is in the fact that they can be used in the database and in applications. The same stuct source code is used in both places.

There are certainly limits to their usefulness if the the UDTs cannot have methods associated with them since they seem to be defined as "struct" (IIRC from the article). Applications would probably add wrapper classes with methods that provide useful functions, but at least the basic data definition is in one place.

Does someone know for sure that these have to be defined as "struct" and can structs have private members and/or methods (public or private)?



Post #207084
Posted Wednesday, August 3, 2005 1:42 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
Peter:

Structs can have methods, but no code access attributes (everything is public).

UDTs can be defined as either structs or classes. If you define one as a class, you must inherit IBinarySerialize.

Shameless plug: See my upcoming book for more info on the topic


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #207085
Posted Wednesday, August 3, 2005 2:01 PM


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

Who says there's nothing on the type but those properties?

We did.  Look at previous posts; these are the only properties and methods defined so far.

How do you know there aren't private members that hold the geocode?

Because they haven't been defined as of yet.  Again, see previous posts.

How do you know that the DistanceFrom() operator can't communicate with a map server?

As long as we're going the "how do you know ... can't ...", how do we know that the DistanceFrom operator can't make a darn good cup of coffee, toast a bagel or wash the dishes?  We know because it hasn't been defined.

The key here is encapsulation. You don't know -- and as an end user, you shouldn't have to know -- how it works. It just works. As a DBA or developer, you know the details. But you don't need to expose them to the world.

The key here is definition.  Whether public, private, friend or whatever you like; if it ain't defined, it don't exist.  Now you're talking about geocoding functionality.  Great!  Just like I said previously (in more than one post here), let's 1) Define the encoding interfaces and 2) Define the standard Units of Measure.  Then we can 3) Generate *useful* results.

Post #207089
Posted Wednesday, August 3, 2005 2:08 PM


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

If this is the case, what exactly is the advantage of creating a UDT in SQL Server and allowing access to it as opposed to just creating a Class in a traditional middle-tier app which coordinates access between the front end GUI and the back-end SQL Server?

Post #207093
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse