19

I have this table:

CREATE TABLE Vendors

(
    VendorID            NUMERIC(10)     NOT NULL,
    VendorName          CHAR(50)        NOT NULL,
    VendorAddress       VARCHAR(30)     NULL,
    VendorCityName      VARCHAR(20)     NOT NULL,
    VendorStateName     CHAR(2)         NOT NULL,
    VendorZip           VARCHAR(10)     NULL,
    VendorContactName   CHAR(50)        NOT NULL,
    VendorContactPhone  VARCHAR(12)     NOT NULL,
    VendorContactEmail  VARCHAR(20)     NOT NULL,
    VendorSpecialty     CHAR(20)        NOT NULL

    CONSTRAINT VendorsPK        PRIMARY KEY (VendorID)      
);

And this insert:

INSERT INTO Vendors(VendorID, VendorName, VendorAddress, 
  VendorCityName, VendorStateName, VendorZip, VendorContactName, 
  VendorContactPhone, VendorContactEmail, VendorSpecialty)
VALUES(151330, 'Hyperion', '77 West 66th Street', 'New York', 
  'NY', 10023, 'John Hinks', '212-337-6564', 
  'jhinks@hyperionbooks.com', 'Popular fiction')

Why does this statement yield the 8152 error?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Stephen Fians
  • 311
  • 1
  • 2
  • 5

3 Answers3

34

VendorContactEmail is only 20 bytes. Your e-mail address on the first line (jhinks@hyperionbooks.com) is longer than that - 24 bytes. And many e-mail addresses will be longer. Who decided to only allow 20 characters in the e-mail address column? According to the standard, this should be VARCHAR(320) - 64 characters for <localpart> + 1 for @ + 255 for <domain>.

As for the error message itself, finding the culprit is easier today than it was back then.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Surprised, varchar(n) can't auto extend. Then, what's the point of using varchar(n) over varchar(max)? – Zhang Jun 11 '23 at 05:05
  • 1
    @Zhang Well `max` has quite a bit of performance overhead so it is not wise to just use it for all string values if it is not reasonable to require more than that. [For e-mail address, for example](https://dba.stackexchange.com/a/37021/1186). In this case, allowing only 20 characters for an e-mail address was clearly a design mistake, probably based on one single value like `aaron@msn.com`. For strings in general, see [this](https://dba.stackexchange.com/a/162117/1186) for why blindly oversizing your columns (including max, which has additional problems) is bad. – Aaron Bertrand Jun 11 '23 at 16:21
12

How to Force an Operation that Results in Truncation to Execute Anyway

  • Do you know what truncation is?
  • Do you know what is going to be truncated?
  • Is it your intention to truncate long data to fit?

If and and only if you answer YES to the above questions, you can force your inserts to ignore the warning and run anyway. If you answered no to any of the above, read on.

SET ANSI_WARNINGS  OFF;
-- Your operation TSQL here.
SET ANSI_WARNINGS ON;

(source)

What is Truncation in this Context

Truncation is simply putting as much of a value as will fit into the column and then discarding any portion that doesn't fit. For example, truncating The quick brown fox jumps over the lazy dog to 13 characters would be The quick bro

Why would I receive this error message

You receive this error when you attempt to insert data that won't fit into a destination column definition because the data is too short.

I'm trying to insert many rows of data and want to determine which rows don't fit

Aaron's excellent answer notes that if you are running SQL Server 2019 or newer, you'll actually get a message that contains the column and value that won't fit - which is awesome! But if you aren't running a version that new, read on for tips.

If you receive this error message while attempting to bulk insert many rows of data, you might try splitting the insert into multiple inserts and running them separately to narrow down where the long value is.

Alternatively, you could insert the data into a new temp table and search said temp table for values that won't fit into your destination table.

--(insert into new temp table #Vendors)
INSERT INTO #Vendors(VendorID, VendorName, VendorAddress, 
  VendorCityName, VendorStateName, VendorZip, VendorContactName, 
  VendorContactPhone, VendorContactEmail, VendorSpecialty)
VALUES(151330, 'Hyperion', '77 West 66th Street', 'New York', 
  'NY', 10023, 'John Hinks', '212-337-6564', 
  'jhinks@hyperionbooks.com', 'Popular fiction')

Then query for rows that don't fit.

--(query for values that don't fit)
SELECT *,
LEN(VendorContactEmail) AS Length
FROM #Vendors
WHERE LEN(VendorContactEmail) > 20 --set your destination column length is here

See also LEN and DATALENGTH documentation for information on whitespace handling and binary data lengths.

Jon
  • 9,156
  • 9
  • 56
  • 73
  • While this "works" I don't think it's a good idea to pretend that data loss is just an ignorable warning and you should sweep it under the rug. – Aaron Bertrand Aug 26 '19 at 14:47
  • @AaronBertrand There are cases where once the cause of the warning is investigated, truncating the data is an acceptable resolution. In my case, the column stored VINs which are invalid if they are exactly 17 characters. Truncating an incorrect VIN that is 18 characters didn't leave me any worse off since the VIN was invalid anyway. – Jon Jan 15 '21 at 18:13
  • Data loss might be an acceptable risk to you in your one scenario, but I doubt it's acceptable to the majority of readers. – Aaron Bertrand Jan 15 '21 at 18:19
  • @AaronBertrand that's a fair critique, thank you. I've edited my answer to discourage readers from ignoring this warning and doing something they shouldn't. – Jon Jan 15 '21 at 20:45
1

You can also minimize the length of column values by using following expression: 

LEFT(columnName, 250) + '...'

I tried and it works.

surajs1n
  • 1,493
  • 6
  • 23
  • 34
Athar Ahmed
  • 11
  • 1