SET NUMERIC_ROUNDABORT script

Transact-SQL
1

Hey,

To enable the use of indexed views in a number of databases, I had to set NUMERIC_ROUNDABORT to OFF. I have used this kind of script for all the databases.

USE databaseNameA;
SET NUMERIC_ROUNDABORT OFF;
USE databaseNameB;
SET NUMERIC_ROUNDABORT OFF;
...

Yet, when I checked this setting in the Properties window in SSMS, NUMERIC_ROUNDABORT was still set to "True" in all the databases.

I have the impression that SET NUMERIC_ROUNDABORT is only effective in the session that script was run.

Is there a way to set this option in a script (as opposed to do it manually in the Properties window in SSMS)? That way I can include it in the script that generates the scripts to create databases (in the generated scripts, the correct name and location of the .mdb and .ldb files are generated, a login and user are defined, ...)

As a side node. I don't understand what NUMERIC_ROUNDABORT has to do with indexed views. There must be an underlying reason, but what is it?

2

Have you read this?

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-numeric-roundabort-transact-sql?view=sql-server-2017

3

yosiasz, thank you for your reply.

I have read the article.
As far as I can tell, it doesn't provide an answer to my question(s).

4

The default db NUMERIC_ROUNDABORT setting is completely separate from the session setting.

You can get the NUMERIC_ROUNDABORT setting for the current session like this:
SELECT @@OPTIONS & 8192
You can set it for the current session as you did above:
SET NUMERIC_ROUNDABORT OFF;

The db setting is the default for all connections to that db, unless the connection sets it differently. If you want to change that setting, you'd need to use ALTER DATABASE.

5

Hi Scott, thank you for your reply.

I have tried the command

alter database databaseNameA
	SET numeric_roundabort OFF

It works!. Thank you very much.