Skip to content

Latest commit

 

History

History
38 lines (30 loc) · 2.6 KB

sql-on-premises-vs-azure-similar-sys-views-include.md

File metadata and controls

38 lines (30 loc) · 2.6 KB
author ms.author ms.date ms.service ms.topic
rwestMSFT
randolphwest
10/22/2023
azure-sql-database
include

Some Transact-SQL code examples written for SQL Server need small changes to run in Azure. One category of such code examples involves catalog views whose name prefixes differ depending on the database engine type:

  • server_ - prefix for SQL Server and Azure SQL Managed Instance
  • database_ - prefix for Azure SQL Database and SQL Managed Instance

Azure SQL Database supports only database-scoped event sessions. SQL Server Management Studio (SSMS) fully supports database-scoped event sessions for Azure SQL Database: an Extended Events node containing database-scoped sessions appears under each database in Object Explorer.

Azure SQL Managed Instance supports both database-scoped sessions and server-scoped sessions. SSMS fully supports server-scoped sessions for SQL Managed Instance: an Extended Events node containing all server-scoped sessions appears under the Management folder for each managed instance in Object Explorer.

Note

Server-scoped sessions are recommended for managed instances. Database-scoped sessions aren't displayed in Object Explorer in SSMS for Azure SQL Managed Instance. Database-scoped sessions can only be queried and managed with Transact-SQL when using a managed instance.

For illustration, the following table lists and compares two subsets of catalog views. For brevity, the subsets are restricted to view names that also contain the string _event. The subsets have differing name prefixes because they support different database engine types.

Name in SQL Server and Azure SQL Managed Instance Name in Azure SQL Database and Azure SQL Managed Instance
server_event_notifications
server_event_session_actions
server_event_session_events
server_event_session_fields
server_event_session_targets
server_event_sessions
server_events
server_trigger_events		 database_event_session_actions
database_event_session_events
database_event_session_fields
database_event_session_targets
database_event_sessions

The two lists in the preceding table were accurate as of March 2022. For an up-to-date list, run the following Transact-SQL SELECT statement:

SELECT name
    FROM sys.all_objects
    WHERE
        (name LIKE 'database[_]%' OR
         name LIKE 'server[_]%' )
        AND name LIKE '%[_]event%'
        AND type = 'V'
        AND SCHEMA_NAME(schema_id) = 'sys'
    ORDER BY name;