SQLServerCentral / Discuss Content Posted by Dinesh Priyankara / Article Discussions / Article Discussions by Author / User Defined Data Types in SQL Server 2005 / Latest Posts

RE: User Defined Data Types in SQL Server 2005

Dinesh Priyankara — Fri, 25 May 2007 19:49:00 GMT

Points are taken. I agree with Peter too. The whole purpose of this article was giving an introduction of creating UDT with CLR components and I tried my best to explain it. But the sample I had taken is controversial that because I have considered only the requirements I had at that time. I tested this implementation with a sample database and had no performance problems though it has violated some general rules. I may be testing the implementation with VLDBs to check the performance. It may be worthwhile implementing calculated column for maintaining difference between stocks rather than maintaining an UDT. Let me do the load testing and see. Apart from this, hope I have shown the way of creating UDTs understandable.I appreciate all of your comments on this.

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Fri, 25 May 2007 12:10:00 GMT

Agreed with Peter. The top uses I see for UDTs are:

A) Extension of the type system with new types and associated operators (not too many of these)

B) Binding of domain validation rules to types (similar to rules in previous versions of SQL Server, but bound at the type level rather than the column level)

Creating complex types does not work well due to the inefficiencies they bring to the table... (pun slightly intended? )

RE: User Defined Data Types in SQL Server 2005

Peter DeBetta — Fri, 25 May 2007 11:16:00 GMT

I want to further validate comments made by a number of people. For demo purposes, this works fine, but it is a bad example of a real-world UDT. This point needs to be made very clear in the article, and it is not. It is even implied that this is a good real-world use, as shown here:

"The physical stock and system stock are stored separately in two different columns. Why do we not have a single column for this purpose? This thought came up as soon as I saw my own design."

This is not a reason for creating a UDT. The purpose of UDTs is to extend the type system, not combine existing types into a more complex type. It was also stated that:

"...this little scenario is going to be used for our first UDT, though a little simple."

Making a more complex type to represent 2 perfectly good types already in the relational design does not simplify, but rather, complicates the database design. Issues arise, such as querying against system stock. Based on the implementation, this would not be doable with any efficiency since the native serialization would serialize the physical stock first. The only way to efficiently query against the system stock value is to now create a computed column of the system stock and then index that computed column, which defeats the stated purpose of creating this UDT in the first place.

Don't get me wrong, I think CLR integration is a great feature, but must be used with prudence.

RE: User Defined Data Types in SQL Server 2005

Timothy-313907 — Fri, 25 May 2007 10:42:00 GMT

Interesting discussions so far!

I guess I was swayed by the opinions of others in this thread over the usefulness of UDTs but now I can see why you'd want them. Basically a UDT is no different than say a DateTime type, for example. Internally this is stored as a number but because it's a specialized type it has its own operations, like DateDiff, and operators can act differently when there are DateTime operands involved. With a DateTime type if you add two together using the + operator you get a new DateTime. For instance if you execute this

select cast('1/1/2007' as datetime) + cast('2/1/2007' as datetime)

you get the DateTime 2/1/2114, but if you execute this

select '1/1/2007' + '2/1/2007'

you get the string '1/1/20072/1/2007'. Basically the + operator is overloaded in OO lingo.

So for a UDT you could have the AddressLine type mentioned earlier in the thread and it would have its own operations, like DistanceFrom, and operators can behave differently with it. Perhaps the + operator would be invalidated so if you tried to add two together you'd get an error. The value of a UDT is determined by the person inventing / using it. The built-in types of SQL Server are just types generally thought to be useful, that's not to say an application can't find a value in using another type. For instance, treating a string not as a string but as a date.

RE: User Defined Data Types in SQL Server 2005

CraigIW — Fri, 25 May 2007 02:34:00 GMT

Or you could just store it as a block of XML?

Nope, doesn't cut it for me yet. Nice idea, but I still don't see a point in it, and have big concerns over the performance impact.

