Use SESSIONPROPERTY To Check If NUMERIC_ROUNDABORT Is On Or Off

You want to create an indexed view; however creating an indexed view is not as straight forward as creating an index on a table. The view must be created with the SCHEMABINDING option.

The following SET options must be set to ON when the CREATE INDEX statement is executed:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

The NUMERIC_ROUNDABORT option must be set to OFF.

The view must not reference any other views, only base tables. There are more restrictions, you can look them up by reading Creating an Indexed View in Books On Line.

So how do you find out if NUMERIC_ROUNDABORT is set to ON or OFF? Since we are using SQL server 2005 we can use the sys.dm_exec_sessions view

SELECT *
FROM sys.dm_exec_sessions
WHERE session_id = @@spid

But wait there is no column named numeric_roundabort. Okay then let's use the old SQL server 2000 DBCC USEROPTIONS
After executing DBCC USEROPTIONS this is the output for me


DBCC USEROPTIONS
-----------------------------
textsize 64512
language us_english
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed



Still no luck. But there is hope because SQL Server 2005 offers the SESSIONPROPERTY function

Let's do a quick test

SET NUMERIC_ROUNDABORT ON
SELECT SESSIONPROPERTY('numeric_roundabort') --1

SET NUMERIC_ROUNDABORT OFF
SELECT SESSIONPROPERTY('numeric_roundabort') --0


Now let's create our own view with all the set options needed for creating indexed views included

CREATE VIEW UserOptionsExpanded
AS
SELECT session_id,concat_null_yields_null,
quoted_identifier,
ansi_nulls,
ansi_padding,
ansi_warnings,
arithabort,SESSIONPROPERTY('numeric_roundabort') AS numeric_roundabort
FROM sys.dm_exec_sessions

Let's select from the view

SELECT *
FROM UserOptionsExpanded
WHERE session_id = @@spid


And yes all the columns are there

Of course we could have just executed the following and be done ;-)

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

Let's see what else we can pass into the SESSIONPROPERTY function

ANSI_NULLS
Specifies whether the SQL-92 compliant behavior of equals (=) and not equal to (<>) against null values is applied.
1 = ON
0 = OFF

ANSI_PADDING
Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in character and binary data.
1 = ON
0 = OFF

ANSI_WARNINGS
Specifies whether the SQL-92 standard behavior of raising error messages or warnings for certain conditions, including divide-by-zero and arithmetic overflow, is applied.
1 = ON
0 = OFF

ARITHABORT
Determines whether a query is ended when an overflow or a divide-by-zero error occurs during query execution.
1 = ON
0 = OFF

CONCAT_NULL_YIELDS_ NULL
Controls whether concatenation results are treated as null or empty string values.
1 = ON
0 = OFF

NUMERIC_ROUNDABORT
Specifies whether error messages and warnings are generated when rounding in an expression causes a loss of precision.
1 = ON
0 = OFF

QUOTED_IDENTIFIER
Specifies whether SQL-92 rules about how to use quotation marks to delimit identifiers and literal strings are to be followed.
1 = ON
0 = OFF

[Any other string]
NULL = Input is not valid.

So it looks like SESSIONPROPERTY takes only the SET options required to create an indexed view. Interesting indeed.