Cons w/ UDTs?

I came across a database at my company that contained many columns in many
tables defined as UDTs. Upon further investigation, there were no rules
associated with the UDTs and they were not explicitly bound.
Upon even further investigation, the person who created the database said
they were using a modeling tool to test the quality/validity of the data
model and the tool complained that no data domains were present and suggested
the utilization of UDTs.
After reading more about them, i was intrigued and can definitely see their
value. My question is are there any cons to using them? For example, can
over usage cause performance issues? What issues are encountered if a rule
or data type needs to be altered? Etc... Thanks in advance.parchk,
Are you using SQL Server 2000 or 2005? Things are changing.
User-Defined Types in 2000 and 2005 can simply be an alias for describing
data domains, but for both versions of SQL Server, the gotcha is: "When you
create a user-defined type, it is local to a single database." If you don't
care about cross-database use of the UDT, not even in tempdb, then no
problem. (But even then, 2005 offers some options that 2000 does not.)
(SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
(SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
Then you can use them in your code in the proper database as if they were a
native datatype.
I would guess that this is the type of UDT you have in your database. I
used to use them heavily, but decided that the value of using UDTs as
documentation of data domains was to small to repay the hassle. (And, when
you generate scripts you tend to get the base datatypes scripted out for
you.)
--
With 2005, there is now "CLR User-Defined Types". This is a much richer and
more complex type of UDT, where you define the type and the operations that
work with the datatype. If you need this, you need to read about them in
detail, starting in the SQL Server 2005 Books Online.
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>I came across a database at my company that contained many columns in many
> tables defined as UDTs. Upon further investigation, there were no rules
> associated with the UDTs and they were not explicitly bound.
> Upon even further investigation, the person who created the database said
> they were using a modeling tool to test the quality/validity of the data
> model and the tool complained that no data domains were present and
> suggested
> the utilization of UDTs.
> After reading more about them, i was intrigued and can definitely see
> their
> value. My question is are there any cons to using them? For example, can
> over usage cause performance issues? What issues are encountered if a
> rule
> or data type needs to be altered? Etc... Thanks in advance.|||If you can imagine a db system that is 'strongly' typed, where
you can define types natively instead of going 'outside' the system
and where any type can be the type of a 'variable' not just
a value then I have a surprise for you :-)
www.beyondsql.blogspot.com
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMMF6yO6HHA.2380@.TK2MSFTNGP02.phx.gbl...
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When
> you create a user-defined type, it is local to a single database." If you
> don't care about cross-database use of the UDT, not even in tempdb, then
> no problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were
> a native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And,
> when you generate scripts you tend to get the base datatypes scripted out
> for you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer
> and more complex type of UDT, where you define the type and the operations
> that work with the datatype. If you need this, you need to read about
> them in detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>>I came across a database at my company that contained many columns in many
>> tables defined as UDTs. Upon further investigation, there were no rules
>> associated with the UDTs and they were not explicitly bound.
>> Upon even further investigation, the person who created the database said
>> they were using a modeling tool to test the quality/validity of the data
>> model and the tool complained that no data domains were present and
>> suggested
>> the utilization of UDTs.
>> After reading more about them, i was intrigued and can definitely see
>> their
>> value. My question is are there any cons to using them? For example,
>> can
>> over usage cause performance issues? What issues are encountered if a
>> rule
>> or data type needs to be altered? Etc... Thanks in advance.
>|||Hi Russell,
We are using 2005. Thanks for your response. For databases that make heavy
use of UDTs to define data domains, is there any performance impact? The
volume will not be high; however, the database is involved with replication.
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||So overall would you recommend extensive utilization of UDTs for defining
data domains and/or the other capabilites of UDTs?
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||Parchk,
The 'alias' form of UDTs should have absolutely no performance overhead. It
is really just a naming / documentation scheme.
In terms of replication (I am not a replication expert) I would say that you
want to make sure that the UDTs are also replicated or created with the same
id on the replicated database. And even that is probably not completely
necessary, since the underlying datatypes are knows on both servers.
(Perhaps someone who does replication a lot, can add a comment.)
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:4512EE17-B439-46FE-9504-86F7BD01A882@.microsoft.com...
> Hi Russell,
> We are using 2005. Thanks for your response. For databases that make
> heavy
> use of UDTs to define data domains, is there any performance impact? The
> volume will not be high; however, the database is involved with
> replication.
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>|||Parchk,
It is handy for defining things that you want to be the same everywhere.
For example:
CREATE TYPE SSN FROM varchar(11) NOT NULL
If you use this everywhere a Social Security Number is needed, then the
columns and variables will always be 11-characters, which leaves room for
the dashes, eg. 123-45-6789. This helps avoid having some places coded for
varchar(9), which is intended for 123456789 without dashes, but might wind
up holding 123-45-67 which, of course, is invalid.
The thing that moved me away from using them was the cross-database issues.
These are describe in the 2005 Books Online topic: "Using User-defined Types
Across Databases". It seems, without testing, like it is a little more
robust than in earlier versions, but there are still things to be dealt
with.
I switched over to using class names for my columns, which has served me
well in the years since then. e.g.
Policy_Holder_SocialSecurityNumber
Mate_SocialSecurityNumber
Business_PhoneNumber
Person_FamilyName
In these examples the final token of the column (or variable) name is the
data class. This does not, of course, enforce that everyone defines the
same datatype for the class names. Documentation and review are needed to
catch errors.
FWIW,
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:6B7E0FAD-0EA4-46C7-98AE-825692FC1543@.microsoft.com...
> So overall would you recommend extensive utilization of UDTs for defining
> data domains and/or the other capabilites of UDTs?
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>