what is the use of view in sql server? [duplicate]

This question already has answers here: Closed 12 years ago.

Possible Duplicate:

What are views good for?

hi, i have a doubt what is a view. where should w开发者_C百科e use and what is the use of view. is there any good reference for views. thank you

---

Please, have an eye out this View (database) Wikipedia article.

In short, a View can be used with more than SQL Server, it is a SQL Standard.

A VIEW is generally used:

1. To present the information data on a different point of view;
2. To simplify the access to the information of a high-level complexity of the database schema;
3. To prevent direct access to database tables (for security purposes)(less popular these days because of ORM tools);
4. Some "reality" is just simpler with a VIEW.

Here's an example when the database schema stores hierarchical data in multiple database tables.

CREATE TABLE Vehicules (
    VId int IDENTITY(1, 1) PRIMARY KEY
    , VDescription nvarchar(20) NOT NULL
)

CREATE TABLE MotoredVehicules (
    MvId int IDENTITY(1, 1) PRIMARY KEY
    , MvVId int NOT NULL REFERENCES Vehicules (VId)
    , MvMake nvarchar(20) NOT NULL
)

CREATE TABLE MotorHP (
    MhpId int IDENTITY(1, 1) PRIMARY KEY
    , MhpMvId int NOT NULL REFERENCES MotoredVehicules (MvId)
    , MhpHP decimal(6, 2) NOT NULL
)

CREATE TABLE VehiculesWheels (
    VwId int IDENTITY(1, 1) PRIMARY KEY
    , VwVId int NOT NULL REFERENCES Vehicules (VId)
    , VwNumberOfWheels int NOT NULL
)

insert into Vehicules (VDescription) values (N'Bicycle')
GO
insert into Vehicules (VDescription) values (N'Motorcycle')
GO
insert into Vehicules (VDescription) values (N'Automobile')
GO
insert into Vehicules (VDescription) values (N'Yacht')
GO

-- Inserting the information about the vehicules that have a motor.
insert into MotoredVehicules (MvVId, MvMake) (
    select v.VId
            , N'Harley Davidson'
        from Vehicules as v
        where v.VDescription LIKE N'Motorcycle'
)
GO
insert into MotoredVehicules (MvVId, MvMake) (
    select v.VId
            , N'Sea-Ray'
        from Vehicules as v
        where v.VDescription LIKE N'Yacht'
)
GO
insert into MotoredVehicules (MvVId, MvMake) (
    select v.VId
            , N'Mercedes'
        from Vehicules as v
        where v.VDescription LIKE N'Automobile'
)
GO

-- Inserting motor HP for the motorized vehicules.
insert into MotorHP (MhpMvId, MhpHP) (
    select mv.MvId
            , 350
        from MotoredVehicules as mv
        where mv.MvMake LIKE N'Sea-Ray'
)
GO
insert into MotorHP (MhMvId, MhpHP) (
    select mv.MvId
            , 280
        from MotoredVehicules as mv
        where mv.MvMake LIKE N'Mercedes'
)
GO
insert into MotorHP (MhpMvId, MhpHP) (
    select mv.MvId
            , 930
        from MotoredVehicules as mv
        where mv.MvMake LIKE N'Harley Davidson'
)
GO

-- Inserting the number of wheels for wheeled vehicules.
insert into VehiculesWheels (VwVId, VwNumberOfWheels) (
    select v.VId
            , 2
        from Vehicules as v
        where v.VDescription IN (N'Bicycle', N'Motorcycle')
)
GO
insert into VehiculesWheels (VwVId, VwNumberOfWheels) (
    select v.VId
            , 4
        from Vehicules as v
        where v.VDescription LIKE N'Automobile'
)
GO

This relational model is not very comprehensive by itself. We could have used one only table to insert them all with all of their specifications, and let NULL the fields with no data. However, because of some hierarchy reasons, we have created tables for specifications about different type of Vehicules. Thus, when you want to have the information about vehicule in particular, it is not very practical, as you have to join the tables every now and then you have to retrieve the information. Here, it could become practical to have a View like so:

CREATE VIEW WheeledMotoredVehiculesView AS
    select v.VId
            , mv.MvMake
            , hp.MhpHP
            , vw.NumberOfWheels
            , v.VDescription
        from Vehicules as v
            left join MotoredVehicules as mv on mv.MvVId = v.VId
            left join MotorHP as hp on hp.MhpMvId on mv.MvId
            left join VehiculesWheels as vw on vw.VwVId = v.VId
GO

CREATE VIEW MotoredVehiculesView AS
    select v.VId
            , mv.MvMake
            , hp.MhpHP
            , v.VDescription
        from Vehicules as v
            left join MotoredVehicules as mv on mv.MvId = v.Id
            left join MotorHP as hp on hp.MhpMvId = mv.MvId
GO

CREATE VIEW WheeledVehicules AS
    select v.VId
            , vw.NumberOfWheels
            , v.VDescription
        from Vehicules as v
            left join VehiculesWheels vw on vw.VwVId = v.VId
GO

Then, instead of having to write the select contained within each of both above-created views each time you need to access some information data about a given vehicule, you simply query against the appropriate view itself:

select *
    from MotoredVehiculesView

Or whatever information you need.

Disclaimer: This code has not been tested and was written directly for example purposes only. It might not work as-is.

I hope this helps you better understand views, somehow.

---

Views are essentially "Queries" that you can select from without touching the main tables.

Any select query that you normally do (multiple tables, functions, etc.) could be a view.

There are two main advantages of views.

The first is that they allow you to reuse common ways of looking at the data without having to write the same complex SQL over and over (ie, modularization).

The second is that you can tighten security on the source tables for the particular user while allowing them to see the data through the views.

---

See Views in SQL Server for a good overview.

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is not stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.