MS SQL Server Views Interview Preparation Guide
Prepare comprehensively for your SQL Server Views interview with our extensive list of 7 questions. Our questions cover a wide range of topics in SQL Server Views to ensure youre well-prepared. Whether youre new to the field or have years of experience, these questions are designed to help you succeed. Dont miss out on our free PDF download, containing all 7 questions to help you succeed in your SQL Server Views interview. Its an invaluable tool for reinforcing your knowledge and building confidence.7 SQL Server Views Questions and Answers:
1 :: Do you know what are the restrictions that views have to follow?
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.
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.
2 :: Explain 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
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
3 :: Explain 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
Syntax:
Create Index CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
The view is created using the CREATE VIEW synatx
4 :: Explain the functionalities that views support?
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
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
5 :: What are views?
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.
6 :: Explain 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.
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.
7 :: Can you explain 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]
CREATE [UNIQUE], [CLUSTERED | NONCLUSTERED] INDEX index_name
ON {view}
[WITH <index_option>]
[ON filegrp]