This topic has not yet been rated - Rate this topic

sp_db_selective_xml_index (Transact-SQL)

SQL Server 2012

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
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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

Transact-SQL

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

Arguments

[ @ 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.
[ @ selective_xml_index = ] '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'

Return Code Values

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

Examples

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
  , @selective_xml_index = 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'
  , @selective_xml_index = 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
  , @selective_xml_index = 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'
  , @selective_xml_index = 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

Concepts

Selective XML Indexes (SXI)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD