August 16, 2007 at 5:33 am
hey
we will have many tables about 4-5 like this
id(int) name(varcahr(30)) data (varchar(max))
with lot of data inserted in the data field
so what i am asking is what are the disadvantges and drawback of such tables, performance, disk space.....
what happens when the table become big and lot of data inserted in the varchar(max) field?
August 16, 2007 at 7:08 am
The performance you get will depend on how much data you're putting into the max fields, your database settings, and what operations you're doing with these fields. I was apprehensive about using varchar(max) fields at first, but so far I haven't noticed undue performance problems in their use, at least in the system I work with. If your sp_tableoption setting for 'large value types out of row' is set to 0 then the first 8000 chars will be stored in row like a varchar(8000), and only characters over that will be stored in Large Object storage space. If the option is set to 1, the row will only contain a pointer, and all of the data will be in LOB space. The large values I work with don't get touched very often after initial storage, so data page splitting isn't really a factor after that. If you're doing a lot of updates on these types of fields, it's probably going to be more of a performance hit.
August 16, 2007 at 7:20 am
August 17, 2007 at 12:49 am
Hi
adding my 2 bits to this...
I have been using varchar(max) for some time now. I have not realy seen any disadvantage(s).
"Keep Trying"
August 17, 2007 at 5:40 am
August 17, 2007 at 6:41 am
"Text" and "Image" data types do not act the same as other data types. They are stored quite differently, you cannt use them in a group by, they do not appear in triggers, etc.
If you are aware of the differences, they work fine. They also give you some other interesting update options and if used correctly, you can update parts of the fields instead of the entire field all of the time.
The only thing I have run into with VARCHAR(MAX) is in dealing with these in conversions to other systems. They are clearly not VARCHAR, so everything seems to recognize them the same way "Text" is seen.
August 17, 2007 at 7:41 am
Hey D Smith
I notice you mentioned that using sp_tableoption settings either stored the data or a pointer. I have two questions regarding this.
1. Using a pointer would be faster if accessing it alot?
2. If the answer to question 1 is yes would using this as an index be a good idea?
Sorry if this is a stupid question only new to this and still learning the nitty gritty. Currently Indexes are what i am trying to learn.
August 17, 2007 at 12:13 pm
I have no hard evidence of it, but I think that a pointer would be faster for access only if the field size is routinely over 8000 characters. Otherwise, you'll incur the extra time for a lookup in the LOB space without really needing to.
I don't know if indexing on the pointer would be advantageous or not. None of our varchar(max) fields are used in indexes, and I've not read any accounts of performance differences between indexing with the data in-row as opposed to by pointer. That might be an interesting thing to test.
I don't think it's a stupid question. I've only been using SQL Server 2005 since November of last year after a contract gig converting SQL Server 7 and 2000 apps to Oracle 10g, so I'm still coming up to speed on a lot of the new features myself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply