SQL Server silently truncates varchar’s in stored procedures

by

It just is.

I’ve never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I’d expect SQL to never see data that is too long. If I did see truncated data, it’d be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you’ll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Edit:

There is an open Microsoft Connect item regarding this issue.

And it’s probably worthy of inclusion in Erland Sommarkog’s Strict settings (and matching Connect item).

Edit 2, after Martins comment:

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);
SELECT @sql="B", @nsql="B"; 
SELECT 
   LEN(@sql), 
   LEN(@nsql), 
   DATALENGTH(@sql), 
   DATALENGTH(@nsql)
;

DECLARE @t table(c varchar(8000));
INSERT INTO @t values (replicate('A', 7500));

SELECT LEN(c) from @t;
SELECT 
   LEN(@sql + c), 
   LEN(@nsql + c), 
   DATALENGTH(@sql + c), 
   DATALENGTH(@nsql + c) 
FROM @t;

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. How to SELECT FROM stored procedure
  3. Function vs. Stored Procedure in SQL Server
  4. SQL Server – How to lock a table until a stored procedure finishes
  5. Stored procedure or function expects parameter which is not supplied
  6. What is a stored procedure?
  7. How to assign an exec result to a sql variable?
  8. Search text in stored procedure in SQL Server
  9. SQL Call Stored Procedure for each Row without using a cursor
  10. SQL Server SELECT into existing table
  11. How do I execute a stored procedure once for each row returned by query?
  12. How to check if a stored procedure exists before creating it
  13. Execute Stored Procedure from a Function
  14. SQL : in clause in stored procedure:how to pass values
  15. How to return the output of stored procedure into a variable in sql server
  16. Split values over multiple rows [duplicate]
  17. How do I pass a list as a parameter in a stored procedure?
  18. Must declare the scalar variable
  19. Why execute stored procedures is faster than SQL query from a script?
  20. Stored procedure that exports data into csv files only exports to one file
  21. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  22. Insert Update stored proc on SQL Server
  23. SQL Server: should I use information_schema tables over sys tables?
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. UNION the results of multiple stored procedures
  26. How to pass a temp table as a parameter into a separate stored procedure
  27. Eliminate and reduce overlapping date ranges
  28. How to execute a stored procedure inside a select query
  29. Dynamic order direction
  30. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?

Leave a Comment