Why does SQL Server select the truncate varchar query string?

Question

Customization

I use several memory tables to cache data for my site. This is a local web host on my mac and dev server on linux using the php 5.5 mssql library

A simplified table looks like this:

CacheID INT NOT NULL CacheData VARCHAR(MAX) 

Sometimes I insert a few large values. 10,000+ characters. The insert works fine. A simple request tells me that all data is in a field:

 SELECT CacheID, LEN(CacheData) FROM Caches 

Problem

However, when I actually select the data, the cache column is always truncated to 8192 characters, causing problems. Simple choice:

 SELECT CacheData FROM Caches WHERE CacheID = 10 

I checked the character limit of varchar (max). And it is far beyond 8192. 2^32 - 1

Question

Why is data truncated?

The question is in a different form. I actually came across this Hagian and forgot about this decision. I recalled a little when I forgot the root cause. Here is what I was looking for, thinking that SQL Server was the culprit.

What is the maximum length of a Varchar (MAX) SQL Server? . If your values ​​are truncated, it is probably caused by the php non sql server. If you just want to know what max is, this question will be answered: The maximum size of the variable varchar (max)

Answer 1

disclosure

Answering my own question. Just posting it here because it took me more than an hour to track down this question, and it would be nice to get a direct answer to it. I saw similar questions about the stack overflow, but they were very specific for the use case or poorly formulated, they could not be found by the search results

Answer (direct mssql)

The mssql module for php has several default settings in the php.ini file. One such parameter is mssql.textlimit and mssql.textsize . These settings truncate any text field (varchar, text, nvarchar) to the size that they also specify.

I saw messages where the truncation is on 8192 and 4096. Therefore, I assume that any of them may be the default. I raised my to 65535 (2 ^ 16 - 1).

Answer (via mssql via pdo)

I recently started migrating to PDO and found that the direct mssql answer does not work with pdo.

There seems to be an error in PDO ( not an error, but a kind of error ) that hardcodes the text limit to 64 KB. You can override this by passing the following query immediately before a long query: SET TEXTSIZE -1 .

Other related issues

As a note, I also saw a similar question specifically for query results in SQL Server Management Studio. The program has several restrictions on the number of displayed or exported characters. The program also has settings for editing the number of displayed / exported characters. More information on this subject here .