October 14, 2023 at 2:42 am
hi,
Just an observation I was trying to make sense of. I'd appreciate anyone's explanation for why this happens.
I've got a table field with datatype varchar(10). If a value greater than 10 bytes is inserted into this field, an error is generated and the row doesn't get inserted. However, if I have a field that is set to varchar(8000) and an attempt is made to insert a value greater than 8000 bytes, then the value gets truncated, no error gets generated and the row gets inserted.
October 14, 2023 at 6:06 am
hi,
Just an observation I was trying to make sense of. I'd appreciate anyone's explanation for why this happens.
I've got a table field with datatype varchar(10). If a value greater than 10 bytes is inserted into this field, an error is generated and the row doesn't get inserted. However, if I have a field that is set to varchar(8000) and an attempt is made to insert a value greater than 8000 bytes, then the value gets truncated, no error gets generated and the row gets inserted.
First, it would be help if you did 2 things on such posts in the future.
I ran the following demo code in both SQL Server 2017 and 2022. It confirms your finding and I don't know what the cause is.
--===== If it exists, drop the test table to make reruns in SSMS easier.
DROP TABLE IF EXISTS #TestTable;
GO
--===== Create the test table with one column each of the two datatypes cited
-- and an extra.
CREATE TABLE #TestTable
(
VC10 VARCHAR(10)
,VC8000 VARCHAR(8000)
,VC7999 VARCHAR(7999)
)
;
GO
RAISERROR('--===== Test the VARCHAR(10) oversize insert (it fails) =================================',0,0) WITH NOWAIT
DECLARE @LongString VARCHAR(MAX) = REPLICATE('X',20)
;
INSERT INTO #TestTable
(VC10)
SELECT VC10 = @LongString
;
GO
RAISERROR('--===== Test the VARCHAR(8000) oversize insert (it truncates with no failure) ===========',0,0) WITH NOWAIT
DECLARE @LongString VARCHAR(MAX) = REPLICATE('X',9000)
;
INSERT INTO #TestTable
(VC8000)
SELECT VC8000 = @LongString
;
GO
RAISERROR('--===== Test the VARCHAR(7999) oversize insert (it fails) ===============================',0,0) WITH NOWAIT
-- This kind of proves that VARCHAR(8000) is "special"
DECLARE @LongString VARCHAR(MAX) = REPLICATE('X',9000)
;
INSERT INTO #TestTable
(VC7999)
SELECT VC8000 = @LongString
;
And here are the run results that prove robh0502's claim. I have no idea why 8000 doesn't fail. Results are similar in SQL Server 2022 and 2017 except that 2017 doesn't tell you anything about the table involved in the error.
--===== Test the VARCHAR(10) oversize insert (it fails) =================================
Msg 2628, Level 16, State 1, Line 17
String or binary data would be truncated in table 'tempdb.dbo.#TestTable__________________________________________________________________________________________________________000000000018', column 'VC10'. Truncated value: 'XXXXXXXXXX'.
The statement has been terminated.
--===== Test the VARCHAR(8000) oversize insert (it truncates with no failure) ===========
(1 row affected)
--===== Test the VARCHAR(7999) oversize insert (it fails) ===============================
Msg 2628, Level 16, State 1, Line 34
String or binary data would be truncated in table 'tempdb.dbo.#TestTable__________________________________________________________________________________________________________000000000018', column 'VC7999'. Truncated value: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'.
The statement has been terminated.
To confound, the issue, when the following code is executed using variables in either 2022 or 2017...
DECLARE @Varchar10 VARCHAR(10)
,@Varchar8000 VARCHAR(8000)
,@Varchar7999 VARCHAR(7999)
;
SELECT @Varchar10 = REPLICATE('X',20);
SELECT LEN(@Varchar10);
SELECT @Varchar8000 = REPLICATE('X',9000);
SELECT LEN(@Varchar8000);
SELECT @Varchar7999 = REPLICATE('X',9000);
SELECT LEN(@Varchar7999);
GO
... none of the cause errors and all of the truncate properly. It's one of the useful features of variables but why being in a table as above is different for only VARCHAR(8000) is just crazy and it seems that it's been that way for quite a while. That being said, there is no mention of this "feature" in any of the MS Documentation I've seen.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2023 at 6:09 am
hi,
Just an observation I was trying to make sense of. I'd appreciate anyone's explanation for why this happens.
I've got a table field with datatype varchar(10). If a value greater than 10 bytes is inserted into this field, an error is generated and the row doesn't get inserted. However, if I have a field that is set to varchar(8000) and an attempt is made to insert a value greater than 8000 bytes, then the value gets truncated, no error gets generated and the row gets inserted.
First, it would be help if you did 2 things on such posts in the future.
I ran the following demo code in both SQL Server 2017 and 2022. It confirms your finding and I don't know what the cause is.
--===== If it exists, drop the test table to make reruns in SSMS easier.
DROP TABLE IF EXISTS #TestTable;
GO
--===== Create the test table with one column each of the two datatypes cited
-- and an extra.
CREATE TABLE #TestTable
(
VC10 VARCHAR(10)
,VC8000 VARCHAR(8000)
,VC7999 VARCHAR(7999)
)
;
GO
RAISERROR('--===== Test the VARCHAR(10) oversize insert (it fails) =================================',0,0) WITH NOWAIT
DECLARE @LongString VARCHAR(MAX) = REPLICATE('X',20)
;
INSERT INTO #TestTable
(VC10)
SELECT VC10 = @LongString
;
GO
RAISERROR('--===== Test the VARCHAR(8000) oversize insert (it truncates with no failure) ===========',0,0) WITH NOWAIT
DECLARE @LongString VARCHAR(MAX) = REPLICATE('X',9000)
;
INSERT INTO #TestTable
(VC8000)
SELECT VC8000 = @LongString
;
GO
RAISERROR('--===== Test the VARCHAR(7999) oversize insert (it fails) ===============================',0,0) WITH NOWAIT
-- This kind of proves that VARCHAR(8000) is "special"
DECLARE @LongString VARCHAR(MAX) = REPLICATE('X',9000)
;
INSERT INTO #TestTable
(VC7999)
SELECT VC8000 = @LongString
;
And here are the run results that prove robh0502's claim. I have no idea why 8000 doesn't fail. Results are similar in SQL Server 2022 and 2017 except that 2017 doesn't tell you anything about the table involved in the error.
--===== Test the VARCHAR(10) oversize insert (it fails) =================================
Msg 2628, Level 16, State 1, Line 17
String or binary data would be truncated in table 'tempdb.dbo.#TestTable__________________________________________________________________________________________________________000000000018', column 'VC10'. Truncated value: 'XXXXXXXXXX'.
The statement has been terminated.
--===== Test the VARCHAR(8000) oversize insert (it truncates with no failure) ===========
(1 row affected)
--===== Test the VARCHAR(7999) oversize insert (it fails) ===============================
Msg 2628, Level 16, State 1, Line 34
String or binary data would be truncated in table 'tempdb.dbo.#TestTable__________________________________________________________________________________________________________000000000018', column 'VC7999'. Truncated value: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'.
The statement has been terminated.
To confound, the issue, when the following code is executed using variables in either 2022 or 2017...
DECLARE @Varchar10 VARCHAR(10)
,@Varchar8000 VARCHAR(8000)
,@Varchar7999 VARCHAR(7999)
;
SELECT @Varchar10 = REPLICATE('X',20);
SELECT LEN(@Varchar10);
SELECT @Varchar8000 = REPLICATE('X',9000);
SELECT LEN(@Varchar8000);
SELECT @Varchar7999 = REPLICATE('X',9000);
SELECT LEN(@Varchar7999);
GO
... none of the code causes errors and all of the truncate properly. It's one of the useful features of variables but why being in a table as above is different for only VARCHAR(8000) is just crazy and it seems that it's been that way for quite a while. That being said, there is no mention of this "feature" in any of the MS Documentation I've seen.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2023 at 8:07 pm
October 21, 2023 at 6:17 pm
Got it. Thanks for the info. New to SS!
October 22, 2023 at 5:02 pm
Don't quote me on this - but I believe the reason it works for the VARCHAR(8000)/NVARCHAR(4000) columns is due to how SQL Server handles strings, string concatenation and implicit conversions.
For each of the examples - before the attempt to insert the data into the table, SQL Server has to convert the MAX string to an appropriate data type - which will be either VARCHAR(8000) or NVARCHAR(4000) - truncating the string if needed. It can then compare the column length to the string length.
For VARCHAR(8000)/NVARCHAR(4000) it works because the truncated string fits in the column. For all others - the truncated string exceeds the length of the column and it fails.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 23, 2023 at 4:18 am
replicate returns 8k
https://learn.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql?view=sql-server-ver16
Lordy... I've been bitten by that before. In this case, it returns 8K because it wasn't predefined as a MAX datatype.
Let run the test a bit differently.
--===== If it exists, drop the test table to make reruns in SSMS easier.
DROP TABLE IF EXISTS #TestTable;
GO
--===== Create the test table with one column each of the two datatypes cited
-- and an extra.
CREATE TABLE #TestTable
(
VC10 VARCHAR(10)
,VC8000 VARCHAR(8000)
,VC7999 VARCHAR(7999)
)
;
GO
--===== Prove that the @LongString now contains 9,000 characters...
RAISERROR('--===== Prove that the @LongString now contains 9,000 characters... =====================',0,0) WITH NOWAIT
DECLARE @LenLongString INT = LEN(REPLICATE(CONVERT(VARCHAR(MAX),'X'),9000));
RAISERROR('@LenLongString = %u',0,0,@LenLongString) WITH NOWAIT
;
GO
RAISERROR('--===== Test the VARCHAR(10) oversize insert (it fails) =================================',0,0) WITH NOWAIT
DECLARE @LongString VARCHAR(MAX) = REPLICATE(CONVERT(VARCHAR(MAX),'X'),9000)
;
INSERT INTO #TestTable
(VC10)
SELECT VC10 = @LongString
;
GO
RAISERROR('--===== Test the VARCHAR(8000) oversize insert (it fails now) ===========',0,0) WITH NOWAIT
DECLARE @LongString VARCHAR(MAX) = REPLICATE(CONVERT(VARCHAR(MAX),'X'),9000)
;
INSERT INTO #TestTable
(VC8000)
SELECT VC8000 = @LongString
;
GO
RAISERROR('--===== Test the VARCHAR(7999) oversize insert (it fails) ===============================',0,0) WITH NOWAIT
-- This kind of proves that VARCHAR(8000) is "special"
DECLARE @LongString VARCHAR(MAX) = REPLICATE(CONVERT(VARCHAR(MAX),'X'),9000)
;
INSERT INTO #TestTable
(VC7999)
SELECT VC8000 = @LongString
;
Now, all the code fails... including the VARCHAR(8000) code. So... no mystery now. Our tests were incorrect for the reason that Steve pointed out.
--===== Prove that the @LongString now contains 9,000 characters... =====================
@LenLongString = 9000
--===== Test the VARCHAR(10) oversize insert (it fails) =================================
Msg 8152, Level 16, State 31, Line 44
String or binary data would be truncated.
The statement has been terminated.
--===== Test the VARCHAR(8000) oversize insert (it fails now) ===========
Msg 8152, Level 16, State 31, Line 52
String or binary data would be truncated.
The statement has been terminated.
--===== Test the VARCHAR(7999) oversize insert (it fails) ===============================
Msg 8152, Level 16, State 31, Line 61
String or binary data would be truncated.
The statement has been terminated.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2023 at 7:00 pm
So - going back to the OP's original post we see that a string that is greater than 8000 characters gets inserted with truncation and is successful for columns defined as VARCHAR(8000).
As shown in the fixed sample code - the error will occur when you actually try to insert a string that is larger than the defined column length.
Therefore, it must be some operation prior to the insert that is truncating the data - and there are many ways that could happen.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 23, 2023 at 8:29 pm
So - going back to the OP's original post we see that a string that is greater than 8000 characters gets inserted with truncation and is successful for columns defined as VARCHAR(8000).
As shown in the fixed sample code - the error will occur when you actually try to insert a string that is larger than the defined column length.
Therefore, it must be some operation prior to the insert that is truncating the data - and there are many ways that could happen.
I agree. We don't actually know what the op is doing nor what any of the datatypes are. He'd have to post some code (Both DDL and DML).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy