ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
In addition, SET option NUMERIC_ROUNDABORT must be OFF.
By default, SQL Server 2000's client tools set all these options except ARITHABORT to the correct values, making it easier for you to work with indexed views. To be safe, you can use sp_configure to set a bit in the user options value to set ARITHABORT ON. The following code finds the current value of user options by looking in the syscurconfigs table, then uses the bitwise OR operator to turn on the appropriate bit in addition to any other bits you've previously set:
use master
declare @value int
select @value = value from syscurconfigs
where config = 1534
set @value = @value | 64
exec sp_configure 'user options', @value
reconfigure
Note that the options must be set to these values when you create the indexed view, when you modify any tables included in the indexed view, and when the optimizer decides to use the indexed view as part of a query plan. You can check which options are set for a particular connection by executing the query DBCC USEROPTIONS. You can also use the new property function SESSIONPROPERTY to test whether you have each option appropriately set (1 equals ON, 0 equals OFF):
SELECT SessionProperty('NUMERIC_ROUNDABORT')
You also need to keep a few other special requirements in mind when creating a view. First, all functions and expressions in the view definition must be deterministic. In general, any function that can return for the same arguments two different results in two separate invocations is nondeterministic. Two obvious examples of nondeterministic functions are getdate( ) and rand( ). Most parameterless system functions, such as @@spid, @@servername, and @@rowcount, are also nondeterministic. You might be surprised that datename( ) is nondeterministic; the values it returns depend on the language you've configured SQL Server to use with sp_configure. The language can change from user to user—even on the same server in the same database—so that one user can get error messages in English, for example, and another can get messages in Spanish. Datepart( ) is nondeterministic when the first argument is DW (day of week) because DW can vary based on the DATEFIRST setting. (The final version of SQL Server Books Online—BOL—for SQL Server 2000 will list which functions are and aren't deterministic.) In addition, any view that contains a column, constant, or expression of data type float in its definition is nondeterministic.
The second requirement to be aware of when creating a view is that you don't want any underlying object's schema definition to change. To prevent a change in schema definition, SQL Server 2000's CREATE VIEW statement allows the SCHEMABINDING option. When you specify with SCHEMABINDING, the SELECT statement defining the view must include the two-part names (owner.object) of all referenced tables. You can't drop or alter tables participating in a view created with the SCHEMABINDING clause unless you've dropped that view or changed the view so that it no longer has SCHEMABINDING. Otherwise, SQL Server raises an error. If someone else owns a table on which you're basing a view, you don't automatically have the right to create the view with SCHEMABINDING, which would restrict the table's owner from making changes to her own table. The table owner must grant the other user REFERENCES permission for that user to create a view with SCHEMABINDING.
Prev. page
1
[2]
3
4
5
next page 
