What Size Do You Use for Varchar(Max) in Your Parameter Declaration

What size do you use for varchar(MAX) in your parameter declaration?

In this case you use -1.

What SqlDbType maps to varChar(max)?

Actually you can use it with VarChar. If it is a parameter you can build it with: new SqlParameter("@myparam", SqlDbType.NVarChar, -1)

See: http://msdn.microsoft.com/en-us/library/bb399384.aspx

Maximum size of a varchar(max) variable

As far as I can tell there is no upper limit in 2008.

In SQL Server 2005 the code in your question fails on the assignment to the @GGMMsg variable with

Attempting to grow LOB beyond maximum allowed size of 2,147,483,647
bytes.

the code below fails with

REPLICATE: The length of the result exceeds the length limit (2GB) of
the target large type.

However it appears these limitations have quietly been lifted. On 2008

DECLARE @y VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),92681); 

SET @y = REPLICATE(@y,92681);

SELECT LEN(@y) 

Returns

8589767761

I ran this on my 32 bit desktop machine so this 8GB string is way in excess of addressable memory

Running

select internal_objects_alloc_page_count
from sys.dm_db_task_space_usage
WHERE session_id = @@spid

Returned

internal_objects_alloc_page_co 
------------------------------ 
2144456    

so I presume this all just gets stored in LOB pages in tempdb with no validation on length. The page count growth was all associated with the SET @y = REPLICATE(@y,92681); statement. The initial variable assignment to @y and the LEN calculation did not increase this.

The reason for mentioning this is because the page count is hugely more than I was expecting. Assuming an 8KB page then this works out at 16.36 GB which is obviously more or less double what would seem to be necessary. I speculate that this is likely due to the inefficiency of the string concatenation operation needing to copy the entire huge string and append a chunk on to the end rather than being able to add to the end of the existing string. Unfortunately at the moment the .WRITE method isn't supported for varchar(max) variables.

Paul White confirms the above supposition here and also provides the information that the variable is held entirely in memory if <= 512KB and changes to the tempdb-based backup scheme for values larger than that.

Addition

I've also tested the behaviour with concatenating nvarchar(max) + nvarchar(max) and nvarchar(max) + varchar(max). Both of these allow the 2GB limit to be exceeded. Trying to then store the results of this in a table then fails however with the error message Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. again. The script for that is below (may take a long time to run).

DECLARE @y1 VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),2147483647); 
SET @y1 = @y1 + @y1;
SELECT LEN(@y1), DATALENGTH(@y1)  /*4294967294, 4294967292*/

DECLARE @y2 NVARCHAR(MAX) = REPLICATE(CAST('X' AS NVARCHAR(MAX)),1073741823); 
SET @y2 = @y2 + @y2;
SELECT LEN(@y2), DATALENGTH(@y2)  /*2147483646, 4294967292*/

DECLARE @y3 NVARCHAR(MAX) = @y2 + @y1
SELECT LEN(@y3), DATALENGTH(@y3)   /*6442450940, 12884901880*/

/*This attempt fails*/
SELECT @y1 y1, @y2 y2, @y3 y3
INTO Test

OdbcParameter error - size of 0 for varchar(MAX) output parameter

If you want to stick with System.Data.Odbc then the following workaround might suffice. For the stored procedure

CREATE PROCEDURE [dbo].[HodorSpeaks] 
    @repeat int = 1, 
    @response varchar(max) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @whatHeSaid varchar(max);
    SET @whatHeSaid = 'HODOR! ';
    SELECT @response = REPLICATE(@whatHeSaid, @repeat);
END

the C# code

using (var cmd = new OdbcCommand())
{
    cmd.Connection = conn;
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = 
        "SET NOCOUNT ON; " +
        "DECLARE @out varchar(max); " +
        "EXEC dbo.HodorSpeaks @repeat=?, @response=@out OUTPUT; " +
        "SELECT @out;";
    cmd.Parameters.Add("?", OdbcType.Int).Value = 10000;
    string resp = cmd.ExecuteScalar().ToString();
    Console.WriteLine("{0} characters were returned", resp.Length);

}

tells me that

70000 characters were returned

Is Varchar(Max) take much space than Varchar(N) if text length is fixed?

Varchar(MAX) & Varchar(n) reserve fix space for same text store in Column.
I have two table tblMaxData & tblNData and both table have single column with VARCGAR(MAX) & VARCHAR(N). Column have 'Hello' Value in Both Table. After then i was checked space , both column has same occupied Space.

How to add SQL parameter with MAX value in C#

Just use VARCHAR or NVARCHAR without specifying the size, which is default to MAX, but should not exceed 8000 for VARCHAR and 4000 for NVARCHAR

Best practices for SQL varchar column length

No DBMS I know of has any "optimization" that will make a VARCHAR with a 2^n length perform better than one with a max length that is not a power of 2.

I think early SQL Server versions actually treated a VARCHAR with length 255 differently than one with a higher maximum length. I don't know if this is still the case.

For almost all DBMS, the actual storage that is required is only determined by the number of characters you put into it, not the max length you define. So from a storage point of view (and most probably a performance one as well), it does not make any difference whether you declare a column as VARCHAR(100) or VARCHAR(500).

You should see the max length provided for a VARCHAR column as a kind of constraint (or business rule) rather than a technical/physical thing.

For PostgreSQL the best setup is to use text without a length restriction and a CHECK CONSTRAINT that limits the number of characters to whatever your business requires.

If that requirement changes, altering the check constraint is much faster than altering the table (because the table does not need to be re-written)

The same can be applied for Oracle and others - in Oracle it would be VARCHAR(4000) instead of text though.

I don't know if there is a physical storage difference between VARCHAR(max) and e.g. VARCHAR(500) in SQL Server. But apparently there is a performance impact when using varchar(max) as compared to varchar(8000).

See this link (posted by Erwin Brandstetter as a comment)

Edit 2013-09-22

Regarding bigown's comment:

In Postgres versions before 9.2 (which was not available when I wrote the initial answer) a change to the column definition did rewrite the whole table, see e.g. here. Since 9.2 this is no longer the case and a quick test confirmed that increasing the column size for a table with 1.2 million rows indeed only took 0.5 seconds.

For Oracle this seems to be true as well, judging by the time it takes to alter a big table's varchar column. But I could not find any reference for that.

For MySQL the manual says "In most cases, ALTER TABLE makes a temporary copy of the original table". And my own tests confirm that: running an ALTER TABLE on a table with 1.2 million rows (the same as in my test with Postgres) to increase the size of a column took 1.5 minutes. In MySQL however you can not use the "workaround" to use a check constraint to limit the number of characters in a column.

For SQL Server I could not find a clear statement on this but the execution time to increase the size of a varchar column (again the 1.2 million rows table from above) indicates that no rewrite takes place.

Edit 2017-01-24

Seems I was (at least partially) wrong about SQL Server. See this answer from Aaron Bertrand that shows that the declared length of a nvarchar or varchar columns makes a huge difference for the performance.

Handling more than 8000 chars in stored proc parameter

You need to use nvarchar(max), instead of varchar(4000) or varchar(max). This can store up to 2 GB of text, which will solve your problem...

For more information see http://technet.microsoft.com/en-us/library/ms186939.aspx

---

Related Topics