Craig

RE: User Defined Data Types in SQL Server 2005

Ninja's_RGR'us — Thu, 04 Aug 2005 06:28:00 GMT

Poor bastard is the one who has to make the difference between the col with uddt and the 3/4 part naming of objects .

RE: User Defined Data Types in SQL Server 2005

Kevin O'Donovan — Thu, 04 Aug 2005 05:21:00 GMT

> How do you know that the DistanceFrom() operator > can't communicate with a map server?Now that would be fun, the first time the user doesSELECT * FROM AllHousesInUSA WHERE Address.DistanceFrom>120Performance might be a bit, suboptimal...

RE: User Defined Data Types in SQL Server 2005

Mike C — Wed, 03 Aug 2005 14:08:00 GMT

If this is the case, what exactly is the advantage of creating a UDT in SQL Server and allowing access to it as opposed to just creating a Class in a traditional middle-tier app which coordinates access between the front end GUI and the back-end SQL Server?

RE: User Defined Data Types in SQL Server 2005

Mike C — Wed, 03 Aug 2005 14:01:00 GMT

Who says there's nothing on the type but those properties?

We did. Look at previous posts; these are the only properties and methods defined so far.How do you know there aren't private members that hold the geocode?

Because they haven't been defined as of yet. Again, see previous posts.How do you know that the DistanceFrom() operator can't communicate with a map server?

As long as we're going the "how do you know ... can't ...", how do we know that the DistanceFrom operator can't make a darn good cup of coffee, toast a bagel or wash the dishes? We know because it hasn't been defined.The key here is encapsulation. You don't know -- and as an end user, you shouldn't have to know -- how it works. It just works. As a DBA or developer, you know the details. But you don't need to expose them to the world.

The key here is definition. Whether public, private, friend or whatever you like; if it ain't defined, it don't exist. Now you're talking about geocoding functionality. Great! Just like I said previously (in more than one post here), let's 1) Define the encoding interfaces and 2) Define the standard Units of Measure. Then we can 3) Generate *useful* results.

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Wed, 03 Aug 2005 13:42:00 GMT

Peter:Structs can have methods, but no code access attributes (everything is public).UDTs can be defined as either structs or classes. If you define one as a class, you must inherit IBinarySerialize.Shameless plug: See my upcoming book for more info on the topic

RE: User Defined Data Types in SQL Server 2005

Peter Kryszak — Wed, 03 Aug 2005 13:39:00 GMT

These UDTs won't be terribly useful if they exist just in the database. They are a liability in that case. The real value is in the fact that they can be used in the database and in applications. The same stuct source code is used in both places.There are certainly limits to their usefulness if the the UDTs cannot have methods associated with them since they seem to be defined as "struct" (IIRC from the article). Applications would probably add wrapper classes with methods that provide useful functions, but at least the basic data definition is in one place.Does someone know for sure that these have to be defined as "struct" and can structs have private members and/or methods (public or private)?

RE: User Defined Data Types in SQL Server 2005

Kenneth Lee — Wed, 03 Aug 2005 13:27:00 GMT

As you pointed out, I don't see any advantage to a DistanceFrom "operator" over creating it as a UDF.

The advantage for the UDT is that it is a CLR, which means it's accessible in the middle tier. You don't have to connect to the SQL Server to get access to the business rules tied into the UDT. This is right up the alley of the DBA who believes that business rules should never be written in stored procedures. This is extremely threatening to the DBA who believes that business rules should never be written anywhere else. This might be a bridge solution where the developer can design what he/she wants stored and the DBA can keep the schema away from the ignorant developer while still being able to see what is being stored on "his" DB.

Never underestimate the capacity for idiotic behavior in any human. I've been on the programmer side, where I know what data I want stored, but have to pass the request on to a DBA who can't do what I want done unless I correct his "solution" 15 times. I've been on the DBA side where I've seen the devastation a programmer can do to a DB because they don't understand what they are doing and are allowed to modify the schema.

