I know information schema views are part of SQL standard, but I find catalog views as well as DMV "term" used interchangeably
any explanation that clearly tell us difference particularly difference between catalog views and DMV?
DMV's are views, DMF's are functions. Dynamic Management Views and Functions were introduced in SQL 2005 and allow you to look at information that is stored in the Query Cache, query plans, CLR Data, Extended Events, Resource Governor info, etc.
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Dynamic management views and functions return internal, implementation-specific state data.
Catalog views return information that is used by the SQL Server Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.
INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.
Catalog views represent views over some hidden tables. They return data from the database itself (from disk).
DMVs represent views over internal functions. They return data from internal SQL structures (from memory). DMV names always start with sys.dm_
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With