CODEWITHSUNDEEP
sql-servert-sql
Allan Xu
Allan Xu

Reputation: 9358

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

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?

Upvotes: 4

Views: 530

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

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.

Upvotes: 4

Related Questions