October 19, 2018 at 5:28 am
Hello, I am creating a table which has a persisted computed HashByte column. After the table is created when I try to insert data from CSV to that table it complains: INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Some of the columns in CSV has data more than 8000 length so before inserting data in table I have Set ANSI_WARNINGS OFF so that it does not complain about data truncation. This was working perfectly until there was a requirement to include computed column.
So the question is How can I keep persisted computed column and at same time Set ANSI_WARNINGS OFF property enabled?
Create table dbo.demo( [UniqueID] [bigint] IDENTITY(1,1) NOT NULL, Name varchar(50), Description varchar(max), -- data truncation issue for this column HashValue as Hashbytes ('MD5' , CONCAT ('|', Name,Description))PERSISTED )
insert into [dbo].[demo] (Name,Description) values ('John','<characters more than 8000>')
October 19, 2018 at 5:36 am
Why not fix the truncation problem instead? That would seem the better idea.
(Also, duplicate thread on Stack Overflow: https://stackoverflow.com/questions/52891568/set-options-issue-on-sql-server-2014 )
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 19, 2018 at 5:41 am
Thom A - Friday, October 19, 2018 5:35 AMWhy not fix the truncation problem instead? That would seem the better idea.(Also, duplicate thread on Stack Overflow: https://stackoverflow.com/questions/52891568/set-options-issue-on-sql-server-2014 )
I don't want to truncate data. While loading data from CSV to table I want the same data and so while creating the table I have kept column type as varchar(max)
October 19, 2018 at 5:46 am
So why are you using SET ANSI_WARNINGS OFF when there is no truncation? Notice Jeroen has said the same thing in SO that I said here; fix the truncation issue. If you're saying there isn't any truncation, then don't use the setting.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 19, 2018 at 6:04 am
Thom A - Friday, October 19, 2018 5:46 AMSo why are you using SET ANSI_WARNINGS OFF when there is no truncation? Notice Jeroen has said the same thing in SO that I said here; fix the truncation issue. If you're saying there isn't any truncation, then don't use the setting.
I have to set ANSI Warnings off because there is data in one of the column name Description in my csv which has data more than 8000. Please check my post I have added the code.
October 19, 2018 at 8:12 am
Thom A - Friday, October 19, 2018 6:35 AMOk, the problem is HASHBYTES. The maximum length, on SQL Server 2014, is 8000 characters. You can't use a varchar(MAX) with HASHBYTES on SQl Server 2014-.
Spot on! But then is there any other alternative for HashBytes which won't have this 8000 character limit issue. I know there is checksum but it again has its own limitations If at least one of the values in the expression list changes, the list checksum will probably change. However, this is not guaranteed. Therefore, to detect whether values have changed, we recommend use of CHECKSUM only if your application can tolerate an occasional missed change. Otherwise, consider using HashBytes instead. With a specified MD5 hash algorithm, the probability that HashBytes will return the same result, for two different inputs, is much lower compared to CHECKSUM.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply