April 2, 2013 at 12:03 am
Hi All,
Is there any performance difference between VARCHAR(200) and VARCHAR(2000), if input string is not going to exceed 200 characters.
April 2, 2013 at 2:12 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2013 at 3:12 am
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!!!
April 2, 2013 at 3:23 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2013 at 3:35 am
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.
April 2, 2013 at 3:37 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2013 at 4:10 am
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[/url]
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
April 2, 2013 at 6:10 am
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
April 2, 2013 at 6:38 am
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).
April 2, 2013 at 7:16 am
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/
April 2, 2013 at 8:43 am
theashishtrivedi (4/2/2013)
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).
There's no space reservation. If you define a VARCHAR(8000) and store 20 charactersin it, it's the same in terms of storage as a VARCHAR(20) with 20 characters in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2018 at 12:45 am
T.Ashish - Tuesday, April 2, 2013 6:38 AMI 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).
in addition to "Adi" comment. If you used sp_executesql,
1)Memory wastage
2)Network traffic
---Good
SELECT 'EXEC sp_executesql N''SELECT * FROM [HumanResources].[Employee] WHERE [LoginID] = @name'', N''@name varchar(100)'', @name = ''adventure-works\guy1'';',
DATALENGTH('EXEC sp_executesql N''SELECT * FROM [HumanResources].[Employee] WHERE [LoginID] = @name'', N''@name varchar(100)'', @name = ''adventure-works\guy1'';') network_traffic_good
---Bad
SELECT 'EXEC sp_executesql N''SELECT * FROM [HumanResources].[Employee] WHERE [LoginID] = @name'', N''@name varchar(8000)'', @name = ''adventure-works\guy1'';',
DATALENGTH('EXEC sp_executesql N''SELECT * FROM [HumanResources].[Employee] WHERE [LoginID] = @name'', N''@name varchar(8000)'', @name = ''adventure-works\guy1'';') network_traffic_bad
if you have 100 or 1000 SQLs like that if one SQL wastage 1 bytes means millions of SQLs, will wast millions of bytes.
Tharindu Dhaneenja.
MCTS,MCITP(SQL Server),OCA(Oracle)
http://www.databaseusergroup.com
December 28, 2018 at 9:31 am
I would add that there may be issues with extracting the data to another location, like a data warehouse. I have run into these issues, albeit more with an unwarranted use of varchar(max). I have read about the indexing before. This is also, as has been indicated, a matter of data governance. If a correct answer can't be more than 200 characters, why do you want to provide the space for 2000? I've worked with systems where nvarchar(50) was the standard minimum. So even if the field would only ever hold eight characters because they held dates in a YYYYMMDD format, they were nvarchar(50) when a char(8) would have been perfect. These systems performed slower than others I worked on where the data types were more correctly chosen. I can't attribute it directly to the "minimum standard," but based on what I'd read on indexing, I had to assume it might have been partly responsible.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply