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.