4

In my SP, I need to work with a string that could be up to 40 characters.

Would it make a difference if I use VARCHAR(4000) instead of VARCHAR(40)?

Update:

The reason I'm asking this question is that to my understanding the memory usage of a VARCHAR(4000) and VARCHAR(40) variable is the same. I'm trying to figure out why should I limit myself to a VARCHAR(40) variable? Why not VARCHAR(4000)? Is it because performance or memory efficiency?

Update 2, Important assumption:

I assume when we store the string "Hello World" in a variable with either of VARCHAR(4000) or VARCHAR(40) types, it takes the same amount of memory. Am I right?

Community
  • 1
  • 1
Allan Xu
  • 7,998
  • 11
  • 51
  • 122
  • What difference are you interested/concerned about? – Bob Kaufman Aug 22 '16 at 04:41
  • @BobKaufman: I expect that should be very obvious. memory, performance? – Allan Xu Aug 22 '16 at 04:43
  • This is a non specific question. Not really possible to answer it. So have down voted – SyntaxGoonoo Aug 22 '16 at 04:44
  • @BobKaufman: I think I should ask, what difference do i need to be interested or concerned about? – Allan Xu Aug 22 '16 at 04:44
  • It is about performance or memory efficiency. – toha Aug 22 '16 at 04:50
  • @SyntaxGoonoo: please read my update. If my understanding of VARCHAR variables in TSQL is correct, then I have a point here. If still the question is not clear, please ask so I can explain. – Allan Xu Aug 22 '16 at 04:51
  • @toha: I assume when you store a string "Hello World" in a variable with either of VARCHAR(4000) or VARCHAR(40) type, it takes the same amount of memory. Am I right? – Allan Xu Aug 22 '16 at 04:54
  • as Far as I know, it is different. Varchar(4000) will take higher – toha Aug 22 '16 at 04:55
  • 1
    @AllanXu:See this answer..http://stackoverflow.com/a/5654947/2975396 – TheGameiswar Aug 22 '16 at 04:56
  • Also this:http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/ – TheGameiswar Aug 22 '16 at 04:57
  • @TheGameiswar: You nailed it. Thank you!. How did you find such relevant posts so quickly? Did you know them before you there is a search technique that I can learn from you? – Allan Xu Aug 22 '16 at 05:02
  • I add site:stackoverflow.com to search .Also i go through all answers most of time and bookmark ,so i can recall specific person answered it and i will add that to search as well – TheGameiswar Aug 22 '16 at 05:03
  • @AllanXu:please upvote those answers whatever you feel helpfull, as a small gratitude for sharing their amazing knowledge – TheGameiswar Aug 22 '16 at 05:07
  • @TheGameiswar: Can you kindly explain the "plus answerer name to" part? It would be very helpful if you post your search phrase(s). Alternatively, should I post to SO Meta? I expect this will help others as well. – Allan Xu Aug 22 '16 at 05:08
  • For this issue i searched like this..`varchar vs varchar(max) Martin Smith or varchar vs varchar(max) Remus Rusanu` each one seperately – TheGameiswar Aug 22 '16 at 05:19
  • Also this meta link might help you:http://meta.stackoverflow.com/questions/332773/what-is-the-best-way-to-google-for-problems-involving-actual-stack-overflow-with?cb=1 – TheGameiswar Aug 22 '16 at 05:23
  • @TheGameiswar: Thank you for help. I am finding this link very helpfull as well: https://api.stackexchange.com/docs/advanced-search – Allan Xu Aug 22 '16 at 05:29
  • You would use it when you want to limit the data size. Not often but some time you do want to limit the data. There is not reason X to have more than 80 chars so throw an error. In testing an application would only need to test up to the max size. – paparazzo Aug 22 '16 at 07:31

1 Answers1

4

Declaring a variable as VARCHAR(40) or VARCHAR(4000) behaves identically at runtime from performance or memory consumption point of view. But there is an important behavior difference: VARCHAR(40) may silently truncate a value to 40 length. This is why is best to use VARCHAR(8000) for variables (or NVARCHAR(4000)), to avoid the risk of silent truncation. Do not use VARCHAR(MAX) unless the value is a MAX, as MAX types do behave differently at runtime.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569