Home > IT Answers > SQL Server > varchar(max) size limit
Niranjan56
35 pts.
 varchar(max) size limit
SQL Server, VARCHAR
I have defined a table field with datatype of varchar(max) but it only stores upto 8k characters, I thought (max) upped this limit to 2g character. How can I get it to store more than 8k characters?



Software/Hardware used:
Windows NT/2000
ASKED: February 11, 2010  4:49 PM
UPDATED: February 12, 2010  5:03 PM
RELATED QUESTIONS
Answer Wiki:
varchar(max) stores up to 2 Gigs of data per row per column. The SQL Server Management Studio will only show 8k by default. When you look at the value in your application it will show all the data (unless the application is designed to only show 8k characters).
Last Wiki Answer Submitted:  February 11, 2010  8:31 pm  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Depends on what database version you are using:

SQL Server 2000 can only store up to 8000 characters in a VARCHAR field. I don’t believe it supports the MAX option.

SQL Server 2005, 2008 still only supports 8000 characters for VARCHAR, but thet also adds the MAX option which will allow you to store 2.1M.

Gent01
 1,855 pts.
 

I have SQL Server 2005 and I have defined the table field to be varchar(max) but it’s not accepting anything more than 8000.

Niranjan56
 35 pts.
 

ok – so 2 Gb of data is 2^31 characters, way WAY over 8000 characters – right?
so why do I get “[Microsoft][ODBC SQL Server Driver]String data, right truncation” error when I try to insert any thing more than 8000 characters?

Niranjan56
 35 pts.
 

Well, you didn’t mention that you were getting an error, nor did you mention that you were using ODBC.

I think the ODBC driver could be causing the error.

Are there some other details you might want to provide ??

carlosdl
 63,535 pts.
 

good point Carlos – and apologies to everyone.
here is what my ODBC configuration looks like:
Microsoft SQL Server ODBC Driver Version 03.85.1117

Data Source Name: LMS Dev
Data Source Description:
Server: WS2K3032SQL
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

The SQL Server is running on some Intel machine, VB ver is 6.5,

Niranjan56
 35 pts.
 

Try to use SS Native Client as ODBC driver.

msi77
 1,610 pts.