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.SystemStockUPDATE 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*.9WHERE InStock.PhysicalStock < InStock.SystemStockbut 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?
And how do these fit in to that one purpose?:public int PhysicalStockpublic int SystemStockpublic int Difference
public int PhysicalStockpublic int SystemStockpublic 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.
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.
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.
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.