MS SQL Server - 37 - System Catalog MCQ

37 – System Catalog MCQ

Best of luck!

1 / 25

1. What are Catalog Views in SQL Server used for?

Querying the system database

Querying database objects and metadata

Querying data in user tables

Querying user-created databases

2 / 25

2. Which system database in SQL Server contains catalog views?

msdb

tempdb

model

master

3 / 25

3. What is the primary purpose of Dynamic Management Views (DMVs) and Functions in SQL Server?

To query system metadata and monitor server activity

To create user-defined functions

To define foreign key relationships

To store user data

4 / 25

4. Which of the following is a common use case for Dynamic Management Views (DMVs) and Functions in SQL Server?

Querying user tables

Storing application data

Monitoring server performance

Creating indexes

5 / 25

5. Which system view provides information about the columns in user tables, views, and system catalog views in SQL Server?

sys.tables

sys.views

sys.objects

sys.columns

6 / 25

6. What is the purpose of the INFORMATION_SCHEMA schema in SQL Server?

To store user data

To define foreign key relationships

To create user-defined functions

To query system metadata

7 / 25

7. Which system view provides information about the indexes on tables and views in SQL Server?

sys.views

sys.objects

sys.indexes

sys.tables

8 / 25

8. What is the primary difference between Catalog Views and Dynamic Management Views (DMVs) in SQL Server?

Catalog Views are accessible only to system administrators, while DMVs are accessible to all users.

Catalog Views provide real-time monitoring, while DMVs provide static information.

Catalog Views are used for creating user-defined functions, while DMVs are used for creating user tables.

Catalog Views store user data, while DMVs store system metadata.

9 / 25

9. Which Dynamic Management View (DMV) in SQL Server provides information about currently executing queries and their resource usage?

sys.dm_exec_sessions

sys.dm_exec_connections

sys.dm_exec_queries

sys.dm_exec_requests

10 / 25

10. In SQL Server, what type of information can you retrieve from the sys.dm_os_wait_stats DMV?

Information about database objects

Information about user-defined functions

Information about user tables and views

Information about active server waits and their statistics

11 / 25

11. Which system view in SQL Server provides information about all user-defined tables, views, and procedures in a database?

sys.objects

sys.tables

sys.procedures

sys.schemas

12 / 25

12. Which Dynamic Management View (DMV) in SQL Server provides information about query execution plans and statistics?

sys.dm_exec_sql_text

sys.dm_exec_query_stats

sys.dm_exec_requests

sys.dm_exec_sessions

13 / 25

13. What is the primary use of the sys.dm_os_performance_counters DMV in SQL Server?

To retrieve data from user tables

To monitor server performance counters

To create user-defined functions

To define foreign key relationships

14 / 25

14. In SQL Server, which Dynamic Management View (DMV) provides information about the active connections to the server?

sys.dm_exec_requests

sys.dm_exec_sql_text

sys.dm_exec_connections

sys.dm_exec_sessions

15 / 25

15. What is the primary purpose of the sys.dm_db_index_physical_stats DMV in SQL Server?

To create user-defined functions

To monitor server performance counters

To retrieve storage information for partitions

To retrieve data from user tables

16 / 25

16. Which Dynamic Management Function (DMF) in SQL Server is used to split a delimited string into multiple rows?

STRING_SPLIT

STRING_TO_TABLE

STRING_EXTRACT

STRING_PARSE

17 / 25

17. In SQL Server, which Dynamic Management View (DMV) provides information about currently executing T-SQL batches?

sys.dm_exec_requests

sys.dm_exec_sessions

sys.dm_exec_connections

18 / 25

18. What is the primary purpose of the sys.dm_db_partition_stats DMV in SQL Server?

To monitor server performance counters

To create user-defined functions

To retrieve data from user tables

To retrieve storage information for partitions

19 / 25

19. Which system view in SQL Server provides information about the columns in user tables but not the system catalog views?

sys.objects

sys.views

sys.columns

sys.tables

20 / 25

20. In SQL Server, which Dynamic Management View (DMV) provides information about the current state of locks on resources?

sys.dm_exec_sessions

sys.dm_tran_locks

sys.dm_exec_connections

sys.dm_exec_requests

21 / 25

21. What is the primary purpose of the sys.dm_exec_query_plan DMV in SQL Server?

To retrieve data from user tables

To create user-defined functions

To retrieve execution plans for queries

To monitor server performance counters

22 / 25

22. In SQL Server, which Dynamic Management View (DMV) provides information about the wait statistics for different types of waits?

sys.dm_os_wait_stats

sys.dm_exec_sessions

sys.dm_exec_requests

sys.dm_exec_connections

23 / 25

23. What is the primary use of the INFORMATION_SCHEMA.COLUMNS view in SQL Server?

To monitor server performance counters

To retrieve information about columns in user tables

To create user-defined functions

To retrieve data from user tables

24 / 25

24. In SQL Server, which Dynamic Management Function (DMF) can be used to split a string into multiple columns based on a delimiter?

STRING_SPLIT

STRING_TO_TABLE

STRING_EXTRACT

STRING_PARSE

25 / 25

25. Which of the following SQL statements can be used to retrieve information from the INFORMATION_SCHEMA.COLUMNS view in SQL Server?

SELECT * FROM sys.columns

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

SELECT * FROM sys.dm_exec_sessions

SELECT * FROM sys.tables

Your score is

MS SQL Server – 37 – System Catalog MCQ