MCQs on Views | SQL Server
Master SQL Server Views: Comprehensive MCQs designed to enhance your understanding of creating and managing views, working with indexed views, ensuring security with views, and modifying data through them.
Creating and Managing Views
- What is a view in SQL Server?
- A) A temporary table stored in memory
- B) A virtual table based on a SELECT query
- C) A physical copy of table data
- D) A stored procedure
- How can a view be created in SQL Server?
- A) Using the CREATE TABLE statement
- B) Using the CREATE VIEW statement
- C) Using the INSERT INTO statement
- D) Using the ALTER VIEW statement
- What is the primary benefit of using views?
- A) Increased query execution speed
- B) Simplified access to complex queries
- C) Permanent storage of calculated data
- D) Elimination of duplicate data
- Which of the following statements can be included in a view definition?
- A) SELECT
- B) INSERT
- C) DELETE
- D) UPDATE
- Can views include data from multiple tables?
- A) Yes, by using JOINs in the SELECT statement
- B) No, views are limited to one table
- C) Only when UNION is used
- D) Only in indexed views
- What happens when you drop a base table that a view depends on?
- A) The view becomes invalid.
- B) The view is automatically deleted.
- C) The view starts returning NULL values.
- D) The view continues to work without any issues.
- Which statement is true regarding renaming a view?
- A) A view can be renamed using the RENAME keyword.
- B) A view cannot be renamed once created.
- C) A view must be dropped and recreated to rename it.
- D) The sp_rename system procedure can be used to rename it.
- Can a view be created with the WITH SCHEMABINDING option?
- A) No, views cannot use this option.
- B) Yes, but it restricts modifications to underlying tables.
- C) Yes, but only for indexed views.
- D) Yes, and it ensures automatic indexing.
Indexed Views
- What is an indexed view in SQL Server?
- A) A view that supports sorting automatically
- B) A view with a unique clustered index
- C) A view stored in memory for faster access
- D) A read-only view
- What is a requirement for creating an indexed view?
- A) The view must include the WITH ENCRYPTION option.
- B) The view must use the WITH SCHEMABINDING option.
- C) The view must be created using a JOIN.
- D) The view must include only one column.
- What is the primary benefit of indexed views?
- A) Faster query execution due to precomputed results
- B) Reduced storage usage
- C) Increased security for sensitive data
- D) Simplified data modification
- Which statement is true about updating an indexed view?
- A) Indexed views cannot be updated directly.
- B) Updates to base tables automatically update indexed views.
- C) Indexed views require manual updates.
- D) Indexed views can be updated using triggers only.
- How do indexed views impact performance in write-heavy workloads?
- A) They improve performance.
- B) They decrease performance due to update overhead.
- C) They have no effect on performance.
- D) They only affect SELECT statements.
- Can an indexed view include aggregate functions?
- A) Yes, but only with GROUP BY.
- B) No, aggregate functions are not allowed.
- C) Yes, without restrictions.
- D) Yes, but only in read-only views.
- How do indexed views handle data modifications in base tables?
- A) Changes are automatically reflected in the indexed view.
- B) Changes are ignored unless the view is refreshed.
- C) Indexed views become invalid until manually updated.
- D) Indexed views do not allow data modifications.
Security Considerations with Views
- How do views enhance security in SQL Server?
- A) By encrypting all data in base tables
- B) By providing access to specific data without exposing the entire table
- C) By enforcing mandatory password protection
- D) By restricting SQL injection attacks
- Can a user execute a view without permissions on the underlying tables?
- A) Yes, if the user has SELECT permissions on the view.
- B) No, the user must have permissions on all base tables.
- C) Yes, but only for indexed views.
- D) No, unless WITH ENCRYPTION is used.
- Which option encrypts the definition of a view?
- A) WITH SCHEMABINDING
- B) WITH ENCRYPTION
- C) WITH VIEW_METADATA
- D) WITH CHECK OPTION
- What happens if a user attempts to modify a view created with the WITH ENCRYPTION option?
- A) The user is allowed to modify the view.
- B) The user must decrypt the view first.
- C) The modification fails due to encrypted definition.
- D) The modification automatically decrypts the view.
- How does the WITH CHECK OPTION clause improve view security?
- A) It encrypts the view definition.
- B) It prevents data changes that violate the view’s WHERE clause.
- C) It blocks unauthorized users from executing the view.
- D) It ensures the view definition cannot be altered.
Modifying Data through Views
- Can data be inserted through a SQL Server view?
- A) Yes, if the view is not complex.
- B) No, views are read-only.
- C) Yes, but only for indexed views.
- D) No, unless triggers are used.
- Which condition allows updates through a view?
- A) The view is based on a single table without calculated columns.
- B) The view includes JOIN statements.
- C) The view uses aggregate functions.
- D) The view has an index.
- What happens when you modify a base table through a view?
- A) The view becomes invalid.
- B) The view reflects the changes automatically.
- C) The view must be refreshed manually.
- D) The modification fails.
- Which views do not allow data modifications?
- A) Views with WHERE clauses
- B) Views with aggregate functions or GROUP BY clauses
- C) Views with no primary key
- D) All views allow modifications
- How does the INSTEAD OF trigger enhance data modification through views?
- A) It prevents any modifications.
- B) It allows custom logic for handling data changes through views.
- C) It increases the performance of the view.
- D) It encrypts the view.
- Can you delete rows through a view in SQL Server?
- A) Yes, if the view references a single table.
- B) No, deletion is not allowed.
- C) Yes, but only with indexed views.
- D) No, unless the base table has an index.
- What happens when you attempt to update a calculated column in a view?
- A) The update succeeds.
- B) The update fails with an error.
- C) The update affects the underlying base table.
- D) The update is ignored.
- Can you insert data into all columns of a base table through a view?
- A) Yes, without restrictions.
- B) No, only visible columns in the view can be inserted into.
- C) No, views do not allow inserts.
- D) Yes, but only with indexed views.
- What is the purpose of the WITH CHECK OPTION when modifying data through a view?
- A) Ensures data modifications comply with the view’s WHERE clause.
- B) Prevents modifications to the base table.
- C) Enables encryption of the view.
- D) Allows calculated columns to be updated.
- Can a view modify multiple base tables simultaneously?
- A) Yes, if JOINs are used in the view.
- B) No, views are restricted to a single table.
- C) Yes, but only for indexed views.
- D) No, unless INSTEAD OF triggers are implemented.
Answer Key
| Qno | Answer |
|---|---|
| 1 | B) A virtual table based on a SELECT query |
| 2 | B) Using the CREATE VIEW statement |
| 3 | B) Simplified access to complex queries |
| 4 | A) SELECT |
| 5 | A) Yes, by using JOINs in the SELECT statement |
| 6 | A) The view becomes invalid. |
| 7 | D) The sp_rename system procedure can be used to rename it. |
| 8 | B) Yes, but it restricts modifications to underlying tables. |
| 9 | B) A view with a unique clustered index |
| 10 | B) The view must use the WITH SCHEMABINDING option. |
| 11 | A) Faster query execution due to precomputed results |
| 12 | B) Updates to base tables automatically update indexed views. |
| 13 | B) They decrease performance due to update overhead. |
| 14 | A) Yes, but only with GROUP BY. |
| 15 | A) Changes are automatically reflected in the indexed view. |
| 16 | B) By providing access to specific data without exposing the entire table |
| 17 | A) Yes, if the user has SELECT permissions on the view. |
| 18 | B) WITH ENCRYPTION |
| 19 | C) The modification fails due to encrypted definition. |
| 20 | B) It prevents data changes that violate the view’s WHERE clause. |
| 21 | A) Yes, if the view is not complex. |
| 22 | A) The view is based on a single table without calculated columns. |
| 23 | B) The view reflects the changes automatically. |
| 24 | B) Views with aggregate functions or GROUP BY clauses |
| 25 | B) It allows custom logic for handling data changes through views. |
| 26 | A) Yes, if the view references a single table. |
| 27 | B) The update fails with an error. |
| 28 | B) No, only visible columns in the view can be inserted into. |
| 29 | A) Ensures data modifications comply with the view’s WHERE clause. |
| 30 | D) No, unless INSTEAD OF triggers are implemented. |