I can see it now, the DBA doesn't allow direct access to the table and will only allow the developer to send/receive the properties of the UDT in sprocs. And of course the DBA screws up what properties are allowed in the UDT.

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Wed, 03 Aug 2005 12:50:00 GMT

Who says there's nothing on the type but those properties?How do you know there aren't private members that hold the geocode?How do you know that the DistanceFrom() operator can't communicate with a map server?The key here is encapsulation. You don't know -- and as an end user, you shouldn't have to know -- how it works. It just works. As a DBA or developer, you know the details. But you don't need to expose them to the world.

RE: User Defined Data Types in SQL Server 2005

Mike C — Wed, 03 Aug 2005 12:42:00 GMT

So, how exactly do we implement a DistanceFrom() operator without encoding our Addresses using a standard UOM? (like lat/lon for instance)? There is nothing intrinsic to this particular type, as presented, that tells us we can perform any useful operations of any type on it, without first converting to a standard UOM (see previous post). This type so far consists of:

StreetAddressCityStateZIP Code

And of course the DistanceFrom() operator that uses the information above to calculate the distance between addresses.

Just like with the InventoryItems, etc., what type of useful calculations can you perform with the above without first converting it to a standard UOM and performing the calculations on that encoded data?

I think I already mentioned the need for these UDT's to have some sort of encoding ability as well as some type of conversion ability (either implicit or explicit) to standard UOMs in order to get any type of useful result.

The distance from '123 Sesame St' to '1600 Pennsylvania Ave' is 17.

*Levenshtein Edit Distance

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Wed, 03 Aug 2005 10:24:00 GMT

Are + or - scalar operators _on the Address type_? Or are they scalar operators on OTHER types?What is or is not a scalar operator depends on the type. DistanceFrom is a scalar operator on the Address type -- but is not an operator for the integer type.Remember, scalar != primitive.

RE: User Defined Data Types in SQL Server 2005

Mike C — Wed, 03 Aug 2005 10:03:00 GMT

What's infeasible is performing scalar operations on it:

Address1 = "12 West Broadway, New York, NY 10036"Address2 = "941 Market Street, San Francisco, CA 94109"

Address3 = Address1 + Address2

Address4 = Address1 - Address2

IF Address1 < Address2 THEN ...

As you pointed out, I don't see any advantage to a DistanceFrom "operator" over creating it as a UDF.

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Wed, 03 Aug 2005 09:50:00 GMT

What's infeasible about having a class called Address in an OO language? I don't see any problem with that. You would have properties like StreetNumber, StreetPreDirectional, StreetPostDirectional, StreetName, City, State, PostalCode, etc.There aren't too many operators, but I can think of at least one:DistanceFrom(Address other)

RE: User Defined Data Types in SQL Server 2005

Mike C — Wed, 03 Aug 2005 09:41:00 GMT

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.

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Wed, 03 Aug 2005 08:48:00 GMT

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.

RE: User Defined Data Types in SQL Server 2005

Alex-217289 — Wed, 03 Aug 2005 07:58:00 GMT

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.

RE: User Defined Data Types in SQL Server 2005

Mike C — Wed, 03 Aug 2005 07:55:00 GMT

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.

RE: User Defined Data Types in SQL Server 2005

N Cook — Wed, 03 Aug 2005 07:54:00 GMT

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

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Tue, 02 Aug 2005 18:54:00 GMT

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.

RE: User Defined Data Types in SQL Server 2005

DavidMcAfee — Tue, 02 Aug 2005 18:22:00 GMT

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

RE: User Defined Data Types in SQL Server 2005

Kenneth Lee — Tue, 02 Aug 2005 17:00:00 GMT

And how do these fit in to that one purpose?: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.

RE: User Defined Data Types in SQL Server 2005

Peter Kryszak — Tue, 02 Aug 2005 16:30:00 GMT

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.

RE: User Defined Data Types in SQL Server 2005

Kenneth Lee — Tue, 02 Aug 2005 16:25:00 GMT

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?

RE: User Defined Data Types in SQL Server 2005

Peter Kryszak — Tue, 02 Aug 2005 15:52:00 GMT

Old fashioned rows are great. I use them all the time and probably will for a few more years. I haven't used UDTs yet, except maybe as constrained versions of basic database types. The _contrived_ example that I gave had as its goal to _freeze_ the data at a point in time and give ready access to an application. It wasn't a full design meant to meet your needs but it seemed to address the question that was asked.OO databases haven't been terribly popular and won't be, but I would argue that this cojoining of the elegant raw power of RDBMS with the OODBMS's ability to hide complex data structures is incredibly empowering.Modern applications promise powerful cababilities using complex datatypes including graphics and complex physical models that won't fit the relational model very well, with the exception of a certain amount of metadata that should and will be searchable, aggregatable, sortable, relatable, etc. However, the highly complex, highly valuable "new fashioned" data must be handled differently. Get ready for the revolution.

RE: User Defined Data Types in SQL Server 2005

Chris Hedgate — Tue, 02 Aug 2005 15:45:00 GMT

This is storing one field for one purpose, in fact it's a much more specialized purpose than defining a binary field in the table.And how do these fit in to that one purpose?:public int PhysicalStockpublic int SystemStockpublic int Difference

RE: User Defined Data Types in SQL Server 2005

Chris Hedgate — Tue, 02 Aug 2005 15:41:00 GMT

And that is where I would use 'old fashioned rows' of data.

RE: User Defined Data Types in SQL Server 2005

Kenneth Lee — Tue, 02 Aug 2005 15:38:00 GMT

FYI, your article has a bug:

//Returns the difference between physical and system stock. This is a read-only propertypublic int Difference { get { return _physicalStock - _physicalStock;}}

That would always return 0.

Someone asked earlier why set it up as Serializable? This would be the method of communication between the software and the SQL Server. When saving to the table the object would be serialized and written as a binary field in SQL. When you are reading this data, each record would be instanciated as an object.

As far as the DBA's complaining about this de-normalizing the DB. Actually, I think the rule you are referring to is to use one field for one purpose. This is storing one field for one purpose, in fact it's a much more specialized purpose than defining a binary field in the table. I believe that complaint is much more valid with the implementation of the XML field.

I'm sure the OO designers will like this capability. The whole point of creating an object is to encapsilate the data and protect it from unwanted changes. The addition of specialized properties inside of the SQL syntax might be handy.

I'm kind of interested in the performance question as well. In order to use the field, every access to it means converting it into an object, and executing the properties. If you are accessing several properties in a select statement, aren't you creating an instance for each one listed?

Also, what happens to the old objects in SQL when you need to add a new field to the class? This is a basic problem with storing objects in their native serialized form. One VERY good reason to save the fields in separate locations and make sure the user has access to the data only through the object.

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Tue, 02 Aug 2005 15:25:00 GMT

Chris,Although VARBINARY(MAX) might be simpler in some ways, there are some arugments for UDTs. For instance, how would the DBMS constrain on the VARBINARY(MAX) to make sure that every instance really was a 'bill'? What's to stop me from inserting serialized binary for a Word document? One benefit of UDTs is that they very effectively enforce typing in ways that LOBs cannot.

RE: User Defined Data Types in SQL Server 2005

stojce — Tue, 02 Aug 2005 15:22:00 GMT

//Returns the difference between physical and system stock. This is a read-only property public int Difference { get { return _physicalStock - _physicalStock;}}should be:public int Difference { get { return _physicalStock - _systemStock;}}

RE: User Defined Data Types in SQL Server 2005

Chris Hedgate — Tue, 02 Aug 2005 15:18:00 GMT

Why not simply store it in a VARBINARY(MAX))?The job of a DBMS is to preserve the integrity of the database. The only way it can do that is to 'know' everything it needs to know about the data, which it does by enforcing constraints. If this bill is to be treated only as a blob of data, and the only integrity that needs to be maintained is that a bill has an owner (for instance), then storing it in a VARBINARY(MAX) is enough. If the database needs to know more, for instance the date when the bill was created, then it needs access to that information. To access that information in a UDT in this way requires us to implement our own methods, instead of designing the data so that the relational DBMS can use relational operations as it should.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.Is 'old fashioned rows' a bad thing? Why is that?

RE: User Defined Data Types in SQL Server 2005

Chris Hedgate — Tue, 02 Aug 2005 15:12:00 GMT

The problem with this approach is that we would be using a relational DBMS (not truly relational, but still) for storing the data, but for some reason not using the existing relational operations to manipulate it. Instead of using functionality that is based on proven theories more than 100 years old we would instead need to implement our own operations, which offer no benefits over the former said.

RE: User Defined Data Types in SQL Server 2005

Adam Machanic — Tue, 02 Aug 2005 13:05:00 GMT

Mike,I think that conversion and encryption would be great for UDFs -- but I don't really see the point of having an 'encrypted' type, personally. What benefit would that have over simply storing the data in a VARBINARY(MAX) column?

RE: User Defined Data Types in SQL Server 2005

Mike C — Tue, 02 Aug 2005 12:50:00 GMT

The problem is that we're de-normalizing the data using UDT's. It also seems that all the kinks aren't worked out on the MS end yet. It seems to me that this might come in handy in certain situations (depending on the final implementation), but for the most part it seems like more of an attempt to apply OO design principles to SQL.

Just as an example, if you wanted to store length values and their associated units of measure as a UDT. The only way I could see this being even remotely useful would be if all computations on the length values were converted to a common unit of measure by the UDT itself during the computation. For instance, if I wanted to perform a computation like "12 feet" + "5 inches" + "3 meters" + "98 centimeters", the UDT itself would need to convert the values to a common UOM (let's say centimeters).

What unit of measure the result should be returned in? Should our result be in feet, inches, meters?

Additionally, with UDT's we also need them to sort correctly on our display. i.e., Does "98 centimeters" come before or after "5 inches" when we perform a query and sort by the length value?

The standard way of dealing with a situation like this is to convert each value to a common unit of measure before we store it in the database (we could convert all length measurements to meters prior to storage, for example).

Overall you wrote a good article, but there are just a bunch of questions concerning the new UDT implementation that aren't likely to be resolved until MS releases the final product.

RE: User Defined Data Types in SQL Server 2005

DavidMcAfee — Tue, 02 Aug 2005 12:31:00 GMT

I deal with a system called Ramco. Ramco uses SQL Server 2000 as its BE. The designers of this system, really need to be given a lesson in normalization and to not use UDTs as they do. Just about every field is a UDT.Fields such as Address are not Varchar(40), but rather CMN_DESCRIPTION (which when looked up is a Varchar(40). This one database alone has 962 UDTs!!!Merging to numeric values (assuming stock numbers are numeric) into one concatenated text field, is IMO not a good thing to do. This reminds me of Access developers that use lookup fields, rather than storing an int. too confusing later on.Another reason I dont like UDTs is that you cannot use them in table variables. I don't remember if you canuse them in temp tables, but you can't in table variables (at least not in SQL Server 2000)David

RE: User Defined Data Types in SQL Server 2005

Ninja's_RGR'us — Tue, 02 Aug 2005 12:14:00 GMT

I think this is the main problem at the moment. We already have many correct ways of making the model work, so why would we have to switch to this one??

RE: User Defined Data Types in SQL Server 2005

sara karasik — Tue, 02 Aug 2005 12:13:00 GMT

If you must keep the data as one column, you can always create a calculated column on the table.