I am trying to have an auto-computed column to tell me the difference in hours between the 2 dates.
It will not let me do it because the database has ANSI_WARNINGS = OFF.
I know the best practice is to set ANSI_WARNINGS ON and handle the issues instead of trying to hide them, however, I am not the owner of the database, so I need to work with what I can.
I also know that if I remove the "persisted" keyword, it works, but I need to remove the overhead it generates when queries are made.
Is there any way I can adjust my computed column to allow its creation?
Here is my SQL statement:
ALTER TABLE TABLE_NAME
ADD DATE_DIFFERENCE
AS ROUND( (DATEDIFF(minute, DATETIME1, DATETIME2) / 60.00), 2) PERSISTED
I am getting this error:
ALTER TABLE 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
I am trying to have an auto-computed column to tell me the difference in hours between the 2 dates.
It will not let me do it because the database has ANSI_WARNINGS = OFF.
I know the best practice is to set ANSI_WARNINGS ON and handle the issues instead of trying to hide them, however, I am not the owner of the database, so I need to work with what I can.
I also know that if I remove the "persisted" keyword, it works, but I need to remove the overhead it generates when queries are made.
Is there any way I can adjust my computed column to allow its creation?
Here is my SQL statement:
ALTER TABLE TABLE_NAME
ADD DATE_DIFFERENCE
AS ROUND( (DATEDIFF(minute, DATETIME1, DATETIME2) / 60.00), 2) PERSISTED
I am getting this error:
ALTER TABLE 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
Short answer "NO" - the documentation is clear ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views.
If you're not the owner of the database and can't change the ANSI_WARNINGS settings, then you can't do this - so you'll need to think of other ways to accomplish this. You could investigate e.g. triggers on the table to just simply set the value of a "normal" (not computed) column, or you could possibly have a scheduled job that would update that column on a regular basis (e.g. every hour) - this depends on your needs which you haven't specified in great detail - so I can't really recommend anything
ANSI_WARNINGSsettings, then you can't do this - so you'll need to think of other ways to accomplish this. You could investigate e.g. triggers on the table to just simply set the value of a "normal" (not computed) column, or you could possibly have a scheduled job that would update that column on a regular basis (e.g. every hour) - this depends on your needs which you haven't specified in great detail - so I can't really recommend anything .... – marc_s Commented Jan 7 at 4:56set ansi_warnings onbefore executing the above. Not sure persisted will help though, unless you create index, and usually its not needed either – siggemannen Commented Jan 7 at 7:23OFFreferring to a view with itON). Computed columns etc don't allow this though. But you should always have itONanyway. You always have permission to set itONin your own session, and it will persist in certain objects that you are allowed to create.. – Charlieface Commented Jan 7 at 9:29persistedas in the computed column persisted part of the syntax. – siggemannen Commented Jan 7 at 9:32