Can SQL Server be configured to not silently truncate VARCHAR values?

by Alexei   Last Updated January 11, 2018 10:06 AM

In some scenarios like this one, SQL Server will silently truncate (N)VARCHAR values leading to serious data loss.

When data is inserted/update using an ORM, the definition usually incorporates a "max length" annotation/attribute that prevents this. However, when running in other contexts this is a serious issue?

Question: Can SQL Server be configured to not silently truncate VARCHAR values? (and issue an error/raise an exception instead)



Answers 1


The Database Engine has some built-in defaults for handling data types.

The definition of the data types char and varchar is as follows:

char [ ( n ) ] Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

varchar [ ( n | max ) ] Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are charvarying or charactervarying.

For char and varchar the defaults are as follows:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

If you don't define a length, then SQL Server will do it for you.

Reference

hot2use
hot2use
January 11, 2018 09:51 AM

Related Questions


SQL Server purchasing options in AWS

Updated June 05, 2017 13:06 PM


Visual Studio SSIS Package throws error

Updated September 18, 2017 13:06 PM

SQL Server Performance Issues & Reporting Requirements

Updated October 18, 2017 11:06 AM

SSISDB Catalog in sql server

Updated June 03, 2016 08:02 AM