View can be defined as a virtual table which is created on a table whatever changes (SELECT, UPDATE, INSERT, DELETE). You make against a view will be reflected on the associated table as well. View does not occupies any memory space.
[A view is quite easy to define, but a lot more difficult to create, use and manage. It’s not anymore than a named SELECT statement, or a virtual table. You can select data, alter data, remove data and all other things you can do with a table (with some limitations). But there is a difference between a view and a table. The data accessible through a view is not stored in the database as its own object. It’s stored in the underlying tables that make up the view. The only thing that is stored in the database is the SELECT statement that forms the virtual table ]
As you could see in the previous section, a view is very useful. Here are some other scenarios when a view can be very useful.
- 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 user 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. More on this later.
Syntax
CREATE VIEW
CREATE VIEW EMPV AS SELECT * FROM EMPLOYEES
CREATE VIEW EMPV AS SELECT ENAME, DGN, SAL FROM EMPLOYEES WHERE DEPTNO=20
Changing Columns names in a view
CREATE VIEW EMPV (EEMPNAME, DEISGNATION, SALARY) AS SELECT ENAME, DGN, SAL FROM EMPLOYEES WHERE DEPTNO=20
Dropping a View
DROP VIEW EMPV
.png)
.png)
