User Defined Data Types in SQL Server 2005
-
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?
-
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?
-
If you must keep the data as one column, you can always create a calculated column on the table.
-
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??
-
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
-
The problem is that we're de-normalizing the data using UDT's. It also seems that all the kinks aren't worked out on the MS end yet. It seems to me that this might come in handy in certain situations (depending on the final implementation), but for the most part it seems like more of an attempt to apply OO design principles to SQL.
Just as an example, if you wanted to store length values and their associated units of measure as a UDT. The only way I could see this being even remotely useful would be if all computations on the length values were converted to a common unit of measure by the UDT itself during the computation. For instance, if I wanted to perform a computation like "12 feet" + "5 inches" + "3 meters" + "98 centimeters", the UDT itself would need to convert the values to a common UOM (let's say centimeters).
What unit of measure the result should be returned in? Should our result be in feet, inches, meters?
Additionally, with UDT's we also need them to sort correctly on our display. i.e., Does "98 centimeters" come before or after "5 inches" when we perform a query and sort by the length value?
The standard way of dealing with a situation like this is to convert each value to a common unit of measure before we store it in the database (we could convert all length measurements to meters prior to storage, for example).
Overall you wrote a good article, but there are just a bunch of questions concerning the new UDT implementation that aren't likely to be resolved until MS releases the final product.
-
Mike,
I think that conversion and encryption would be great for UDFs -- but I don't really see the point of having an 'encrypted' type, personally. What benefit would that have over simply storing the data in a VARBINARY(MAX) column?
--
Adam Machanic
whoisactive -
The problem with this approach is that we would be using a relational DBMS (not truly relational, but still) for storing the data, but for some reason not using the existing relational operations to manipulate it. Instead of using functionality that is based on proven theories more than 100 years old we would instead need to implement our own operations, which offer no benefits over the former said.
-
Why not simply store it in a VARBINARY(MAX))?
The job of a DBMS is to preserve the integrity of the database. The only way it can do that is to 'know' everything it needs to know about the data, which it does by enforcing constraints. If this bill is to be treated only as a blob of data, and the only integrity that needs to be maintained is that a bill has an owner (for instance), then storing it in a VARBINARY(MAX) is enough. If the database needs to know more, for instance the date when the bill was created, then it needs access to that information. To access that information in a UDT in this way requires us to implement our own methods, instead of designing the data so that the relational DBMS can use relational operations as it should.
Certainly simple bills could easily be stored as an old fashioned row, but complicated bills with detail records, special rate plans, etc. might be better stored this way rather than with many tables that have been normalized and maintain referentially integrity through constraints.
Is 'old fashioned rows' a bad thing? Why is that?
-
//Returns the difference between physical and system stock. This is a read-only property
public int Difference { get { return _physicalStock - _physicalStock;}}
should be:
public int Difference { get { return _physicalStock - _systemStock;}}
-
Chris,
Although VARBINARY(MAX) might be simpler in some ways, there are some arugments for UDTs. For instance, how would the DBMS constrain on the VARBINARY(MAX) to make sure that every instance really was a 'bill'? What's to stop me from inserting serialized binary for a Word document? One benefit of UDTs is that they very effectively enforce typing in ways that LOBs cannot.
--
Adam Machanic
whoisactive -
FYI, your article has a bug:
//Returns the difference between physical and system stock. This is a read-only property
public int Difference { get { return _physicalStock - _physicalStock;}}
That would always return 0.
Someone asked earlier why set it up as Serializable? This would be the method of communication between the software and the SQL Server. When saving to the table the object would be serialized and written as a binary field in SQL. When you are reading this data, each record would be instanciated as an object.
As far as the DBA's complaining about this de-normalizing the DB. Actually, I think the rule you are referring to is to use one field for one purpose. This is storing one field for one purpose, in fact it's a much more specialized purpose than defining a binary field in the table. I believe that complaint is much more valid with the implementation of the XML field.
I'm sure the OO designers will like this capability. The whole point of creating an object is to encapsilate the data and protect it from unwanted changes. The addition of specialized properties inside of the SQL syntax might be handy.
I'm kind of interested in the performance question as well. In order to use the field, every access to it means converting it into an object, and executing the properties. If you are accessing several properties in a select statement, aren't you creating an instance for each one listed?
Also, what happens to the old objects in SQL when you need to add a new field to the class? This is a basic problem with storing objects in their native serialized form. One VERY good reason to save the fields in separate locations and make sure the user has access to the data only through the object.
-
And that is where I would use 'old fashioned rows' of data.
-
This is storing one field for one purpose, in fact it's a much more specialized purpose than defining a binary field in the table.
And how do these fit in to that one purpose?:
public int PhysicalStockpublic int SystemStock
public int Difference
-
Old fashioned rows are great. I use them all the time and probably will for a few more years. I haven't used UDTs yet, except maybe as constrained versions of basic database types.
The _contrived_ example that I gave had as its goal to _freeze_ the data at a point in time and give ready access to an application. It wasn't a full design meant to meet your needs but it seemed to address the question that was asked.
OO databases haven't been terribly popular and won't be, but I would argue that this cojoining of the elegant raw power of RDBMS with the OODBMS's ability to hide complex data structures is incredibly empowering.
Modern applications promise powerful cababilities using complex datatypes including graphics and complex physical models that won't fit the relational model very well, with the exception of a certain amount of metadata that should and will be searchable, aggregatable, sortable, relatable, etc. However, the highly complex, highly valuable "new fashioned" data must be handled differently. Get ready for the revolution.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply