July 31, 2011 at 12:29 am
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
July 31, 2011 at 2:46 am
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.
July 31, 2011 at 3:19 am
thx for the reply.
wasn't familiar with the BOL option.
July 31, 2011 at 4:14 am
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
July 31, 2011 at 7:31 pm
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
Change is inevitable... Change for the better is not.
July 31, 2011 at 7:36 pm
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy