SQL 2008 R2 text field issue


When running queries to a table that has a text field when I display the information on the page it won't display it.
I tested and ran the query without including that text field in the query and it displays just fine, clearly this is the root of the problem.

When using sql 2008 r2 server is there another field type I can use to replace the text field that will hold all the information without cutting off what I already have in those fields ?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You should be using varchar(max) instead of text.

Varchar(max) will try to store the characters directly in the table unless you exceed the 8k limit, which will then be stored as a BLOB.  Text is always stored as a BLOB directly.
AleksAuthor Commented:
is there a data type blob ?  or is it ntext ?

I need to decide to which data type to change.
About how many characters is 8k ?

Is there a way to run a query to see which is the longest entry so to make sure nothing is cut off ?
Scott PletcherSenior DBACommented:
Varchar(max) will try to store the characters directly in the table unless you exceed the 8k limit, which will then be stored as a BLOB.

I have to quibble with some technical inaccuracies here.

By default varchar(max) will be stored in-row if it fits, but on a table-by-table basis, you can instruct SQL to always store them off-row if you prefer.

When stored off-row, varchar is stored as a CLOB, not a BLOB.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kyle AbrahamsSenior .Net DeveloperCommented:
Text fields have a max length of ~2GB (https://msdn.microsoft.com/en-us/library/ms187993.aspx)

VarChar maxes when stored off table as a CLOB (I stand corrected) is also the same size.

You can do the following to find out your max length:
select max(len(CAST(<FIELD> as varchar(max))))
from <TABLE>

Open in new window

AleksAuthor Commented:
Thanks. Ill run this tonight against all my databases. Which is the max value that could allow me to change the data type to varchar(max) without losing data ?
Kyle AbrahamsSenior .Net DeveloperCommented:
varchar(max) and TEXT have the same size.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Si I take it I wouldn't lose any data. Not sure that will help my data get displayed. Ill have to test, but before I do that what is the difference between those two ?  is one better than the other ?
AleksAuthor Commented:
I tested and didn't make any difference. It didn't display. Then I changed it to varchar(2500) and my data was displayed. What is the maximum number I can enter in varchar that is not (MAX) ?
Scott PletcherSenior DBACommented:
AleksAuthor Commented:
I noticed is 8000, as you mentioned. Ill check data doesn't go past that. How many characters is varchar (8000) ... is that 8000 characters ?
AleksAuthor Commented:
I answered my own question, thanks to all.
AleksAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.