Why does SQL Server select query truncate varchar string?

Question

Setup

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

A simplified table looks like:

CacheID INT NOT NULL
CacheData VARCHAR(MAX)

I occasionally insert several large values. 10,000+ characters. The insert works fine. A simple query tells me all the data is in the 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 select:

SELECT
  CacheData
FROM Caches
WHERE CacheID = 10

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

The question

Why is the data being Truncated?

The question in another form. I actually ran into this agian and forgot about this solution. Took me a bit to remember as I forgot the root cause. Here is what I searched for thinking that SQL Server was the culprit.

What is SQL Servers Varchar(MAX) maximum length? - If your values are being truncated its probably caused by php non sql server. If you just want to know what the max is, the question is answered here: Maximum size of a varchar(max) variable

Answer 1

Disclosure

Answering my own question. Just posting it here because it took me over an hour to hunt this issue down and it would be nice to have a strait-forward answer to it. I've seen similar questions on stack overflow but they were very specific to a use case or so poorly worded they were not findable by search results

Answer (mssql direct)

mssql module for php has several default settings in the php.ini file. One of those settings is mssql.textlimit and mssql.textsize. These settings will truncate any text based field (varchar, text, nvarchar) to the size they are set too.

I have seen posts where the truncate is on 8192 and 4096. So I assume either of these can be default. I upped mine to 65535 (2^16 - 1).

Answer (mssql through pdo)

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

PDO seems to have a bug (not a bug but kind of a bug) that hard-codes the textlimit to 64 kb. You can override this by passing the following query right before your long query: SET TEXTSIZE -1.

Other related issues

As a side note I have also seen similar question specifically for query results in SQL Server Management Studio. There are several limitations in the program for how many characters can be displayed or exported. Also there are settings in the program to edit the number of characters displayed/exported. More info on this issue here.