If you are required to search for created and available file groups in the current database...
Exl technical mcq question, verified with a worked answer. Free to practise - no sign-up.
If you are required to search for created and available file groups in the current database along with the type and read-only status, then which of the following SQL queries can you use to do so?
Show answer & explanation
In SQL Server, filegroups and partition schemes are both considered 'data spaces'. The sys.data_spaces catalog view is the correct system view that contains the name, type, and read-only status for these entities, where 'FG' specifically filters for filegroups.
Step-by-step Derivation:
Step 1: Identify the requirement: The query must retrieve the name, type, and read-only status of filegroups in the current database.
Step 2: Evaluate the system views in SQL Server.
- sys.filegroups does not exist as a standalone catalog view in standard T-SQL (filegroup information is stored in sys.data_spaces).
- sys.groups is used for security/database roles, not storage.
- sys.sysfile is a legacy system table from very old versions of SQL Server and does not follow the current schema for 'type' and 'is_read_only' columns.
Step 3: Analyze sys.data_spaces. This view contains columns: name, type, and is_read_only. The 'type' column distinguishes between a Filegroup ('FG') and a Partition Scheme ('PS').
Step 4: Construct the query: To isolate only filegroups, the filter 'WHERE type = 'FG'' is applied to sys.data_spaces.
Step 5: Conclusion: Option B is the only syntactically and logically correct query for the specified requirement.