December 1, 2005 at 2:50 am
When I create a new VARCHAR or TEXT column, how much space does SQL Server reserve?
Is the advantage of specifying a max length e.g. VARCHAR(50) only that the max length cannot exceed 50 bytes?
What is the difference between e.g. VARCHAR(500) and TEXT(500) and if none, what is the purpose of VARCHAR?
I am trying to work out how to specify the string fields in a new database to match best practice.
Can anyone help with any advice?
Doug
December 1, 2005 at 4:10 am
out of my head:
varchar: easily searchable but limited to 8000 characters (4000 for nvarchar)
text: allows more but is not so searchable because it isn't stored in the "record" itself, just a pointer
See the books online for more information about character data (character data types)
Character data consists of any combination of letters, symbols, and numeric characters. For example, valid character data includes "928", "Johnson", and "(0*&(%B99nh jkJ". In Microsoft® SQL Server™ 2000, character data is stored using the char, varchar, and text data types. Use varchar when the entries in a column vary in the number of characters they contain, but the length of any entry does not exceeds 8 kilobytes (KB). Use char when every entry for a column has the same fixed length (up to 8 KB). Columns of text data can be used to store ASCII characters longer than 8 KB. For example, because HTML documents are all ASCII characters and usually longer than 8 KB, they can be stored in text columns in SQL Server prior to being viewed in a browser.
It is recommended that the defined length of a character column be no larger than the maximum expected length of the character data to be stored.
To store international character data in SQL Server, use the nchar, nvarchar, and ntext data types.
December 1, 2005 at 7:13 pm
Thanks for that info. On your second point, I have read what I can find in Books Online, but I didn't find it very helpful.
What does SQL Server actually do when you specify a VARCHAR field? Does it pre-extend that field in every record to the length you specify, or is it truly variable length and depends in each record on how much data is entered? If you don't specify the length, does it allow any length, but all have to be the same so that any time anyone enters something longer than has been entered before, all previous records have to be lengthened?
Say I have a field into which someone might enter 5000 characters, but I know that only one in 10,000 people will do so and the vast majority will enter nothing at all, am I better off with VARCHAR, VARCHAR(5000) or TEXT?
Since there are a lot of these types of fields I have to specify in this new database, I would like to understand exacly what things I should consider in my decision on which data type to choose.
Thanks again for any help.
Doug
December 2, 2005 at 2:38 am
Varchar - requires 2 bytes + the amount of characters entered per record. The two bytes is used to store the actual length.
If a field is defined VACHAR(5000) and I store the string 'This is a test' in it, it takes up 16 bytes. 14 for the string, 2 to store the length. This is all stored in-page, ie with the rest of the row.
Text - requires 16 bytes to store the pointer to the data, plus the amount of characters stored. The 16 byte pointer is stored in-page with the rest of the record, the text data is stored out-of page (usually, but see the Text in Row option)
I would personally stay away from Text fields if possible. They behave differently from other data types and can trip you up if you're not careful
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
December 2, 2005 at 3:21 am
That's very helpful. Thanks so much.
Doug
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply