difference between dynamic management views and catalog views

Question

what is the difference between dynamic management views and catalog views ? How they are different from system views ? And what is Information Schema Views ?

Answer 1

The catalog views are views over the system tables and thus present static data which only changes through DDL statements like CREATE TABLE etc. The system tables themselves are not directly accessible. A query against a catalog view, is not very different from a query against a user table. That is, data is read from disk, unless it's already in cache.

The Dynamic Management Views on the other hand are essentially function calls into the engine to return data in a tabular format. Some of the DMVs may include disk access, but usually not to read tables, but to perform some other action, like determining the level of fragmentation.

The INFORMATION_SCHEMA views are views for metadata defined by the ANSI standard, and queries on these views will be portable to other platforms. The INFORMATION_SCHEMA views are implemented on top of the catalog views.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thanks..I understood what Catalog Views and Information_Schema Views are from your reply. But still confused on the DMVs.

Arent these views and point to some tables ? How are these function calls ? I thought DMF are the functions and DMV are the views, am I incorrect ? I thougt DMV should eventually query against some table to get the data.

In summary, What is the original source of DMV ?

Thanks again.

Answer 3

Here is one example. If you do "sp_helptext 'sys.dm_exec_query_stats', you will see this:

CREATE VIEW sys.dm_exec_query_stats AS
 SELECT *
 FROM OpenRowset (TABLE QUERY_STATS)  

But there is no physical table on disk called QUERY_STATS. It would be far to expensive to maintain such a table. The data exists only in main memory in SQL Server and probably in some non-relational format. The RowSet provider returns the data, so it can be consumed from SQL.

---

Erland Sommarskog, SQL Server MVP, [e-mail]