Using User defined type instead of varchars

[Miles Ashton]

A common request for enhancement to applications is to "make this
field bigger". I know I've been caught with increasing a field size,
and then spending hours debugging because another stored proc has a
variable or temp table that uses the field defined as the original
varchar size. SQL Server just truncates to fit the data into the
smaller varchar, and so there are no errors raised.

An option suggested by a colleague is to no longer use varchars, but
use User Defined Types instead. To make this work effectively,
though, they suggest we would need to make it a rule that we do not
use varchars anywhere except to define user defined types.

Though there will be one point of changes I can't help thinking this
isn't a very good idea ! Any thoughts ?

Thanks.

[Simon Hayes]

ma*****@tmw.co. uk (Miles Ashton) wrote in message news:<bb******* *************** ****@posting.go ogle.com>...

A common request for enhancement to applications is to "make this
field bigger". I know I've been caught with increasing a field size,
and then spending hours debugging because another stored proc has a
variable or temp table that uses the field defined as the original
varchar size. SQL Server just truncates to fit the data into the
smaller varchar, and so there are no errors raised.

An option suggested by a colleague is to no longer use varchars, but
use User Defined Types instead. To make this work effectively,
though, they suggest we would need to make it a rule that we do not
use varchars anywhere except to define user defined types.

Though there will be one point of changes I can't help thinking this
isn't a very good idea ! Any thoughts ?

Thanks.

MSSQL will raise an error and will not INSERT the data if SET
ANSI_WARNINGS is ON, so I guess for some reason you have it OFF. This
is not recommended, because some queries (distributed queries, indexed
views) require it to be ON.

If you were to use a UDDT, then you would have the issue that you
cannot modify a UDDT, you can only drop and recreate it. So to change
your definition, you would need to unbind it, drop it, recreate it and
bind it again. This may or may not be less effort than modifying your
stored procedure code.

I suspect that the best solution to your issue is to use source code
control, and understand what your dependencies are. Although
sp_depends is not 100% reliable, you can query syscomments directly to
find out which procedures reference a table.

Simon

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

MSSQL will raise an error and will not INSERT the data if SET
ANSI_WARNINGS is ON, so I guess for some reason you have it OFF. This
is not recommended, because some queries (distributed queries, indexed
views) require it to be ON.
But this only applies to INSERT and UPDATE. Not when assigning to
variables or parameters, so if the truncation happens already there,
you won't be noticed.
If you were to use a UDDT, then you would have the issue that you
cannot modify a UDDT, you can only drop and recreate it. So to change
your definition, you would need to unbind it, drop it, recreate it and
bind it again. This may or may not be less effort than modifying your
stored procedure code.

But it has the distinct advantage of that you know where the problems are.
The actual change will take longer time, but once you are completed, you
can feel confident that your SQL code is fine.

By the way, the typical procedure to make a UDDT longer would be to first
rename the old type, create the new definition and the change the tables.

In the database I work with, almost all character columns defined through
UDDTs. There are a few exceptions, usually because there are some odd
special case in some odd table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp