VARCHAR to MAX or not

Is there a performance penalty for me to use VARCHAR(MAX) versus VARCHAR(3000).  I have an string variable in a SQL stored procedure which will most certain be expanded over time.  Is VARCHAR(MAX) equal to VARCHAR(3000) when it comes to speed and memory usage?
Who is Participating?
Aneesh RetnakaranDatabase AdministratorCommented:
now i got it, you can go ahaed with varchar(max)
Aneesh RetnakaranDatabase AdministratorCommented:
there could be some slight performace issue, dont go for varchar(max) unless the rowsize exceeds 8060 bytes, because the query optimiser considers the length of the field for creating an optimal plan
Phil5780Author Commented:
The variable in question is central to constructing a dynamic query (via sp_executesql).  Would I be better off dividing out my logic into smaller queries and manipulating data using a temp table?  I've heard temp tables should always be avoided.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.