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
Dinesh Priyankara
Dinesh Priyankara
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 115
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/userdefineddatatypesinsqlserver2005.asp
Jamie Thomson
Jamie Thomson
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: 1085 Visits: 188

You've talked alot about Serializable e.g. Declaring the UDT as Serializable allows the UDT to be Serialized. Well ur, yeah, obviously! But what does that mean? What can you do with a serialized UDT that you can't do with a non-serialized UDT? This needs expanding on!

Other than that, not a bad article. A nice introduction.

-Jamie



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Kevin O'Donovan
Kevin O'Donovan
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 84
Anyone looked at the performance implications of this? For example, if we had a few tens of thousands of records in that stock table, and needed the total of both stock level columns across all stock items (meaningless, I know, but for sake of example), how would it compare to summing the native columns?



Chris Hedgate
Chris Hedgate
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1861 Visits: 7
This type of example is exactly the reason why I and many more or 'scared' (or at least concerned) about the CLR Integration in SQL Server 2005. I see absolutely no reason whatsoever to create UDTs that combine two scalar values (in fact I have yet to see any example of a good use for CLR UDTs). This gives no benefits at all, but a lot of problems will come from it. The complexity is much higher, for no reason. The portability is completely lost. Displaying the type will be difficult in many tools (probably will just show a binary). And it is a violation of 1NF, so one might wonder why a relational DBMS is used anyway?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Peter Kryszak
Peter Kryszak
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1310 Visits: 3
The choice of example seems reasonable for demonstration, but does not seem at all reasonable for a real-world scenario.

The author asks the rhetorical question, "Why do we not have a single column for this purpose?" I think there are a lot of reasons, including: normalization (and all of its implications), the meaning of null if one value is known and the other is not, performance in evaluating column expressions and where-clauses, difficulting in using these types in ad hoc query or reporting tools, and so on.

These types, especially as defined through the CLR, should be used sparingly and only in situations where the values will be manipulated in an application where such structures are handled easily. SQL just isn't optimal for complex types and I have seen very very very few instances of user defined types in real applications in SQL Server, or even Oracle for that matter. Where they have been used, UDTs were not used in identifying rows or as basic attributes but represented whole objects and were processed in high level languages, not SQL, one at a time.

Have others seen widespread used of UDTs? Oracle's had this capability for a long time, and it is still rare to see UDTs in applications so I doubt that they'll catch on too much in SQL Server, except that Visual Studio will make it too easy for developers to add these.



Jamie Thomson
Jamie Thomson
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: 1085 Visits: 188

Peter,

That last point is a good one. IMO developers should never be let loose near a relational database and that is truer with the advent of SQL2005 than ever before.

-Jamie



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
N Cook
N Cook
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 22

Can anyone think of any good example for UDTs to be used? Peter, what exactly might be an example of the "whole object" that would fit well in a UDT?

Just trying to learn.
Thanks,
Nate


Kevin O'Donovan
Kevin O'Donovan
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 84
> Displaying the type will be difficult in many tools
> (probably will just show a binary)

I've not had a chance to install sql2005 yet - do the tools provide any support for working with these types of columns, or can you only access them through code?


> only in situations where the values will be
> manipulated in an application where such structures
> are handled easily

I think that's the key. I've been trying to think of anywhere we might like to use them, and the only one I've come up with in one of our apps so far is international phone numbers. We let the user select an international prefix from a drop down list of countries, and then have seperate entry boxes for area code and number. These are then stored as three seperate fields. From the SQL point of view there's little advantage to this (I suppose it would allow us to easilly update should an international dialling code change), so I might be tempted to use a UDT for such a field. Whether it would be worth any ensuing problems though is another matter. It doesn't appear to offer me any real benefits though - all our updates are done via stored procedures, so I'd presumably still have to pass three parameters into the proc. Hmmm



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

Group: General Forum Members
Points: 1310 Visits: 3
Here's an example...

Let's say you are writing an application that presents a bill. That bill should not change over time and it's attributes won't be queried on for aggregation or any other ad hoc reporting scenario, because those queries will go to source data. However, you'll want to store a snap shot of that bill to be able to reproduce it later when the account holder requests it on the web site.

A table that stores those snap shots might have an primary key identity column and maybe an owner column to allow getting that one bill but the bill itself may be stored as a UDT in a single column to simplify access and leave the data structure interpretation to the application. It could contain all the info that normally is in the bill, perhaps an image, and may even include an electronic signature, etc. to ensure that the bill's values haven't been changed.

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.



N Cook
N Cook
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 22

Peter, that is an excellent example. I think I understand the concept now.

Thanks!
Nate


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