User Defined Data Types in SQL Server 2005
-
I take it, these two commands should work:
UPDATE Items
SET InStock = CAST( InStock.PhysicalStock AS VARCHAR) + '|' + CAST( InStock.SystemStock*.9 AS VARCHAR)
WHERE InStock.PhysicalStock < InStock.SystemStock
UPDATE Items
SET InStock = CAST( InStock.SystemStock*.9 AS VARCHAR) + '|' + CAST( InStock.PhysicalStock AS VARCHAR)
WHERE InStock.PhysicalStock < InStock.SystemStock
No stopping stupid mistakes. (At least, without transactions.) Clunky way of getting what you want. No intellesense in SQL. SQL is generally case insensitive, would InStock.systemstock work? INSTOCK.SystemStock? What kind of error for InStock.SystemStoc? If you aliased a table as InStock and joined it with this table, would it work? If so, would you have to prefix the InStock struct with it's table name?
This command should fail?:
UPDATE Items
SET InStock.SystemStock = InStock.SystemStock*.9
WHERE InStock.PhysicalStock < InStock.SystemStock
but could work if the set property was defined?
UDTs are always value types, not reference types?
When reading into a DataTable, if you include the namespace for the Struct, will it automatically include that type in the data adapter? Even with earlier versions of .NET?
-
I expect you'll see IntelliSense in SQL development tools soon. Though Microsoft does have a way of delivering truly useful development technology only after tantalizing developers with little bits of power for months in early releases.
-
And how do these fit in to that one purpose?:
public int PhysicalStockpublic int SystemStock
public int Difference
The one purpose is the storage of stock information. Those are attributes of the dataType.
Look at systypes, there are lots of attributes for the SQL defined fields.OK, not a good example, the results of systypes are static for an individual type and the results of those properties are not static in the resultset. The existence of the properties are like an extended set of attribute fields for the data type.
Also, in my prior post, I showed how you can in fact treat the two fields in the struct as independent values. Which I agree isn't normalized.
Neither is an image, because I can upload the image, change a pixel's color/brightness, and re-save the image back into SQL. I am treating that pixel as an independent value with 4 byte attributes associated with it and storing that pixel with thousands of others that make up a picture. It's all in your point of view of what is normalized data.
Also, you can normalize a DB to it's knees. De-normalization to some degree is expected.
-
Coming from a VBA background, I didn't like QA at first due to lack of intellisense. Now I can't stand working in VBA. you intentionally leave a line incorrect to move a line up to copy and paste a variable and you get the message box giving you the error.
I really can't stand what they've done with VS2003. start to type a command and if formats the lines below you. I like to do that stuff manually. Maybe Im just getting old and grumpy
David
-
I agree, some of the auto-formatting is quite annoying (although you can turn it off if you want) -- it formats some code very differently than the way I want it done. But the intellisense in VS2003 is a lot better than in VB6, where it would give you the message boxes. That does not happen in VC#.
Intellisense in VS2005, unfortunately, is at once better and more invasive. I encountered some situations in which it seemed to override what I wanted, based on -- who knows what... Hoping that was a bug.
--
Adam Machanic
whoisactive -
In case anyone didn't know you can turn off annoying message boxes in VB6 by going to Tools->Options and uncheck 'Auto Syntax Check'. It still checks the syntax but instead of giving you a msg box it just turns the font color of that line to red. MUCH better IMO.
Nate
-
I was thinking more along the lines of just declaring a variable, assigning the value and it automatically encrypts or encodes to the proper base. You're right, there's probably not much benefit to that over a UDF, except to say "look what I did." But then again, I'm not seeing much value in UDT's overall so far, except to say "look what I did."
My main point is that UDT's will probably work best for simple scalar types as opposed to aggregated, de-normalized types that consist of 2 or more separate pieces of information. I think the SQL 2000 "Money" datatype implementation is a prime example of the things that can go wrong when you try to create such a data type.
I also don't see any benefit to declaring an ItemStock or other Inventory UDT. What's the "total" of 3 hammers, 2 nails and 1 piece of wood in my inventory? Is it less than an inventory of 3 french hens, 2 turtledoves and a partridge in a pear tree?
Performing scalar (or even aggregate) functions on a UDT like this doesn't seem to make a lot of sense... Once again, before inventories are summed accountants usually prefer they be converted to a common UOM - usually dollars representing an assigned value of each item; although it could also be weight, size, or any other common measure.
-
In my opinion the use of UDTs carry little advantage. If you refer to Peter's example all the way back on page 1 about using a UDT to hold an entire "object" like a bill, it begs the question of why you are even working with a RDBMS in the first place. "Old Fashioned" row??? Regardless of how long the RDBMS has been around, it isn't being improved by this "fake" normalization. There is no better way to store information in a RDBMS than using a main table and one of several possible "lookup" table designs for the detail records. That's what this is all about.
-
I'm not sure what an "old fashioned" row is, or what "fake normalization" would look like. The fact is, we can't possibly know whether a given column called "bill" is in 1NF or not, without the semantics of the application the database is being used for. Normalization is determined not by the data itself, but by how the data is used.
As for the advantages of UDTs, I think they have a lot of potential.
A quick review: A table is nothing more than a collection of attribute/type/value triplets. Attribute, we refer to as column name. Value is the value, for any given row, of that attribute. And that leaves type...
But what is a type? When a lot of people think about types, they don't get beyond primitives. String types like varchar, or numeric types like integer. But that's not all there is to it.
A type is defined by both a domain of possible values, and operators that can act on instances of the type--and make sense for instances of the type. And that's where things get interesting.
Take, for example, a column called 'AddressLine1', defined as VARCHAR(50). The VARCHAR type happens to have a concatenation operator, +. But does this really make sense for an address line? What is the output of address line + address line? I'm not sure I know the answer...
I could write my own AddressLine type, and it probably would not have a concatenation operator. It might have various operators that make sense for address lines; for instance, it might have operators to add or modify various valid parts of an address line, such as a directional or street name. These operators make sense for the AddressLine type. Concatenation really does not.
Furthermore, I could encapsulate within my type the ability to enforce a domain of possible values. Is the value "abcdefg" a valid instance of AddressLine1? I could write a CHECK constraint to enforce values, but what happens next time I need an AddressLine? Do I write more constraints? What if I want a variable in which to store address lines? Do I embed the constraint logic all over my code?
It would be a lot easier to simply write the logic once, and reuse it wherever I need it. And that's really the main benefit that UDTs can provide.
--
Adam Machanic
whoisactive -
You're talking about scalar operations on simple/primitive UDT types. In the example you are talking about a datatype called AddressLine which is composed of a single unit of data (a VARCHAR, or whatever, representing a "street address"). And then you talk about performing scalar operations on that type.
What I'm taking from the rest of this thread is more or less the idea that people seem to think storing street address, city, state and zip code all in one big data type called "Address" and performing scalar operations of some sort on that type is feasible. I think a good rule of thumb might be that if it's not feasible in a 4GL OO language like C++ there's probably a good chance it isn't feasible with SQL UDTs.
I'm thinking that performing scalar operations on most of the UDTs people will come up with (like InventoryItems, etc.) probably won't work out too well.
I can see how storing a piece of data that you do not need to manipulate with scalar operations might be useful. As an example, storing a graphic image in your database as a UDT might come in handy. You could expose various properties like "ColorDepth", "ImageFormat" (JPEG, PNG, BMP, etc.), "Width", "Height", "Palette", "AlphaChannel", etc., which would all be nice information to have on the application side when you're ready to display the image. But performing scalar operations on such a type? Just doesn't make sense.
-
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
whoisactive -
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.
-
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
whoisactive -
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
-
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
whoisactive
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply