Post reply

VARCHAR(MAX) and limitations

  • September 15, 2010 at 3:52 pm

    #99357

    Alright, the question came up with what happens when the text string coming in is too big for the 'in row' BLOB of VARCHAR(MAX).

    [EDIT:]Please drop to second post for the proper code. The code in my first post left out a cast necessary to over-populate the VARCHAR(MAX) past 8k characters.[/EDIT]

    The setup:

    create table #tmp

    (tID INT IDENTITY( 1,1),

    TestString VARCHAR(MAX)

    )

    GO

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 10) + ',', 50)

    )

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 10) + ',', 75)

    )

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 10) + ',', 120)

    )

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 20) + ',', 120)

    )

    GO

    Yes, replicating a replicate works... 🙂

    Now, the test script:

    select

    charindex( ',', teststring) AS CommaPos,

    LEN( TestString) AS StrLen

    from#tmp

    This gets me these results:

    CommaPos StrLen

    -------------------- --------------------

    261 7830

    261 7830

    261 7830

    521 7815

    So, am I setting the VARCHAR(MAX) wrong, so that it will pop out of in-row settings and go to blob formats? A TEXTPTR command run against Varchar(MAX) gets this:

    Msg 8116, Level 16, State 1, Line 1

    Argument data type varchar(max) is invalid for argument 1 of textptr function.

    So you can't actually go to try to do a readtext against the blob, if it pops over 8k.

    From BoL SQL 2k5:

    varchar [ ( n | max ) ]

    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. The SQL-2003 synonyms for varchar are char varying or character varying

    Where's my error here (Besides not using text/ntext/xml)?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 15, 2010 at 5:17 pm

    #1221788

    Code correction, need to cast the replicate or it trims itself as close to 8k as it can:

    create table #tmp

    (tID INT IDENTITY( 1,1),

    TestString VARCHAR(MAX)

    )

    GO

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( CAST( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS VARCHAR(MAX)), 10) + ',', 50)

    )

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( CAST( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS VARCHAR(MAX)), 10) + ',', 75)

    )

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( CAST( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS VARCHAR(MAX)), 10) + ',', 120)

    )

    INSERT INTO #tmp (TestString) VALUES (

    REPLICATE( REPLICATE( CAST( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS VARCHAR(MAX)), 20) + ',', 120)

    )

    GO

    select

    charindex( ',', teststring) AS CommaPos,

    LEN( TestString) AS StrLen,

    len( substring( TestSTring, 9000, 9000)),

    substring( TestSTring, 9000, 9000)

    from#tmp

    Works like a charm now...

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 15, 2010 at 5:26 pm

    #1221790

    Correct me if I'm wrong,please, but I don't see anything that pops over 8K to begin with.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 15, 2010 at 5:36 pm

    #1221791

    Jeff Moden (9/15/2010)

    Correct me if I'm wrong,please, but I don't see anything that pops over 8K to begin with.

    Need to use the second post's code, not the first, might be the difference. Second post returns this (minus the actual substring call):

    CommaPos StrLen substringLen

    -------------------- -------------------- --------------------

    261 13050 4051

    261 19575 9000

    261 31320 9000

    521 62520 9000

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 15, 2010 at 5:39 pm

    #1221793

    Sorry... I'd started to respond before your second post. Ships passing in the night, as it were...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply