This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Views”.
1. What is a view ?
a) A view is a special stored procedure executed when certain event occurs
b) A view is a virtual table which results of executing a pre-compiled query
c) A view is a database diagram
d) None of the Mentioned
Explanation:VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. A view do not contain data of their own.
2. Which of the following is not a limitation of view ?
a) ORDER BY Does Not Work
b) Index Created on View Used Often
c) Cross Database Queries Not Allowed in Indexed View
d) Adding Column is Expensive by Joining Table Outside View
Explanation:Views created on indexed Columns is often used because of performance optimization problems.
3. Which of the following following statement is true ?
a) Views could be looked as an additional layer on the table which enables us to protect intricate or sensitive data based upon our needs
b) Views are virtual tables that are compiled at run time
c) Creating views can improve query response time
d) All of the Mentioned
Explanation:Views are a valuable tool for the SQL Server Developer, because they hide complexity and allow for a readable style of SQL expression.
4. SQL Server has mainly how many types of views ?
Explanation:In SQL Server we have two types of views-System Defined Views and User Defined View.
5. Dynamic Management View is a type of
a) System Defined Views
b) User Defined View
c) Simple View
d) Complex View
Explanation:Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine.
6. Syntax for creating views is
a) CREATE VIEW AS SELECT
b) CREATE VIEW AS UPDATE
c) DROP VIEW AS SELECT
d) CREATE VIEW AS UPDATE
Explanation:SQL CREATE VIEW Syntax:CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition.
7. You can delete a view with ___________ command.
a) DROP VIEW
b) DELETE VIEW
c) REMOVE VIEW
d) TRUNCATE VIEW
Explanation:DROP VIEW removes one or more views from the current database.
8. What is SCHEMABINDING a VIEW ?
a) Schema binding binds your views to the dependent physical columns of the accessed tables specified in the contents of the view
b) These are stored only in the Master database
c) These types of view are defined by users on specified schema
d) These are used to show database self describing information
Explanation: SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
9. Which of the following is not a SQL Server INFORMATION_SCHEMA view ?
Explanation:The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database. These views can be found in the master database under Views / System Views and be called from any database in your SQL Server instance.
10. ___________ is stored only in the Master database.
a) Database-scoped Dynamic Management View
b) Complex View
c) Catalog View
d) None of the above mentioned
Explanation:Server-scoped Dynamic Management View are stored only in the Master database.
Sanfoundry Global Education & Learning Series – SQL Server.