Varchar vs text
-
Are there any reasons why you should use text over varchar for columns where the maximum length is, 8000 say? I'm aware of the difficulties when performing comparisons against and updating text columns and the fact that using sp_spaceused shows that a table with a text column uses about twice as much space as an equivalent table containing the same data in a column of type varchar(8000).
Should you only use the text data type in situations where you need to store more than 8000 characters?
Thanks in advance for any help.
Sean
-
Should you only use the text data type in situations where you need to store more than 8000 characters?
Yes. You should always use the most fitting datatype for any data you want to store.
-
Hmm. What about performance? Say your data is always close to 8000 chars and you access it very seldomly. Would it not make sense to make it a text (not in-row) to improve table scans (where the column is not returned?
-
Thanks for both of your comments. I've realised that the varchar option has a big impact on the row size in that if a column does actually contain a record with 8000 chars it is dangerously close to the maximum 8060 bytes at which updates will fail. Text seems a better option in this case.
Because it is likely that the column will need to be queried fairly regularly, I've changed the design and reduced the varchar size to keep it the row size below this limit.
-
In that case I would probably put the varchar column in a separate table.
-
Yeah, I guess you are right. Though the idea might be interesting to consider. An aditional table would be perfect if you could rely 100% on the SQL Servers ability to use vertically partitioned views/tables. Otherwise it might introduce some extra work you might not want to bother with. Or for example if you do not want to dirty your data model
-
Also do not forget thw maximum size of all columns of a row is 8060 bytes.
So if you have a column which is declared as a varchar(8000) and if you are filling all of your 8000 bytes then the sum of the size of all remaining columns must not exeed 60 bytes.
If you use nchar, nvarchar, then the max size is nvarchar(4000).
While by using text datatype the size is not limited (or better said it is limited to 2 GB per row).
By using text you can fit more records into one page (8K) therefore you can increase the performance
Bye
Gabor -
'Increase performance'... Yes, if you do not actually access the text that the 16 byte pointers are pointing to. That actually degrades your performance...
-
The name of the Game in this case is USAGE. How is it going to be used?
There are many problems associated with data manipulation of text fields the complexity that you may introduce by keeping a separate table with a varchar(8000) are easyly erased by the complexity introduced in manipulating (Updating / Apending / searching within ) text columns. If on the contrary is extremely seldom modified or the contents are rarely parsed then it may be the only case that I would use the text type
Just MY opinion.
* Noel -
Oh, and if my answer still does not convince you have a look at some of the difficuties that this guy had to undergo
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=194787
just to perform something very simple to accomplish with varchar columns
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply