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

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

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