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 8:00 am
This was removed by the editor as SPAM
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply