If you are required to search for created and available filegroups in a SQL Server 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 filegroups in a SQL Server database along with their type and read-only status, which of the following SQL queries can you use to do so?
Show answer & explanation
Answer: B. SELECT name, type, is_read_only FROM sys.data_spaces WHERE type = 'FG'
In SQL Server, filegroups and partition schemes are both considered 'data spaces'. The system catalog view 'sys.data_spaces' contains the metadata for both, and filtering by 'type = FG' specifically isolates the filegroups.
Step-by-step Derivation:
Step 1: Identify the requirement: The query must retrieve the name, type, and read-only status of filegroups.
Step 2: Evaluate the system catalog views in SQL Server.
- 'sys.filegroups' does not exist as a standalone system view in standard SQL Server versions (filegroup information is stored in sys.data_spaces).
- 'sys.groups' is not a valid system view for database storage metadata.
- 'sys.sysfiles' is a legacy compatibility view from older versions of SQL Server and does not contain the 'is_read_only' or 'type' columns in the format requested.
Step 3: Analyze 'sys.data_spaces'. This view contains columns: 'name', 'type', and 'is_read_only'.
Step 4: Verify the 'type' column values. In 'sys.data_spaces', 'FG' stands for Filegroup and 'PS' stands for Partition Scheme.
Step 5: Conclusion: The query 'SELECT name, type, is_read_only FROM sys.data_spaces WHERE type = 'FG'' correctly filters for filegroups and retrieves the requested metadata.