February 22, 2020 at 3:12 pm
Comments posted to this topic are about the item In Praise of User-Defined Datatypes
Best wishes,
Phil Factor
February 25, 2020 at 6:03 pm
for sure ! I've used UDTs for years - example: when having a db using typical autoincrement primary keys; that means any FK reference is of data type "int". SO that means its up to naming conventions to differentiate between an FK "int" and a data field "int" (e.g., a col named "CustomerId" , versus a col named "QuantityOrdered"). By creating a UDT called "FKID:int" I can now differenciate easily, I can evangelize (and enforce) the conventions so that all other devs can benefit. My ORM flgs these as UDTs (anyone out there still using Breeze?) so client-side development also benefits. All in all a very useful tool and heck with ANSI cobol-era constraints. Cheers, rick.
February 25, 2020 at 6:06 pm
I once thought they were a good idea too...
until I needed to revise the underlying type definitions for a set of user defined data types in multi terrabyte production databases with hundreds of tables + views + functions + stored procedures all of which were defined using the user defined data types.
The only way this can be achieved in practice is by creating a completely new database and copying everything from the old database into the new one - assuming that a convenient maintenance window is available, and that you have the necessary storage available.
If the system has to be available close to 24x7 then that is a lot of hastle when all you want to do is redefine varchar(30) to varchar(60) for 1000 columns; which can be achieved if normal data types were being used with alter table alter column in a minimal amount of down time.
We now still support the concept of a user defined type in metadata but resolve it to base data types in the database build or delta scripts.
February 27, 2020 at 1:59 pm
If they were supported, it would be so cool. But the cons currently outweigh the pros.
The optimizer doesn't consider Rules, as it does with the Constraints. Changing the data type is a nightmare. Can't use it with In-Memory OLTP and there are some other limitations I can't think of right now (maybe something with an indexed computed column?)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply