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.
Example:
CREATE VIEW StudentProducts AS
SELECT * FROM Products WHERE CategoryId in (Select CategoryID from Categories where CategoryName='Books')
GO
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.
Example:
CREATE VIEW StudentProducts AS
SELECT * FROM Products WHERE CategoryId in (Select CategoryID from Categorieswhere CategoryName='Books')
GO
5. What is
indexed view?
Creating a unique clustered index on a view is an
indexed view.
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.
More info…http://www.codechef4u.com/post/2015/09/05/indexed-views
6. What is
Catalog Views?
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.
3. Outer
Join Not Allowed in Indexed Views.
4. Count(*)
not allowed in views , you can use COUNT_BIG(*) instead.
5. With
Index View union not allowed
6. You can't
create a view with a parameter or parameterized view.
7. Cross
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
tables”
8. What are
partitioned views and distributed partitioned views?
A partitioned
view:
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
Local
partitioned view:
In a local partitioned view, all participating tables
and the view reside on the same instance of SQL Server.
Distributed
partitioned view:
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
a system.
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.
Table joins:
Views can subset data in a table
They can join multiple tables into one virtual table
Security:
Views can provide more security and decrease
complexity.
Performance:
Better performance compared with complex queries.
They save space because only their definition is
stored.
Abstraction:
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.
Data Integrity
If data is accessed and entered through a view, the
DBMS can automatically check the data to ensure that it meets the specified
integrity constraints.
10. Explain
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.
b. Setting
the lazy schema validation option, by using sp_serveroption, for each linked
server definition that is used in distributed partitioned views.
c. Creating
a distributed partitioned view on each member server.
Example:
CREATE VIEW ProductsData AS
SELECT * FROM CompanyDatabase.TableOwner.Products_33
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Products_66
UNION ALL
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
view:
1. Verify
the SET options are correct for all existing tables that will be referenced in
the view.
2. Verify
that the SET options for the session are set correctly before creating any new
tables and the view.
3. Verify
that the view definition is deterministic.
4. Create
the view by using the WITH SCHEMABINDING option.
5. Create
the unique clustered index on the view.