SqlDbType & VarChar(max) columns

Question

_{Tuesday, November 19, 2013 11:14 PM}

Hi everybody,

We're having a discussion with my colleague as what to use for varchar(max) parameter to SQL Server procedure in .NET code.

He suggests using SqlDbType.Text and I am thinking of SqlDbType.VarChar, -1 as I've seen in the samples here http://stackoverflow.com/questions/973260/what-size-do-you-use-for-varcharmax-in-your-parameter-declaration and there is a link to documentation showing such implementation in the example code.

However, documentation for SqlDbType enumeration is contradictory:

http://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx#feedback

It says:

String. A variable-length stream of non-Unicode characters ranging between 1 and 8,000 characters. Use VarChar when the database column is varchar(max).

So, if it's between 1 and 8,000 characters, then why does it suggest to use it for varchar(max) columns which may contain more text?

Can someone please clarify this problem and the documentation?

Thanks in advance.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

All replies (5)

_{Wednesday, November 20, 2013 12:26 PM ✅Answered | 1 vote}

Hi Noami,

yes, the documentation could be improved here.

The Size property for all MAX data types should set to -1.  The mapping to SqlDbType is:

• VARCHAR(MAX) => SqlDbType.VarChar
• NVARCHAR(MAX) => SqlDbType.NVarChar
• VARBINARY(MAX) => SqlDbType.VarBinary

If only the Value property for a SqlParameter is set, the SQL Server data type will be inferred from the actual value length. A String with equal or less 4000 bytes is mapped to NVARCHAR(4000), a larger value is mapped to NVARCHAR(MAX).

The SqlDbType.Text, SqlDbType.NText and SqlDbType.Image are mapped to the (deprecated) SQL Server Types and should be avoided for MAX data types.

An example to illustrate SqlDbType and Size:

public static void SqlParameterSqlDbTypeMapping()
{
    using (var connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=SSPI;"))
    {
        connection.Open();

        var command = new SqlCommand("SELECT "
                + "@VARCHAR_01 AS VARCHAR_01, "
                + "@VARCHAR_02 AS VARCHAR_02, "
                + "@VARCHAR_03 AS VARCHAR_03, "
                + "@NVARCHAR_01 AS NVARCHAR_01;"
                , connection);
        // inferred from string length
        command.Parameters.Add("@VARCHAR_01", SqlDbType.VarChar, 0).Value = "VARCHAR_01";
        // inferred from string length and more than 8000 characters
        command.Parameters.Add("@VARCHAR_02", SqlDbType.VarChar, 0).Value = "VARCHAR_02_".PadRight(8001, 'x');
        // Size -1 => VARCHAR(MAX)
        command.Parameters.Add("@VARCHAR_03", SqlDbType.VarChar, -1).Value = "VARCHAR_03";
        
        // Defaults to NVarChar with size inferred
        command.Parameters.AddWithValue("@NVARCHAR_01", "NVARCHAR_01");

        using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
        {
            var table = reader.GetSchemaTable();

            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine("{0} = {1}({2}) => SqlDbType.{3}",
                    row["ColumnName"], 
                    row["DataTypeName"], 
                    row["ColumnSize"],
                    Enum.GetName(typeof(SqlDbType), row["NonVersionedProviderType"]));
            }
        }
    }
}

ColumnSize displays  2147483647 (MaxInteger) for VARCHAR(MAX).

Regards, Elmar

_{Wednesday, November 20, 2013 12:34 AM}

You misread this.

VARCHAR(MAX) is limited to 8k Chars.

STRING is limited to 2GiB or 1 Billion Chars (but it is kinda hard to say with Unicode)

Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2 Please mark post as helpfull and answers respectively.

_{Wednesday, November 20, 2013 2:34 AM}

I don't understand what did I misread. varchar(max) can have up to 2GB string. My question is what should be used for varchar(max) parameter from .NET (which SqlDbType) and why documentation says use VarChar for varchar(max) but states that SqlDbType.VarChar can hold up to 8K characters. I see a contradiction here which needs to be clarified.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

_{Wednesday, November 20, 2013 10:13 AM | 1 vote}

As per the source link 

"SQL Server provides the max specifier, which expands the storage capacity of the varchar, nvarchar, and varbinarydata types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data."

So, Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length.

However, I found it very interesting to research on and I will get back to you with my findings.. meanwhile, you can read below link to hold your curiosity for a while :)

http://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable

_{Wednesday, November 20, 2013 3:18 PM}

Very interesting thread, thanks for sharing.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles