1

I have never thought of this question until recently I was troubled by one of my tasks:

So, in my store procedure, I declare a parameter @temp_address with datatype VARCHAR.

This @temp_address will temporary store the result from multiple columns, for example:

SET @temp_address = (Select Address1 + Address2 + City + State + ZipCode + Country 
FROM some_table)

Because I don't really know exactly the combined size of - now the complete - address, I simply use VARCHAR(MAX). However, my boss rejected my work and told me not to use MAX, and I have to specify a size.

While it is possible for me to figure out the maximum possible size (by looking at each columns' size and sum them up), I'm just really curious, why do we need VARCHAR(N) instead of just simple VARCHAR(MAX)?

Same goes to NVARCHAR(MAX) vs NVARCHAR(N)

C.J.
  • 3,409
  • 8
  • 34
  • 51
  • 1
    You might look into the MS documentation. It explains this quite clearly. http://msdn.microsoft.com/en-us/library/ms176089.aspx – Sean Lange Sep 10 '14 at 21:04
  • Thank you for the link. However, it only tells us when to use which, but not why – C.J. Sep 10 '14 at 21:05
  • 1
    It'd be nice to know what your boss said about why not to use MAX. It sounds like it's another cargo cult rule of thumb that people don't understand but insist on anyway because that's what they were told, probably due to something that was true 10 versions ago. – siride Sep 10 '14 at 21:11
  • 3
    http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/ – Lamak Sep 10 '14 at 21:19
  • Well, the MS documentation talks about columns and your code just declares a variable. Even so I wouldn't go by the Rusanu performance tests as mentioned by @Lamak; instead, I would perform my own tests using my specific scenario and see what happens (and then I would defy my boss about his concerns ha ha). – Caffé Sep 10 '14 at 22:15

0 Answers0