VARCHAR to MAX or not

Posted on 2009-12-29
Last Modified: 2012-05-08
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?
Question by:Phil5780
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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

    Author Comment

    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.
    LVL 75

    Accepted Solution

    now i got it, you can go ahaed with varchar(max)

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now