Menu
Here is my predicament.
Basically, I need a column in a table to hold up an unknown length of characters. But I was curious if in Sql Server performance problems could arise using a VARCHAR(MAX) or NVARCHAR(MAX) in a column, such as: 'This time' I only need to store 3 characters and most of the time I only need to store 10 characters. But there is a small chances that It could be up to a couple thousand characters in that column, or even possibly a million, It is unpredictable. But, I can guarantee that it will not go over the 2GB limit.
I was just curious if there are any performance issues, or possibly better ways of solving this problem where available.
738
We can use the varchar(max) column as an included column in the index, but you cannot perform the index seek on this column. It will also require additional storage. Therefore, you should avoid creating an index with the varchar(max) data type.
In addition, varchar(max) prevents the ability to perform online indexes against the entire table which contains the varchar(max) field. This will significantly impact performance of your system.
In addition to the size and performance considerations of setting the size of a varchar (and possibly more important, as storage and processing get cheaper every second), the disadvantage of using varchar(255) "just because" is reduced data integrity.
A varchar will only use as much space as inputted as opposed to a char which will pad with white space. Traffic size on a varchar column, therefore, is considerably smaller than a char column. Show activity on this post. Correct, it will not make any difference.
Sounds to me like you plan to use the varchar(MAX) data type for its intended purpose.
When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types.
For further reading, check out Books Online: char and varchar
196
You cannot create indexes on varchar(max) ( and nvarchar(max)) columns (although they can be included in them. But who would include a column in an index that could get to 2GB?!) so if you want to search on this value, you will do a scan each time unless you use full-text indexes. Also, remember that any report designer or presentation designer (web or otherwise) must assume that someone might put the Encyclopedia into that column and design around it. Nothing is worse than hearing "the users probably won't do X". If a user can do it, they will do it. If a user can put in a tome into a column, at some point they will. If they never should, then IMO, it makes more sense to cap the column size at some reasonable level and if a user tries to stuff more into that column that is allowed, it would elicit a discussion of whether they should be entering that value into that column in the first place.
20
I just saw this article the other day. It documents a fairly minor performance lag for varchar(max) over a varchar(n) column. Probably not enough to make a difference for you. But if it does, perhaps you can use a separate table to store those few large text blocks. Your small text could stay in the main table, but you could add a flag field to tell you to look in the new table for the big ones.
4
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
