The view is a virtual table, which can have the multiple columns from the one or more table. It can be used like the normal table. Normally view cannot store the data permanently in the table. When we create the view it stores the view definition schema as object under the concern database.
Let us see the syntax of the create view
CREATE VIEW View Name [Alias name1, name2,]
WITH ENCRYPTION
WITH SCHEMA BINDING
AS
SELECT statement [WITH CHECK OPTION]
The create view can be created with the view name and the alias can be given in the view name parameter parenthesis. The view schema can be stored in the encrypted format. Here is an option like SCHEMA BINDING; this is an important mile stone in the view to allow the developers to create the permanent view.
When to use VIEW?
When you have complex queries, that use many places in the stored procedures or functions, etc..,
It will be used as security mechanism in the web applications. When we use the original table in the web applications the hackers may drop the table. That time the original data will be persist in the table.
When you want to hide the particular columns to the specific people then we can create the specialized view.
Advantages of Views
Restrict data access and/or simplify data access
A view can be used to limit the user to only use a few columns in a table. For example if we do not want a user to be able to access all columns because of security. But it could also be because not all columns are interesting for the user. It is also possible to limit access to rows, using a WHERE clause. If we use USER_ID(), we can even find out who is using the view, and return only data that is relevant for this user. Furthermore, a view can join several tables, in several databases on several servers, but all the users use is the view's name. Simple, but powerful!
Simplify data manipulation
We can also use a view to manipulate data. Often with a relational database design, this means that many tables must be joined together. Using a view can simplify this, and the users do not need to know all tables involved.
Import and export data
A view can also be used to export and/or import data to/from another application. Both the bcp utility and BULK INSERT works with a view.
Merge data
A so called Partition View can be used to merge data from multiple tables in multiple databases, so it appears as one table only, but it is in fact several tables. This can be accomplished by using the UNION operator. For example if we had customers in Europe and United States, we could have one server for Europe, and one for United States, with tables that are identical, and then merge all data in a partitioned view.
