Post reply

Is there a way to overcome the limitation of 8k per record in a table?

  • February 7, 2008 at 4:56 pm

    #203162

    We have a very wide table that exceeds 8k size for each row. This is not a BLOB object, but simply a very large number of wide columns. Is there a work around this limitation ?

    Thanks

  • February 7, 2008 at 4:59 pm

    #776218

    To the best of my knowledge. NO. Are they variable columns datatypes.?

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • February 7, 2008 at 6:33 pm

    #776244

    Refactor

    Split the rows up into multiple tables with a link.

  • February 7, 2008 at 8:26 pm

    #776267

    VARCHAR(MAX)

    --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)

  • February 8, 2008 at 8:16 am

    #776445

    Sugesh: most of the columns are fixed width, however some of them are varchar.

    Pam: this is what I am also thinking about, I thought I would find a better solution.

    Jeff: In my tests I tried varchar(max), however SQL Server inserts only first 8000 chars of them . Here is my test:

    create table

    wide_table

    (id int identity,

    length int,

    string varchar(max)

    )

    go

    declare

    @length int,

    @string varchar(max)

    set @length = 9000

    select @string = replicate('a' , @length)

    insert into wide_table

    (length, string)

    values (@length, @string)

    select id, length , actual_length=len(string)

    from wide_table

    The result set is:

    id length actual_length

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

    2 9000 8000

  • February 8, 2008 at 2:33 pm

    #776621

    Vertical partion on the table is the only real way. Works just fine.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 8, 2008 at 3:12 pm

    #776633

    Varchar(max) works fine ... from BOL for replicate:

    If character_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, character_expression must be explicitly cast to the appropriate large-value data type.

    Because you passed 'a' to replicate, it truncated the return value.

Viewing 7 posts - 1 through 6 (of 6 total)

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