What are the views and how to create a view in SQL Server
Author: Mike Green
This article will discuss about views and we will also see how we can create a view in SQL Server.
Views can be considered as a virtual table just like a real table with columns and rows. It also appears as a additional layer on the real tables which limits users to reach and change real tables. View is used as a security mechanism to restrict the data available to end users. A view is a stored query and since the definition of view is stored in the database not the data which is accessible from this view, there is no significant space involved for using a view.
Views are used to represent data from a different perspective and to simplify the access of higher complexity information. It is like a window provided to the users so that they can get data in a simple way. We can retrieve data from a view and we can also update data in a view. Views are considered as simple but powerful abstraction of database data.
The columns in the views can be from one or more real tables of the databases. We can add functions, WHERE and JOIN statements to a view and show data from different tables as the data is coming from a single table thus simplifying the schema of our database for users. Views can be used inside a query in SQL Server or in an application, or in a stored procedures, or even from inside another view.
Views are also used to get aggregate data in an optimized way. So views not only hide the complexity but can also provide an optimized way to get aggregate data. It gives the huge performance boost by aggregating data with a view and use that view in your queries and stored procedures.
It is very useful to have views in your database. Views are more handy than functions and more flexible than stored procedures. It can give your database more security and easier access of needed data to users.
Uses of Views
So specifically there are three basic and simple uses of views.
- For security purposes because we encapsulate real data and names of real tables by using views and restrict users from some specific rows and columns.
- To simplify the complexity of data and easy access of required information.
- To retrieve aggregate data of different tables
Limitations and disadvantages of Views
- Views cannot be created with parameters as functions and stored procedures can be.
- ORDER BY clause doesn't work when we create a view. We cannot use ORDER BY inside a view rather we can use it with a view or outside a view as we use it with real tables.
- View doesn't allow us to use COUNT(*) when we create it. It forces us to use column name.
- ANSI_NULLS must be set ON when we create a view.
- Views are not fast enough to match the stored procedures and since it is actually an abstraction therefore it may be slow than the actual select query from the table.
Create a View in SQL Server
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
CREATE View SampleView
SELECT C.CatID, C.CatName, COUNT(P.ProdID) AS TotalProd
dbo.Category C With(nolock)
INNER JOIN dbo.Products P WIth(nolock)
ON C.CatID = P.CatID