Export (0) Print
Expand All

sys.dm_db_objects_impacted_on_version_change (Azure SQL Database)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

This database-scoped system view is designed to provide an early warning system to determine objects that will be impacted by a major release upgrade in Azure SQL Database. You can use the view either before or after the upgrade to get a full enumeration of impacted objects. You will need to query this view in each database to get a full accounting across the entire server.

Applies to: Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Column name

Data Type

Description

class

int NOT NULL

The class of the object which will be impacted:

1 = constraint

7 = Indexes and heaps

class_desc

nvarchar(60) NOT NULL

Description of the class:

OBJECT_OR_COLUMN

INDEX

major_id

int NOT NULL

object id of the constraint, or object id of table that contains index or heap.

minor_id

int NULL

NULL for constraints

Index_id for indexes and heaps

dependency

nvarchar(60) NOT NULL

Description of dependency that is causing a constraint or index to be impacted. The same value is also used for warnings generated during upgrade.

Examples:

space (for intrinsic)

geometry (for system UDT)

geography::Parse (for system UDT method)

Requires the VIEW DATABASE STATE permission.

The following example shows a query on sys.dm_db_objects_impacted_on_version_change to find the objects impacted by an upgrade to the next major server version

SELECT * FROM sys.dm_db_objects_disabled_on_version_change;
GO
class  class_desc        major_id    minor_id    dependency                     
------ ----------------- ----------- ----------- ---------- 
1      OBJECT_OR_COLUMN  181575685   NULL        geometry                      
7      INDEX             37575172    1           geometry                      
7      INDEX             2121058592  1           geometry                      
1      OBJECT_OR_COLUMN  101575400   NULL        geometry   

The following ordered steps describe the corrective action to take after the upcoming June service release upgrade.

Order

Impacted Object

Corrective Action

1

Indexes

Rebuild any index identified by sys.dm_db_objects_impacted_on_version_change For example: ALTER INDEX ALL ON <table> REBUILD

or

ALTER TABLE <table> REBUILD

2

Object

All constraints identified by sys.dm_db_objects_impacted_on_version_change must be revalidated after the geometry and geography data in the underlying table is recomputed. For constraints, revalidate using ALTER TABLE.

For example:
ALTER TABLE <tab> WITH CHECK CHECK CONSTRAINT <constraint name>

or

ALTER TABLE <tab> WITH CHECK CONSTRAINT ALL

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft