View column has an 8000 character limit?

Question

In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 characters, even though the update contains a greater number of characters.

Is there some kind of limit on the number of characters that a view can receive for an update?

Ok I'm working with the application development team on this and they think they found a way around it so they discarded the code they were using as their test case. I'll do my best to give the general idea. The update statement looked something like:

UPDATE vorders
set [description] = '<br> This is some text about the order </br>' 
    + CHAR(34) + 'This is more text' + CHAR(34) + 'and on it goes'
where orderid = 12345

We would then check the length of the characters put into the field by running the following query

SELECT LEN([description])
  FROM vorders
 WHERE orderid = 12345

What the application guys found is that if they remove the + CHAR(34) + part of the code the length would go up to 8700 characters. With the + CHAR(34) + part of the code in it was truncating the text down to 8000 characters. This made me suspect that it was some kind of implicit conversion; however, we found that the truncation did not occur with one or two of the + CHAR(34) + in place. The original statment had something like 30 of them.

Any idea what might be causing this cause I'm at a loss on this one.

Answer 1

I suspect you are simply not checking the length of the column accurately (like inspecting PRINT or SELECT output in Management Studio, which is truncated by the tool, not accurately reflecting what's actually in the database). There is no limitation on the number of characters that can be exposed or updated by a view, unless you introduce them, perhaps you are using an INSTEAD OF trigger, or there is implicit conversion going on somewhere, or your data is being truncated before the update. Hard to tell without more details, which I hope you can furnish.

In the meantime, try this:

USE tempdb;
GO

CREATE TABLE dbo.foobar(x VARCHAR(MAX));
GO

CREATE VIEW dbo.v_foobar 
WITH SCHEMABINDING
AS
  SELECT x FROM dbo.foobar;
GO

INSERT dbo.v_foobar(x) VALUES('c');
GO

UPDATE dbo.v_foobar 
  SET x = REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000)
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000);
GO

SELECT LEN(x), DATALENGTH(x)
  FROM dbo.v_foobar;
GO

Results:

-----    -----
16000    16000

I'm also not convinced that CHAR(34) is the culprit.

UPDATE dbo.v_foobar 
  SET x = CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x' 
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x' 
        + REPLICATE(CHAR(34), 400)
        + 'x' + CHAR(34)
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000);

GO
SELECT LEN(x), DATALENGTH(x)
  FROM dbo.v_foobar;

Results:

-----    -----
17226    17226

I suspect that something else is happening to this string as it's being generated or before the command is passed to SQL Server.

Answer 2

There is a display limit in the SQL Server mgmt studio, that might be what's troubling you. You'll have to check for these large fields through a different application. There are also some addons to SSMS that you might be able to use. This one might help you http://www.ssmsboost.com/