SQL server views interview questions and answers

SQL server views interview questions and answers for freshers and experienced candidates. These interview questions and answers on SQL server views will help you strengthen your technical skills, prepare for the interviews and quickly revise the concepts. Many candidates appear for the interview for one role - many of the them give the right answers to the questions asked. The one who provides the best answer with a perfect presentation is the one who wins the interview race. The set of SQL server views interview questions here ensures that you offer a perfect answer to the interview questions posed to you.
          

SQL Server views interview questions

 

<<Previous  Next>>

Define Views.
What is Indexed view? How to create it?
What are partitioned views and distributed partitioned views?
What functions can a view be used to performed?
Describe the functionalities that views support.
Explain Indexed views and partitioned view with their syntax.
What are the restrictions that views have to follow?

SQL Server views - August 29, 2008 at 18:00 PM by Nishant Kumar

Define Views.

  • View can be created to retrieve data from one or more tables.
  • Query used to create view can include other views of the database.
  • We can also access remote data using distributed query in a view.


Views - October 24, 2008 at 18:10 pm by Rajmeet Ghai

What is Indexed view? How to create it?

In an indexed view, the data is already computed and stored. Data can be accessed by a unique index. This index is a clustered index. In order to create an index the syntax is

CREATE [UNIQUE], [CLUSTERED | NONCLUSTERED] INDEX index_name
ON {view}
[WITH <index_option>]
[ON filegrp]

What are partitioned views and distributed partitioned views?

Partitioned views allow data in a large table to be split into smaller tables. These small tables are called as member tables. The split is done based on range of data values in one of the columns.

In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.

What functions can a view be used to performed?

Functions of View:-

  • Subset data of a table
  • Can join multiple tables values into one
  • They can act as aggregated tables. i.e. a view can be used to store Sum, average of values
  • Views can be nested and can be used for abstraction

Define views.

Answer
A view can be considered as a virtual table. It does not physically exist. It is based on the result set of a SQL statement. A view contains rows and tables just like a real table.

Describe the functionalities that views support.

Answer

  • Views can subset data in a table
  • They can join multiple tables into one virtual table
  • Views can provide security and decrease complexity
  • They save space because only their definition is stored.
  • 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.

Views can create other calculated fields based on values in the real underlying tables

Explain Indexed views and partitioned view with their syntax.

Answer
Indexed view:

An index view has a unique clustered index created on it. They exist as rows on the disk. Because they are saved on the disk, the response time to a query is fast at the cost of space consumption. They are more commonly used in scenarios when data modification is less.

Syntax:
Create Index CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name

The view is created using the CREATE VIEW synatx

Partitioned view:

Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server.

Syntax:

The view is then created by UNIONing all the tables and an updateable partitioned View results

Server 1 :
CREATE TABLE Customer1 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions)

Similar tables created for Server 2 and 3

Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3

What are the restrictions that views have to follow?

Answer

  • Since a view is a virtual table – columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again.
  • The select statement on the view cannot contain ORDER BY or INTO TEMP
  • When a table or view is dropped, any views in the same database are also dropped.
  • It is not possible to create an index on a view
  • It is not possible to use DELETE to update a view that is defined as a join.

<<Previous  Next>>

Also read

What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column?

Answer - A table can have only one ROWGUIDCOL column......

Describe in brief system database.

Answer - Master database is system database. It contains information about server’s configuration. It is a very important database and important to backup Master.....

Describe in brief SQL Server monitoring ways.

Answer - SQL Profiler is a tool that stores events from the server. SQL Profiler saves the events in the trace file......

SQL Server Optimization Tips

Answer - Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic......

Describe in brief authentication modes in SQL server.

Answer - This is the default and recommended security mode. In this mode, access to SQL server is controlled by Windows NT.....   

Describe in brief Databases and SQL Server Databases Architecture.

Answer - A database is a structured collection of data. Database can be thought as simple data file......



Write your comment - Share Knowledge and Experience




 
Interview questions
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring