title: SQL Server LEN/DATALENGTH always returning 8000 for text column
body: <p>I'm working in 2008R2 currently, but I've got an audit table, tAudit, that was created in SQL Server 2000, I believe. Among others, tAudit has a text column called Change.  There's over 200k rows in tAudit.  </p>
  
  <p>I need to find the max length of the text contained in the Change column, but LEN and DATALENGTH both report 8000 characters for every row.  A sample of visual inspections suggests that the max length is more likely in the 200 to 300 character range.</p>
  
  <p>Things I've tried:</p>
  
  <p>-- All 236645 rows are 8000. What is going on here?</p>
  
  <pre><code>SELECT max(DATALENGTH(CAST(change AS VARCHAR(MAX)))), COUNT(*) AS MaxLenCount FROM dbo.tAudit
  SELECT max(DATALENGTH(change)), COUNT(*) AS MaxLenCount FROM dbo.tAudit
  SELECT max(LEN(LTRIM(RTRIM(CAST(change AS VARCHAR(MAX)))))), COUNT(*) AS MaxLenCount 
  FROM dbo.tAudit
  
  CREATE TABLE #tAuditTemp 
  (
       TaskID INT
     , AuditDate DATETIME
     , CHANGE varchar(8000) 
  )
  INSERT INTO #tAuditTemp
     SELECT 
          JobID
        , AuditDate
        , Change
     FROM dbo.tAudit 
     ORDER BY 
          AuditDate
        , JobID
        , cast(Change AS VARCHAR(max))
  
  SELECT max(LEN(change)) AS MaxLen FROM #tAuditTemp  -- All are 8000 again; so much for the VAR part of VARCHAR in this case.
  </code></pre>
  
  <p>Specifying anything less than 8000 for Change in the temp table results in a truncation error at runtime.</p>
  
  <p>I've also tried using CHARINDEX to locate the end of the string, but the Change values don't have sufficient commonality in the last few characters of the string that CHARINDEX does me any good.</p>
  
  <p>The table was created with ANSI_PADDING ON. Capturing some Change data from the SSMS row editor shows that trailing blanks have not been stored.  </p>
  
  <p>Questions:<br>
  &nbsp;&nbsp;&nbsp; 1. Any idea why the length of 8000 would be reported for every row?<br>
  &nbsp;&nbsp;&nbsp; 2. Any idea how the actual lengths of the Change data can be extracted?<br></p>