Create UDDT - good idea?

[Tom van Stiphout]

I'm designing a new database from scratch. It has some columns that I
currently have as datatype=int with check constraint of x>=1 and x
<=32, or others with a range of 1-3, etc. It occurred to me I could
create a UDDT to represent each. Benefit would be that I don't have to
enter the check constraints all the time.

Is there any consensus on whether or not this is a good idea? I am a
bit concerned my front-end application (.NET) will not be able to
recognize these types as easily as "int".

If you have any experience using a CLR-defined type in SQL Server I'd
love to hear from you too.

-Tom.

[Roy Harvey (SQL Server MVP)]

I avoid user defined data types. In theory they should save work, but
in practice they obscure things that would be clear if the basic data
type were used. And the support for them in SQL Server can be a bit
painful to deal with, particularly if one of them has to change.

Roy Harvey
Beacon Falls, CT

On Thu, 23 Oct 2008 19:17:22 -0700, Tom van Stiphout
<to*************@cox.netwrote:

>I'm designing a new database from scratch. It has some columns that I
currently have as datatype=int with check constraint of x>=1 and x
<=32, or others with a range of 1-3, etc. It occurred to me I could
create a UDDT to represent each. Benefit would be that I don't have to
enter the check constraints all the time.

Is there any consensus on whether or not this is a good idea? I am a
bit concerned my front-end application (.NET) will not be able to
recognize these types as easily as "int".

If you have any experience using a CLR-defined type in SQL Server I'd
love to hear from you too.

-Tom.

[Martijn Tonies]

Hello Tom,

I'm designing a new database from scratch. It has some columns that I
currently have as datatype=int with check constraint of x>=1 and x
<=32, or others with a range of 1-3, etc. It occurred to me I could
create a UDDT to represent each. Benefit would be that I don't have to
enter the check constraints all the time.

Is there any consensus on whether or not this is a good idea? I am a
bit concerned my front-end application (.NET) will not be able to
recognize these types as easily as "int".

If you have any experience using a CLR-defined type in SQL Server I'd
love to hear from you too.

In my opinion, there's a difference between user defined types and
what other engines call "domains". A CRL-based type would be a
true "user defined type", while if you're talking about an INT with
an additional constraint, you're actually sub-typing an existing types
or defining a "domain of valid values" for a data-type. SQL Server
used to call these "User Defined Datatypes" ( I believe ).

You're safe to use these (instead of the CLR type) and yes you client
applications will be able to recognize those as being of type "int".
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase
SQL Anywhere, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

[Erland Sommarskog]

Tom van Stiphout (to*************@cox.net) writes:

I'm designing a new database from scratch. It has some columns that I
currently have as datatype=int with check constraint of x>=1 and x
<=32, or others with a range of 1-3, etc. It occurred to me I could
create a UDDT to represent each. Benefit would be that I don't have to
enter the check constraints all the time.

Is there any consensus on whether or not this is a good idea? I am a
bit concerned my front-end application (.NET) will not be able to
recognize these types as easily as "int".

If you have any experience using a CLR-defined type in SQL Server I'd
love to hear from you too.

There is some confusion here. The CREATE TYPE command can be used to
define types in two ways:

CREATE TYPE mytype FROM varchar(23)
CREATE TYPE mytype EXTERNAL NAME ...

The former is known as "alias data types" in Books Online and User-Defined
Data types" in Mgmt Studio. MS appears to prefer User-Defined Types (UDT)
for the latter. I prefer to call them CLR types.

It seems that you have CLR types in mind, but I am not sure.

Anyway, I think that creating a CLR type to encode an integer value
that may only be a in certain range, is making thins overly complex.

On the other hand, using an alias data type could be a could solution
in lieu of a domain:

CREATE TYPE mytype AS tinyint
CREATE RULE myrule AS @x IN (1, 2, 3)
EXEC sp_bindrule myrule, mytype1to3

Bound rules and defaults have been deprecated, and originally Microsoft
said that SQL 2008 would be the last version to have them. I beat them
up over that, pointing out that as long as they don't have real domains,
they need to keep bound rules and defauls.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>Is there any consensus on whether or not this is a good idea?<<

Bad idea.

>I am a bit concerned my front-end application (.NET) will not be able to recognize these types as easily as INTEGER. <<

And neither will any other piece of software that uses Standard SQL.
You are locking yourself in the MS ghetto in a world of heterogeneous
data sources.

>If you have any experience using a CLR-defined type in SQL Server I'd love to hear from you too. <<

I have a client who is trying to get rid of them from a disaster. Not
all CLR languages agree on how to represent Booleans, how MOD() works
and other things.

Remember back when you were a procedural language programmer? The
late Ken Henderson had a great safety still applies to DDL and
declarative code, too. Someone maintaining code has to be much
smarter than the person who wrote it -- they have to figure out what
he did and what he meant to do.

So if you write the trickiest, most proprietary code you can, then you
have to be much smarter than yourself to even be able to read. What
chance dos the other guy have?

Repeat after me: "My favorite flavor of code is VANILLA! I will not
write Chunky Monkey Squid code! "

[Martijn Tonies]

Bound rules and defaults have been deprecated, and originally Microsoft
said that SQL 2008 would be the last version to have them. I beat them
up over that, pointing out that as long as they don't have real domains,
they need to keep bound rules and defauls.

Nice one Erland :-)
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase
SQL Anywhere, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com