Wednesday, 22 May 2013

View In Sql Query

Sqlsever::ViewBasic

Sql server:Basic About View.

1)What is View?
                              VIEW is a virtual table, defined by a query, that does not exist until it is invoked by name in an SQL statement.It means only defination of view is stored on database not a data It is used to implements the security mechanism in the Sql Server.
for example in login_master table have info of user and also have login credentials of User in that case a admin doesnot want to give you full access of a login_master that time we can create a view to get a access of other field to users.

2) The Standard SQL syntax for the VIEW definition is

Create View


     Create View Viewname As Select Column1, Column2 From Tablename Where (Condition)Group by (Grouping Condition) having (having Condition)

Alter View


    Alter View Viewname As Select Column1, Column2 From Tablename Where (Condition)Group by (Grouping Condition) having (having Condition)

How to Use View In Sql Query


   Select *from View Where (Condition)Group by (Grouping Condition) having (having Condition)

We will see a example of Views
Create a following tables and some data for a better understanding.


       create tabledbo.User_Login_Details
   (
           User_Id intnot null,
           User_Name varchar(100) null,
           User_Passowrd varchar(50) null,
           User_Count int null,
           User_Emp_Code varchar(50) null
   )

     create table Emp_mater
   (
         Emp_code int
        ,Emp_name varchar(100)
        ,emp_city varchar(100)
   )


Add Some Data in both table .
Create view on single table.
       Create View ViewSingleTable as select * fromdbo.User_Login_Details

alter view on single table.
       Alter View ViewSingleTable as selectUser_Name,User_Countfromdbo.User_Login_Details

create view on multiple table.
      Create View ViewSingleTable as select * fromdbo.User_Login_Details inner join Emp_mater on User_Emp_Code=Emp_code

How to use a view in query.
       select *  from  ViewMultiTable
    select *   from  ViewSingleTable

No comments:

Post a Comment

8451831077