dynamic data types

  • Hi guys.

    as a newbie in sql world i have a small question.

    i need to make a new table in our DB and one of the columns suppose to hold a string with various length (the goal is to save , for example, a txt file that is length is unknown).

    is there a dynamic data type to support saving long strings?

    i don't want to define nvarchar(MAX) because :

    A.the string can be between 300 chars to 30,000 chars (it's unknown)

    B.i don't even know if nvarchar(MAX) can support this length.

    thanks in advance

    Amir

  • Straight from BOL (BooksOnLine, the SQL Server help system):

    Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

    The same applies for unicode data (NVARCHAR(MAX)), except the limit to exceed is 4000 instead of 8000.

    Your reasoning for not using NVARCHAR(MAX) is a little strange: if the length of your data type and exceed 4000, NVARCHAR(MAX) is exactly the data type you're looking for (VARCHAR(MAX) might be an option, too, depending on the character set used in your txt files: if there's a chance of having unicode data in there, use NVARCHAR and if you can guarantee there will never be any unicode data, use VARCHAR).

    In order to find the max size NVARCHAR(MAX) can: google is your friend if you don't have BOL available offline 😉

    Again, straight from BOL

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

    I think 2Gb will be enough to hold 30k character...

    Another option might be to just sore ther file name and path as a reference instead of the content of the file. It depends on the given situation.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • thx for the reply.

    wasn't familiar with the BOL option.

    🙂

  • Is this unicode or ASCII data? If ASCII, use VARCHAR(MAX), asn NVARCHAR(MAX) is for unicode and takes 2 bytes per character

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • dardar4 (7/31/2011)


    thx for the reply.

    wasn't familiar with the BOL option.

    🙂

    Just in case you don't know it, "BOL" is short for "Books Online" and it's the help-system that comes with (or you can download for in later versions like 2k8). I don't know how anyone can live without it.

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

  • dardar4 (7/31/2011)


    Hi guys.

    as a newbie in sql world i have a small question.

    i need to make a new table in our DB and one of the columns suppose to hold a string with various length (the goal is to save , for example, a txt file that is length is unknown).

    is there a dynamic data type to support saving long strings?

    i don't want to define nvarchar(MAX) because :

    A.the string can be between 300 chars to 30,000 chars (it's unknown)

    B.i don't even know if nvarchar(MAX) can support this length.

    thanks in advance

    Amir

    Just to be clear, NVARCHAR can go up to 4000 or it can go up to MAX. MAX is 2 BILLION [font="Arial Black"]bytes [/font]whether it be VARCHAR or NVARCHAR. Of course, since NVARCHAR uses two bytes per character, NVARCHAR can only go up to a BILLION [font="Arial Black"]characters[/font] whereas VARCHAR will allow 2 Billion [font="Arial Black"]characters[/font]..

    --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 6 posts - 1 through 6 (of 6 total)

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