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/