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
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
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
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: 1351 Visits: 1168

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.


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
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
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: 1351 Visits: 1168

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


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
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
Kenneth Lee
Kenneth Lee
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
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.
Peter Kryszak
Peter Kryszak
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1244 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)?



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
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
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: 1351 Visits: 1168

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.


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: 1351 Visits: 1168

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?


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