SQL Server Views Interview Questions and Answers
In this article, we will discuss most frequently asked SQL Server Views interview questions and answers.
What is a View in SQL Server?
A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.
So, we can think of a view either as a compiled SQL query or a virtual table. As a view represents a virtual table it does not physically store any data by default. When we query a view we actually, retrieve the data from the underlying database tables.
What are the differences between a table and view?
When compared with a table we have the following differences between a table and view.
- The table is physical and the view is logical
- A table is an independent object whereas view is a dependent object that is a view depends on a table or tables from which it is loading the data.
- When a new table is created from an existing table the new and old tables are independent themselves that is the changes of one table will not be reflected into the other table whereas if a view is created based on a table any changes that are performed on the table reflects into the view and any changes performed on the view reflected in the table also.
What is the syntax for creating a view?
Create View <viewName> as Select * / <required column name> From <Table name>
How many types of views are there in SQL Server?
We can create the view in two ways those are
- Simple view and Updatable views
- Complex view and non-updatable views.
What is a simple view or Updatable view?
- The view which is created basing on the columns of a single table is known as the simple view.
- We can perform all DML operations on a simple view so that a simple view is also called as updatable view or dynamic view.
CREATE VIEW VIEW1 AS SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE
CREATE VIEW VIEW2 AS SELECT * FROM DEPT
Example on simple view:
Create a view to display branches details and perform all DML operations on a view.
CREATE VIEW SV1 AS SELECT * FROM BRANCHES
Once we create a VIEW on branches table then we can perform all DML operations on a table through a view like below.
INSERT INTO SV1 VALUES (1026,'PNB','HYD') UPDATE SV1 SET BLOC ='UP' WHERE BCODE = 1023 DELETE FROM SV1 WHERE BCODE = 1027
What is a complex view?
- When we create a view on more than 1 table then it is known as the complex view.
- On complex view, we cannot perform DML operations so that a complex view is also called as a non-updatable or static view.
CREATE VIEW VIEW3 AS SELECT E.EMPNO, E.ENAME, E.SAL. S.GRADE, S.LOSAL, S.HIGHSAL FROM EMPLOYEE E INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HIGHSAL
NOTE: A view which is created based on a single table will also be considered as a complex view provided if the query contains any of the following.
Distinct. Aggregate Function, Group By Clause, having Clause, calculated columns, and set operations.
CREATE VIEW VIEW4 AS SELECT DEPTNO, SUM(SAL) AS SALSUM FROM EMPLOYEE GROUP BY DEPTNO
CREATE VIEW VIEW5 AS SELECT JOB FROM EMPLOYEE WHERE DEPTNO=20 INTERSECT SELECT JOB FROM EMPLOYEE WHERE DEPTNO = 30
Can we drop a table that has dependent views on it?
Yes, we can drop a table even if any dependent views are associated with it, but the views that are associated with it will not be dropped. They will still execute in the database only with the status as inactive object and all those views become active and start functioning provided the table is recreated.
Can we create a view based on other views?
Yes, we can create a view based on other views. Usually, we create views based on tables, but it is also possible to create views based on views.
Can we update the views?
Yes, views can be updated. However, updating a view that is based on multiple tables, may not update the underlying tables correctly. To correctly update a view that is based on multiple tables we can make use INSTEAD OF triggers in SQL Server.
If a view is created by using the attribute “SCHEMABINDING” then we cannot drop the table or alter the table columns on which the view is dependent.
CREATE VIEW MARKETING_EMP WITH SCHEMABINDING AS SELECT EMPNO, JOB, SAL, DEPTNO FROM dbo.EMPLOYEE WHERE DEPTNO = 10
After creating the view, we cannot drop the employee table or alter any columns that are specified in the view.
When we want to use the schema binding option it is must specify each and every column name in the select statement but cannot use “*”.
While using the schema binding option, the table name must be prefixed with the owner name i.e. dbo which tells the current user is only the owner of the table.
If required, we can use the “WITH ENCRYPTION” and “WITH SCHEMABINDING” option at the same time.
ALTER VIEW MARKETING_EMP WITH ENCRYPTION, SCHEMABINDING AS SELECT EMPNO, JOB, SAL, DEPTNO FROM dbo.EMPLOYEE WHERE DEPTNO = 10
Why We Need Views?
To protect the data. If we have a table containing sensitive data in certain columns, we might wish to hide those columns from certain groups of users. For instance, customer names, addresses, and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the original tables.
A view is a logical table but what it stores internally is a select statement that is used for creating the view. So that whenever a user performs an operation on the view like select, insert, update or delete internally the view performs those operations on a table.
Simply we can say that view will act as an interface between the data provider (Table) and the User.
A view is created based on a table any changes that are performed on the table reflects into the view any changes performed on the view reflected in the table also.
Explain the differences between synonyms and views.
|A synonym is an alias name for a table||View is a virtual image for a table.|
|Synonym contains physical existence and hence occupy memory.||There is no physical existence and hence it will not occupy memory.|
|the Synonyms are not possible to create on specific columns of a table.||But views are possible to create on specific columns of a table.|
|By using synonym it is not possible to hide the security column data in a table.||But it is possible to hide a security column data in a table.|
|We cannot create a synonym on more than 1 table at a time.||Whereas view can create on more than 1 table at a time.|
|It does not provide security to the table information.||But views are using for security purpose in real time.|
What are the advantages of using views? OR when do you usually use views?
Advantages of using views:
Views can be used to reduce the complexity of the database schema, for non-IT users. The sample view, vWEmployeesByDepartment, hides the complexity of joins. Non-IT users finds it easy to query the view, rather than writing complex joins.
Views can be used as a mechanism to implement row and column level security.
Row Level Security:
For example, I want an end user, to have access only to IT Department employees. If I grant him access to the underlying tblEmployees and tblDepartments tables, he will be able to see, every department employees. To achieve this, I can create a view, which returns only IT Department employees, and grants the user access to the view and not to the underlying table.
A view that returns only IT department employees:
Create View vWITDepartment_Employees as Select Id, Name, Salary, Gender, DeptName from tblEmployee join tblDepartment on tblEmployee.DepartmentId = tblDepartment.DeptId where tblDepartment.DeptName = 'IT'
Column Level Security:
Salary is confidential information and I want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end user access to these views rather than the base tables.
A view that returns all columns except Salary column:
Create View vWEmployeesNonConfidentialData as Select Id, Name, Gender, DeptName from tblEmployee join tblDepartment on tblEmployee.DepartmentId = tblDepartment.DeptId
Views can be used to present only aggregated data and hide detailed data.
The view that returns summarized data, Total number of employees by Department.
Create View vWEmployeesCountByDepartment as Select DeptName, COUNT(Id) as TotalEmployees from tblEmployee join tblDepartment on tblEmployee.DepartmentId = tblDepartment.DeptId Group By DeptName
What are indexed views? Or What are materialized views?
A view is a virtual table means it does not contain any physical data. A view is nothing more than compiled SQL query. Every time, we issue a select query against a view, we actually get the data from the underlying base tables and not from the view, as the view itself does not contain any data.
When we create an index on a view, the data gets physically stored in the view. So, when we issue a select query against an indexed view, the data is retrieved from the index without having to go to the underlying table, which will make the select statement to work slightly faster. However, the disadvantage is INSERT, UPDATE and DELETE operations will become a little slow, because every time we insert or delete a row from the underlying table, the view index needs to be updated. In short, DML operations will have a negative impact on performance.
Oracle refers to indexed views as materialized views.
Only the views created with schema binding can have an Index. Simply adding WITH SCHEMABINDING to the end of the CREATE VIEW statement will accomplish this. However, the effect is that any changes to the underlying tables which will impact the view are not allowed. Since the indexed view is stored physically, any schema changes would impact the schema of the stored results set. Therefore, SQL Server requires that schema binding is used to prevent the view’s schema (and therefore the underlying tables) from changing.
The first index for a view must be a UNIQUE CLUSTERED INDEX, after which, it’s possible to create non-clustered indexes against the view.
Indexed Views are heavily used in data warehouses and reporting databases that are not highly transactional.
What are the limitations of a View?
- We cannot pass parameters to a view.
- Rules and Defaults cannot be associated with views.
- The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified.
- Views cannot be based on temporary tables.
Please read below articles to learn Views in SQL server with real-time examples.
In this article, I try to explain most frequently asked SQL Server Views interview questions and answers. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.