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

Question

Possible Duplicate:
What are views good for?

hi, i have a doubt what is a view. where should we use and what is the use of view. is there any good reference for views. thank you

Answer 1

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.