MonCapitan
asked on
SQLServer and VARCHAR(MAX) vs VARCHAR(8192)
Hi.
I am designing a database schema and I have question about VARCHAR. I have a field called ContentBody and it holds the content for a particular block on a page and the page is built from one or many of these blocks.
I don't know if the content editor of the site is going to insert more than 8192 bytes of data or less than 8192 bytes. (I am assuming at this point that VARCHAR(X) and VARCHAR(MAX) are my two options).
If I have VARCHAR(8192), a user will have to break up the content into many blocks, if the block is 20000 bytes say, which is not ideal which leaves me with VARCHAR(MAX). Some of the content might only be 100 bytes though and some maybe 20000 bytes or more.
With such a wide difference in number of bytes in this ContentBody field, what will I be doing to the database performance and size etc?
I hope this question makes sense and thank you for your time with this.
I am designing a database schema and I have question about VARCHAR. I have a field called ContentBody and it holds the content for a particular block on a page and the page is built from one or many of these blocks.
I don't know if the content editor of the site is going to insert more than 8192 bytes of data or less than 8192 bytes. (I am assuming at this point that VARCHAR(X) and VARCHAR(MAX) are my two options).
If I have VARCHAR(8192), a user will have to break up the content into many blocks, if the block is 20000 bytes say, which is not ideal which leaves me with VARCHAR(MAX). Some of the content might only be 100 bytes though and some maybe 20000 bytes or more.
With such a wide difference in number of bytes in this ContentBody field, what will I be doing to the database performance and size etc?
I hope this question makes sense and thank you for your time with this.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the replies.
How much of a performance difference. I mean, that is what VARCHAR(MAX) is designed for isn't it to hold lots of content?
How much of a performance difference. I mean, that is what VARCHAR(MAX) is designed for isn't it to hold lots of content?
VARCHAR(MAX) is designed to combine the flexibility of the "small varchar(x)" and the capacity of the TEXT (2GB) data type.
yes, there is performance issue when you have to store/read/manipulate large data.
there is no performance issue for rows where the column data is "small" ...
yes, there is performance issue when you have to store/read/manipulate large data.
there is no performance issue for rows where the column data is "small" ...
ASKER
Thanks again angellll. Sorry for bashing this one out a bit but in my scenario, there will be only around 20-30 rows returned with each request that have the VARCHAR(MAX) field. Would that be truly significant on so negligible I shouldn't worry about it?
the only true answer will be: you have to test it out.
note that if you have to split up the rows into several rows, you will hence need to time the full process of reassembling the "splitted" rows
note that if you have to split up the rows into several rows, you will hence need to time the full process of reassembling the "splitted" rows
Varchar(X) - no of length you mentioned
Varchar(MAx) : 64000
Moreover, varchar occupy only those much of space as per the length of string. no as per the defined.
Varchar(MAx) : 64000
Moreover, varchar occupy only those much of space as per the length of string. no as per the defined.
ASKER
Which won't take much overhead surely as I will be retrieving the rows, loading them into an object via a .NET DataReader and then reassembling them so most of the work is done at the application level.rather on the database server.
I also believe that 75% of the Rows will have less than 8000 bytes in the VARCHAR(MAX) field but of course, 25% will be over 8000.
Is this reasoning sound to you?
I also believe that 75% of the Rows will have less than 8000 bytes in the VARCHAR(MAX) field but of course, 25% will be over 8000.
Is this reasoning sound to you?
unless you prove with tests to use VARCHAR(8000), I would go with the varchar(max) method.
With VARCHAR(MAX) there will be performance issues in terms of maintaining and reading the data.