Export (0) Print
Expand All

sp_db_selective_xml_index (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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.

System_CAPS_noteNote

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.

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

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

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft