Differences in varchar size usage, declaring the maximum available of 8000 characters

Hi, I actually found a thread on this subject and post a follow-up question to it, but it seems that nobody is viewing the thread, probably because it is marked as answer, as such, I post a new thread for my question.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87635&SiteID=1

My question is mainly on the differences between using a varchar(500) compared to varchar(8000), in the event when the stored string is less than 500 characters.

From the explanations that I see from the thread, can I say that to declare a column with a size of 8000 will not have any difference in storage/performance when compared to a column declared with a size of 500, when the stored string in the column is of 100 characters? The drawback of having a larger size declaration is that the probability that a string of 8000 characters might be stored in it in the future (either accidentally or breaking a non desire rule)? So the problem is not a technical one but rather on the soft side where we left a loose control whereby we leave a chance that it might cause a technical issue in the future (worst case)?

Apart from stored data in table, how about a declaration of varchar(8000) in a stored procedure variable or parameter?

Thanks

Eugene

The storage of varchar is always the same. We store the length of the actual data, and the data itself. The length in the table definition is used to validate that the varchar is not longer than the declared length, but does not take up any storage.

The same argument is valid when you use variables as well.

My suggestion would be that you define the size of the variable or column to what makes sense to your application. If you never need more than 500 chars, I don't see the need to define a table as varchar(8000).

Thanks,

Marcel van der Holst
[MSFT]|||

Hi Marcel,

Thanks for the answer, will help me much in designing table. So, about the variables, from your answer, you mean the system would not allocate enough space in memory according to the declared size? What I mean is, the storage space in memory will keep growing as the string length changes, or maybe it is actually creating a new string altogether in a new memory space; rather than allocating a large enough space earlier?

In this case, if the memory space is kept enlarging, or allocating a new space, due to some string intensive operation in the defined procedure/function, would it affect the performance, as the developer can't get to define the maximum probable size, which he knows/can assume?

thanks a million

Eugene

|||

SQL Server does not really allocate space based on the datatype definition in your table. It allocates enough space to hold the data (so if you have 10 chars, it only allocates space for 10 chars). After the data is in memory, one of the checks we do is check the length of the data against the defined length. If the size of the data is bigger, we raise an error. Internally, SQL Server uses several algorithms to avoid too many memory allocations (sometimes it allocates more than needed if it finds out that a lot of string concatenation is being done). The defined size of a variable does not really play a big role here.

Are you using SQL Server 2000 or SQL Server 2005?

If you use SQL Server 2005, you can use the varchar(max) datatype, which will hold data up to 2GB, and you never really have to worry about the size. SQL Server will do the right thing for you. Only when your data gets bigger than 8K, you might get a small perf hit when using varchar(max) as it won't fit on a single page anymore, but if you have a lot of data that is smaller than 8K, you might as well use varchar(max) as internally in the engine varchar(N) and varchar(max) are treated very simular as long as the data is less than 8K.

Hope this helps,

Marcel van der Holst

[MSFT]

|||

Cool work there, I got you. For the table, I think it's pretty straightforward where it is only storing the exact string (length) with some overhead. So for variable/parameter in memory, the memory space is actually a dynamic one (where it can grow or something else), but SQL Server is smart enough to allocate a size that should optimize the situation.

I guess my understanding should be correct, right?

I use both SQL Server 2000 and SQL Server 2005.

Thanks, it helps.

Eugene

|||Yes, your understanding is correct.

Thanks,

Marcel van der Holst
[MSFT]