Export (0) Print
Expand All

sp_db_selective_xml_index (Transact-SQL)

Enables and disables Selective XML Index functionality on a SQL Server database. If called without any parameters, the stored procedure returns 1 if the Selective XML Index is enabled on a particular database.

Note Note

In order to disable the Selective XML Index using this stored procedure, the database must be put in simple recovery mode by using the ALTER DATABASE SET Options (Transact-SQL) command.

Applies to: SQL Server (SQL Server 2012 through current version).

Topic link icon Transact-SQL Syntax Conventions

sys.sp_db_selective_xml_index
[[ @db_name = ] 'db_name'], 
[[ @action = ] 'action']

[ @ db_name = ] 'db_name'

The name of the database to enable or disable Selective XML Index on. If db_name is NULL, the current database is assumed.

[ @action = ] 'action'

Determines whether to enable or disable the index. If another value except 'on', ‘true’, ‘off’, or ‘false’ is passed, an error will be raised.

Allowed values: 'on', 'off', 'true', 'false'

1 if the Selective XML Index is enabled on a particular database.

A. Enable Selective XML Index functionality

The following example enables Selective XML Index on the current database.

EXECUTE sys.sp_db_selective_xml_index
    @db_name = NULL
  , @action = N'on';
GO

The following example enables Selective XML Index on the AdventureWorks2012 database.

EXECUTE sys.sp_db_selective_xml_index
    @db_name = N'AdventureWorks2012'
  , @action = N'true';
GO

B. Disable Selective XML Index functionality

The following example disables Selective XML Index on the current database.

EXECUTE sys.sp_db_selective_xml_index
    @db_name = NULL
  , @action = N'off';
GO

The following example disables Selective XML Index on the AdventureWorks2012 database.

EXECUTE sys.sp_db_selective_xml_index
    @db_name = N'AdventureWorks2012'
  , @action = N'false';
GO

C. Detect if Selective XML Index is enabled

The following example detects if Selective XML Index is enabled. Returns 1 if Selective XML Index is enabled.

EXECUTE sys.sp_db_selective_xml_index;
GO

Community Additions

ADD
Show:
© 2014 Microsoft