Solved

SQLServer and VARCHAR(MAX) vs VARCHAR(8192)

Posted on 2011-04-28
9
913 Views
Last Modified: 2012-08-14
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.

0
Comment
Question by:MonCapitan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 35482275
If you are sure that data will not exceed 8000 then you can go for varchar otherwise you have to go with VARCHAR(MAX) .

With VARCHAR(MAX)  there will be performance issues in terms of  maintaining and reading the data.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35482295
if you can assume that at some point, the "block size" will be larger than the 8K, go with VARCHAR(MAX).
the performance issue mentioned above would only apply to those rows where the data actually goes over 8K
0
 

Author Comment

by:MonCapitan
ID: 35482414
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?
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35482473
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" ...
0
 

Author Comment

by:MonCapitan
ID: 35482884
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35482922
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

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35483018
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.
0
 

Author Comment

by:MonCapitan
ID: 35483033
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35483069
unless you prove with tests to use VARCHAR(8000), I would go with the varchar(max) method.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question