Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

User Defined Data Types in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, August 2, 2005 4:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1

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?

Post #206655
Posted Tuesday, August 2, 2005 4:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:51 PM
Points: 1,244, Visits: 3
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.


Post #206656
Posted Tuesday, August 2, 2005 5:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1

And how do these fit in to that one purpose?:

public int PhysicalStock
public 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.

Post #206660
Posted Tuesday, August 2, 2005 6:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:46 PM
Points: 34, Visits: 17
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



Post #206674
Posted Tuesday, August 2, 2005 6:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #206681
Posted Wednesday, August 3, 2005 7:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 5:54 PM
Points: 99, Visits: 22

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

Post #206872
Posted Wednesday, August 3, 2005 7:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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.

Post #206873
Posted Wednesday, August 3, 2005 7:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 9, 2009 8:13 AM
Points: 75, Visits: 3
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.


Cheers,

Alex


Rogue DBA
Post #206874
Posted Wednesday, August 3, 2005 8:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701

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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #206902
Posted Wednesday, August 3, 2005 9:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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.

Post #206922
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse