October 9, 2008 at 9:58 am
UDBNT (10/8/2008)
I do appreciate the feedback and varied views from the floor.
"Floor"? You obviously have me confused with someone else. I sit in the peanut gallery. 😀
October 10, 2008 at 7:54 am
To me it doesn't make sense to be using VARCHAR(MAX) except for items that you know won't fit into a regular VARCHAR() or are large values that won't be queried often. If you need that column frequently in your queries, that's going to require the database to gather the column data for each record from multiple pages since VARCHAR(MAX) is stored on separate pages like the old TEXT datatype. That could end up hurting your I/O performance.
If the column isn't queried as frequently as the rest of the columns in the table, and it tends to hold large values then I wouldn't worry about using VARCHAR(MAX).
October 12, 2008 at 5:16 pm
Ditto what Chris said. Varchar(max) is going to hurt your I/O. Go there only if you KNOW your data is going to exceed 8000 characters. For that matter, set upper limits on varchar fields. Keep the garbage out.
October 12, 2008 at 9:01 pm
bobhovious (10/12/2008)
Ditto what Chris said. Varchar(max) is going to hurt your I/O. Go there only if you KNOW your data is going to exceed 8000 characters. For that matter, set upper limits on varchar fields. Keep the garbage out.
I'm all for sizing things appropriately. That being said - I was under the impression that Varchar(max) acts just like the "traditional" varchar(8000) until the rowsize got exceeded (>8060). Meaning - it gets treated like a "normal" variable character column until it gets past 8000 when it starts acting a bit more like a TEXT column (except that it doesn't seem to have a lot of the drawbacks of the TEXT datatype).
Just curious how the (max) is going to hurt IO.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 13, 2008 at 2:56 pm
Ugh, OK I think it depends on the setting of large value types out of row for the table, and digging into it deeper, it looks like the default is to store it in-row up to the 8000 limit.
October 14, 2008 at 1:02 am
I did a noddy test on a VARCHAR(MAX) v VARCHAR(nn) for space.
Using only data <100 characters for a sample number of rows, the number of pages for VARCHAR(MAX) and VARCHAR(100) was the same.
As I was implying before even if on these examples, the result is the same, it still doesn't feel right to me and if you want to index the column (in the future), you are FUBAR-ed.
At the end of the day it undoubtly is vendors being very lazy and trying to blind side the customers.
October 15, 2008 at 4:47 pm
I stand corrected on the behavior of varchar(max) and thank you for the education 🙂
However, it still seems to me that it is a good idea to limit varchar columns to a specific number of characters unless you are expecting over 8000 characters from time to time. Someone already mentioned the issue of indexing, but also I want to see an error message if someone tries to force more data into a column than is anticipated. Something is most definitely amiss if the column is meant to hold a street address and the value to be stored is 700 characters long.
One final thing to remember is that varchar(N) columns do not allow you to exceed the 8k page size. It is possible to define a row with 50 varchar(200) columns, but if the data to be stored ever exceeds page size, you're going to get an error on the insert or update.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply