473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Using User defined type instead of varchars

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.
Jul 20 '05 #1
2 1976
ma*****@tmw.co.uk (Miles Ashton) wrote in message news:<bb**************************@posting.google. 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
Jul 20 '05 #2
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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Jari Kujansuu | last post by:
I can successfully parse XML document using SAX or DOM and I can also validate XML document against schema. Problem is that my program should deal with user-defined schemas which means that when...
3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
17
by: Danny J. Lesandrini | last post by:
The following code works with a standard MDB to navigate to a particluar record (with a DAO recordset, of course) but it's giving me problems in an ADP I'm working on. Dim rs As ADODB.Recordset...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
1
by: Daveyk0 | last post by:
Hello there, I have a front end database that I have recently made very many changes to to allow off-line use. I keep copies of the databases on my hard drive and link to them rather than the...
30
by: junky_fellow | last post by:
I was looking at the source code of linux or open BSD. What I found that lots of typedefs were used. For example, consider the offset in a file. It was declared as off_t offset; and off_t is...
5
by: rAinDeEr | last post by:
Hi, I have a web application with a table to store terms and conditions of a Company. This may some times run into many pages and some times it may be just a few sentences. It is a character...
23
by: Bjorn | last post by:
Hi. Every time i post data in a form the contents are being checked for validity. When i click the back-button, all data is gone and i have to retype it. It's obvious that only a few or none of...
14
by: aaragon | last post by:
Hi everyone, I've been writing some code and so far I have all the code written in the .h files in order to avoid the linker errors. I'm using templates. I wanted to move the implementations to...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.