Varchar (255) db performance

[maclav]

Hi
I am working on a new db and have found that in the users area they have varchar(255) from every column including title and telephone number.
I there apprectiable impact on db performance for having varchar(255) with only 5 of those being used.
My db programmer instincts makes me real at this but my manager wont allow me to change it unless it will improve performance.
Thanks
Malcav

 

[AlexCuse]

It probably won't be noticeable because its' varchar and not char, but you are right that you should change it. If it should never contain > 5 characters, do you really want to allow the possibility of that happening?

Also remember that the 250 extra characters count towards your maximum table width.

The varchar(255) is common for db's moved from access using the wizard (I believe that is the default for access if you don't specify size for a text column).

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[SQLDenis]

>>Also remember that the 250 extra characters count towards your maximum table width.


you can still create something like this and you will get a warning and as long you don't fill it all up you are fine

create table MaximusColumusAllowdus (col1 varchar(5000),col2 varchar(5000),col3 varchar(5000))

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Denis can you find me any whitepapers from Microsoft explaining that



[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[SQLDenis]

no they seem to be unbleached and not really whie

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[maclav]

Thanks for your help.
I will have to try to explain to the none technological minded what this is bad even though it has not effect on how fast the batabase runs.
Malcav

 

[AlexCuse]

good luck! Usually they can understand data integrity concerns. If there is any way they could be sued if bad data gets into that column, that will get their attention too ;-)

Denis - sorry, it must be white. Websites unacceptable also.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[SQLDenis]

>>Denis - sorry, it must be white. Websites unacceptable also.

white uses too much energy and increases you carbon footprint

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[harebrain]

can you find me any whitepapers from Microsoft explaining that

Neither can I, but I can tell you from experience with a former employer whose database was built like that that data loss will occur without warning.

If that's a concern for the OP, he/she might be interested in this thread thread183-1346490.

 

[SQLDenis]

>>Neither can I, but I can tell you from experience with a former employer whose database was built like that that data loss will occur without warning



???, should fail

create table MaximusColumusAllowdus (col1 varchar(5000),col2 varchar(5000),col3 varchar(5000))

insert MaximusColumusAllowdus
select replicate('a',5000),replicate('a',5000),replicate('a',5000)

Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 15015 which is greater than the allowable maximum of 8060.
The statement has been terminated.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[harebrain]

I should have said "can" instead of "will." I think it depends on the version of SQL Server, and whether your access is direct or through ADO. (Might have been some crappy programming.)

 

[ESquared]

There are several concerns with varchar columns that are too large. You are right that they don't take up extra space simply by allowing the longer lengths, but...

• This DOES allow entry of data that can take up more space than necessary.
• If data is exceedingly longer than that expected for a column, it's probably in error. Reduce the chance for weird excessive data by making column sizes appropriate. I believe it is best practice to have maximum lengths that are tuned to the expected data. Not only does having every column varchar(255) point to a wizard-created or ODBC-imported database (shudder) but is one of those "that's just WRONG" things for any experienced developer.
• As others have alluded to, if a column does get too much data in it, the update or insert will suddenly fail. This is like having a car that explodes if you exceed 90 mph. That it's true you'll "probably never go that fast" is small comfort the day that it actually happens.
• The idea that the application will control ALL data entry so you don't have to worry about data lengths is a poor one. My experience is that constraints tend to show up for the *developer* rather than the user of the finished application. That is, how do you know you're not building the application incorrectly in the first place? Also, some day someone will load data directly into the database, circumventing the application. When that happens, it's best that the person doing it get a data truncation insert failure rather than months or years down the road discover the problem.

You can run some queries to find out actual data sizes:

SELECT Max(Len(Column)) FROM Table

Then change that column to a size that is a little bigger.