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 12345»»»

User Defined Data Types in SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, July 11, 2005 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/userdefineddatatypesinsqlserver2005.asp
Post #199531
Posted Tuesday, August 2, 2005 2:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, 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
Post #206368
Posted Tuesday, August 2, 2005 5:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:35 AM
Points: 31, Visits: 79
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?


Post #206424
Posted Tuesday, August 2, 2005 6:49 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, 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/
Post #206441
Posted Tuesday, August 2, 2005 6:54 AM


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
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.



Post #206442
Posted Tuesday, August 2, 2005 6:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, 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
Post #206444
Posted Tuesday, August 2, 2005 7:21 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

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

Post #206461
Posted Tuesday, August 2, 2005 7:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:35 AM
Points: 31, Visits: 79
> 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




Post #206463
Posted Tuesday, August 2, 2005 7:46 AM


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
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.




Post #206473
Posted Tuesday, August 2, 2005 7:53 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

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

Thanks!
Nate

Post #206477
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse