User Defined Data Types in SQL Server 2005
-
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.
-
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)?
-
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
whoisactive -
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.
-
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?
-
> 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...
-
Poor bastard is the one who has to make the difference between the col with uddt and the 3/4 part naming of objects
. -
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
-
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.
-
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.
-
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
whoisactive -
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.
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply