Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

VARCHAR(200) vs VARCHAR(2000) Expand / Collapse
Author
Message
Posted Tuesday, April 02, 2013 12:03 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:20 PM
Points: 715, Visits: 520
Hi All,

Is there any performance difference between VARCHAR(200) and VARCHAR(2000), if input string is not going to exceed 200 characters.
Post #1437710
Posted Tuesday, April 02, 2013 2:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
No, but if the string will never exceed 200 characters, why consider a varchar(2000)? Max 200 characters = varchar(200)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1437737
Posted Tuesday, April 02, 2013 3:12 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:20 PM
Points: 715, Visits: 520
Yes, that is the point. 200 characters should be stored in VARCHAR (200), not in VARCHAR (2000). But why? I can use VARCHAR (2000) to store 200 characters. what makes the difference!!!
Post #1437760
Posted Tuesday, April 02, 2013 3:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
Not sure if that was a question with all the !!! after it, but...

Use the smallest data types for the data. If something will be 20 characters, use varchar(20). If it might be up to 250 characters, use a varchar(250). Yes, you could make everything varchar(8000), but why would you unless going for the lazy approach of 'I don't care what goes in this variable'?

Data lengths are partial documentation, if I see a varchar(20) parameter, I don't have to worry whether there's maybe 5600 characters in it, there aren't, there are 20. They're a form of constraint, someone accidentally passes 75 characters and they get an error instead of SQL blindly accepting the incorrect data and possibly breaking elsewhere.

Varchar(8000) everywhere is lazy coding, when I see it it tells me that the developer couldn't be bothered to do a proper job and just went with the quick approach.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1437765
Posted Tuesday, April 02, 2013 3:35 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:20 PM
Points: 715, Visits: 520
Thanks for reply.

I agree with your explanation and I do follow as you said. Can you tell me if there is any performance gain, if I maintain the length of VARCHAR as per requirement. I want to convince my manager that VARCHAR(2000) is really not required everywhere.
Post #1437771
Posted Tuesday, April 02, 2013 3:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
theashishtrivedi (4/2/2013)
Can you tell me if there is any performance gain, if I maintain the length of VARCHAR as per requirement.


I already answered that, in the first reply.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1437773
Posted Tuesday, April 02, 2013 4:10 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:57 AM
Points: 704, Visits: 3,270
theashishtrivedi (4/2/2013)
Thanks for reply.

I agree with your explanation and I do follow as you said. Can you tell me if there is any performance gain, if I maintain the length of VARCHAR as per requirement. I want to convince my manager that VARCHAR(2000) is really not required everywhere.


As has been said already , no difference if it's VARCHAR(200) VARCHAR(2000) or VARCHAR(6000). A single character will occupy a single byte.

DECLARE @Test1 VARCHAR(200)
DECLARE @Test2 VARCHAR(2000)
DECLARE @Test3 VARCHAR(8000)

SET @Test1 = 'ABC'
SET @Test2 = 'ABC'
SET @Test3 = 'ABC'

select DATALENGTH(@test1), DATALENGTH(@test2), DATALENGTH(@test3)

200, 2000 or 8000 won't hurt your performance but queries that request more data than they need will.



---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1437788
Posted Tuesday, April 02, 2013 6:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 12,755, Visits: 31,122
you could run into problems with inserting/updating rows with unecessarily oversized rows of data;
SQL still has a max row size of 8060, so if you had an update statement that tried to manipulate too many oversized columns, you'll get an error.



Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
-- INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437853
Posted Tuesday, April 02, 2013 6:38 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:20 PM
Points: 715, Visits: 520
I accept, there is no performance difference.

Also, the 2 bytes VARCHAR is using to store length of string is consuming disk space but not affecting the performance.

But still one doubt.

If I define VARCHAR(8000) and store 10-20 characters in it, there should be some performance issue in long run (i.e. SQL Server will reserve the space for 8000 characters which is not utilized).
Post #1437863
Posted Tuesday, April 02, 2013 7:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
theashishtrivedi (4/2/2013)
I accept, there is no performance difference.

Also, the 2 bytes VARCHAR is using to store length of string is consuming disk space but not affecting the performance.

But still one doubt.

If I define VARCHAR(8000) and store 10-20 characters in it, there should be some performance issue in long run (i.e. SQL Server will reserve the space for 8000 characters which is not utilized).


Actually there is a significant difference that in some situations can lead to performance problems. Each query needs to get memory in order to run. The server estimates how much memory is needed to the query. The main factors that are used to determine how much memory is needed are:

1) The operators that will be used.
2) Number of records that each operator works with
3) Record's size

When the record has varying size columns, it estimates the average size of the varying column. The bigger the defined size, the bigger the estimation is and the bigger the memory that will be used by the query. In cases that you have lots of records together with an operator that needs lots of memory (for example order) and you used way to long varchar column (for example varchar(2000) instead of varchar(20)), the server will try to allocate much more memory then the amount of memory that is really needed to the query. Adam Machanic did a presentation in Pass 2 years ago with the title "Zen and the Art of Workspace Memory". I didn't find the presentation on line, but he did post the demos for it. You can find it at http://sqlblog.com/blogs/adam_machanic/archive/2011/10/16/pass-summit-2011-zen-and-the-art-of-workspace-memory-demos.aspx

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1437876
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse