Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User Defined Data Types in SQL Server 2005


User Defined Data Types in SQL Server 2005

Author
Message
Kenneth Lee
Kenneth Lee
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
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?


Peter Kryszak
Peter Kryszak
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1244 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.



Kenneth Lee
Kenneth Lee
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
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.


DavidMcAfee
DavidMcAfee
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 19
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



Adam Machanic
Adam Machanic
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 714
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
N Cook
N Cook
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

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.


Alex-217289
Alex-217289
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
Adam Machanic
Adam Machanic
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 714
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
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search