Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server difference between catalog views, information schema views vs DMVs

Tags:

sql

sql-server

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?

like image 698
cshah Avatar asked Nov 16 '10 14:11

cshah


People also ask

What is the difference between DMV and DMF in SQL Server?

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.

What are DMVs in SQL Server?

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.

What is catalog views in SQL Server?

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.

What is information schema in SQL?

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.


1 Answers

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

like image 151
Remus Rusanu Avatar answered Oct 05 '22 05:10

Remus Rusanu