interview questions and answers on sql server Views
In this article I will share some frequently asked views
interview questions and answers in SQL server.
1. What is a View?
View is a virtual table based on the result-set of an
SQL statement and that is Stored in the database with some name.
CREATE VIEW StudentProducts AS
SELECT * FROM Products WHERE CategoryId in (Select CategoryID from Categories where CategoryName='Books')
2. What are Different Types of views?
Basically there are two types of views
1. System views
2. User defined views.
You can create following views:
a. Standard Views
b. Indexed Views
c. Partitioned Views
3. Explain System views?
Microsoft SQL Server provides the ready collections of
system views that expose metadata.
System Views that already exist in the Master database
of Sql Server.
Example: Catalog views
4. Explain User defined views?
As name suggests these view are defined by users.
CREATE VIEW StudentProducts AS
SELECT * FROM Products WHERE CategoryId in (Select CategoryID from Categorieswhere CategoryName='Books')
5. What is
Creating a unique clustered index on a view is an
If the view is indexed then any queries that can be
answered using the index only will never need to refer to the underlying
tables. This can lead to an enormous improvement in performance.
6. What is
Catalog views retrieves information used by the SQL
Server. They are the most general interface to the catalog metadata and provide
efficient way to obtain, transform and present custom forms of this information.
Note: Catalog view do not contain information about
replication, backup or maintenance plans
7. What are
Various Limitations or Restriction of the Views?
Following are some limitations with sql server views
1. SELF JOIN
is not possible with Indexed View
2. ORDER BY now
allowed with views.
Join Not Allowed in Indexed Views.
not allowed in views , you can use COUNT_BIG(*) instead.
Index View union not allowed
6. You can't
create a view with a parameter or parameterized view.
Database Queries Not Allowed in Indexed View
8. You can’t
use keyword View Definition with Index View.
9. Views are
not based on temporary tables, when you try you will get error message like
“Views or functions are not allowed on temporary
8. What are
partitioned views and distributed partitioned views?
A partitioned view joins horizontally partitioned data
from a set of member tables across one or more servers, making the data appear
as if from one table
In a local partitioned view, all participating tables
and the view reside on the same instance of SQL Server.
In a distributed partitioned view, at least one of the
participating tables resides on a different (remote) server.
Distributed partitioned views can be used to implement
a federation of database servers. A federation is a group of servers
administered independently, but which cooperate to share the processing load of
9. Describe the
functionalities that views support? Or Views features
View Builder allows you to build views visually. It
allows you to create and edit views without knowledge of SQL
Simple to use:
A view can draw data from several different tables and
present it as a single table, turning multi-table queries into single-table
queries against the view.
Views can subset data in a table
They can join multiple tables into one virtual table
Views can provide more security and decrease
Better performance compared with complex queries.
They save space because only their definition is
They can also be used to create abstraction
Materialized views are commonly used in data
warehousing. They represent a snapshot of the data from remote sources.
If data is accessed and entered through a view, the
DBMS can automatically check the data to ensure that it meets the specified
Indexed views and partitioned view with their syntax?
Tasks required to create partitioned view:
a. Adding linked server definitions on each member
server that contains the connection information required to run distributed
queries on the other member servers.
the lazy schema validation option, by using sp_serveroption, for each linked
server definition that is used in distributed partitioned views.
a distributed partitioned view on each member server.
CREATE VIEW ProductsData AS
SELECT * FROM CompanyDatabase.TableOwner.Products_33
SELECT * FROM Server2.CompanyDatabase.TableOwner.Products_66
SELECT * FROM Server3.CompanyDatabase.TableOwner.Products_99
Tasks required to create Indexed view:
The following steps are required to create an
indexed view and are critical to the successful implementation of the indexed
the SET options are correct for all existing tables that will be referenced in
that the SET options for the session are set correctly before creating any new
tables and the view.
that the view definition is deterministic.
the view by using the WITH SCHEMABINDING option.
the unique clustered index on the view.