8

Possible Duplicate:
is there an advantage to varchar(500) over varchar(8000)?

I am currently working on a table which has lots of columns with varchar(50). The data, we now have to insert in some columns is above 50 characters so we have to change the column size from 50 to 128 for those columns, since we have a lot of columns its a waste of time to change individual columns.

So I proposed to my team, why dont we change all the columns to varchar(128). Some of the team mates argued that this will cause a performance hit during select and join operations.

Now I am not an expert on databases but I dont think moving from varchar 50 to varchar 128 will cause any significant performance hit.

P.S - We dont have any name, surname, address kind of data in those columns.

Community
  • 1
  • 1
Sumedh
  • 638
  • 2
  • 15
  • 26
  • well, it would only affect performance during join operations if you have queries that join on `varchar` columns. *Do* you have queries that join on `varchar` columns? A lot of people would say that's inadvisable... – AakashM Jul 16 '12 at 08:37

4 Answers4

6

varchar(50) and varchar(128) will behave pretty much identical from every point of view. The storage size is identical for values under 50 characters. They can be joined interchangeably (varchar(50) joined with varchar(128)) w/o type convertion issues (ie. an index on varchar(50) can seek a column varchar(128) in a join) and same applies to WHERE predicates. Prior to SQL Server 2012 ncreasing the size of a varchar column is a very fast metadata-only operation, after SQL Server 2012 this operation may be a slow size-of-data-update-each-record operation under certain conditions, similar to those descirbed in Adding a nullable column can update the entire table.

Some issues can arrise from any column length change:

  • application issues from handling unexpected size values. Native ones may run into buffer size issues if improperly codded (ie. larger size can cause buffer overflow). Managed apps are unlikely to have serious issues, but minor issues like values not fitting on column widths on screen or on reports may occur.
  • T-SQL errors from truncating values on insert or update
  • T-SQL silent truncation occuring and resulting in incorrect values (Eg. @variables declared as varchar(50) in stored proc)
  • Limits like max row size or max index size may be reached. Eg. you have today a composite index on 8 columns of type varchar(50), extending to varchar(128) will exceed the max index size of 900 and trigger warnings.

Martin's warning about memory grants incresing is a very valid concern. I would just buy more RAM if that would indeed turn out to be an issue.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Remus, those are great points, obviously we will brainstorm on this. I wanted to know if the performance issue is genuine or not and I got my answer thanks to you and Martin. – Sumedh Jul 17 '12 at 07:40
2

Please check here Performance Comparison-of-varchar(max) vs varchar(n)

sganesh
  • 79
  • 6
  • The question isn't about `varchar(max)` vs `varchar(n)` it is about `varchar(n1)` vs `varchar(n2)` – Martin Smith Jul 16 '12 at 08:52
  • I agree but this is the same situation if you create any columns which really do not want more space better create the required space only and creating more space will cause the performance issue while joining table and where clause. – sganesh Jul 16 '12 at 08:59
0

How many are such columns? The best practice rule says you need to plan each column carefully and define size accordingly. You should identify columns suitable for varchar(128) and not just increase size of all columns blindly.

Geek
  • 429
  • 2
  • 5
  • There are about 120 columns, initially all of them were varchar(50)( I still dont have satisfactory answer why all the columns were varchar 50). Yes, I agree, best practice is to identify columns, the guy who is in charge of this is busy on some other high priority issues but when the product throws an error because of size limitations in front of the customer its embarassing. – Sumedh Jul 16 '12 at 10:11
  • 1
    I'd say they were all varchar(50) because when you type as much as "varc", SQL auto-completes with "varchar(50)" – strattonn Oct 13 '16 at 07:16
0

I would suggest that change only the columns which needs to be changed.if you do not need more than 50 chars in any column do not change that.

Why are you intersted in making the length same for all columns?I doubt all columns has same length requirements.

Gulli Meel
  • 891
  • 4
  • 6