What is View and how to Create it in SQL server ?

In SQL, a view is a virtual table based on the result-set of an SQLstatement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.


Creating view:-

1.      Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.

2.      To create a view, a user must have the appropriate system privilege according to the specific implementation.

3.      The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

Aletring Views:-

To modify view we sue ALTER keyword :-

ALTER VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

4.      You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL SELECT query.

Create A Database Table

USE [Employee]

GO

/****** Object:  Table [dbo].[Employee_Test]  

 Script Date: 08-05-2015 12:59:52 ******/

CREATE TABLE [dbo].[Employee_Test](

       [Emp_ID] [int] NOT NULL,

       [Emp_name] [varchar](100) NOT NULL,

       [Emp_Sal] [int] NULL,

       [Age] [int] NULL,

       [Gender] [nchar](10) NOT NULL,

       [DeptId] [int] NULL

) ON [PRIMARY]

GO

Insert some records

USE [Employee]

GO

INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])

 VALUES (1, N'Anees', 1000, 30, N'Male', 101)

GO

INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])

 VALUES (2, N'Rinku', 1200, 45, N'Male', 102)

GO

INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])

 VALUES (3, N'John', 1100, 25, N'Male', 101)

GO

INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])

 VALUES (4, N'Stephen', 1300, 30, N'Male', 102)

GO

INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])

 VALUES (5, N'Maria', 1700, 45, N'Female', 101)

GO

INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])

 VALUES (6, N'Ankit', 1000, 25, N'Male', 103)

GO

INSERT [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender], [DeptId])

 VALUES (7, N'Renu', 1200, 30, N'Female', 101)

GO

Select data from Table

Select * from [dbo].[Employee_Test]

Now Create A View on the above table

CREATE View [dbo].[vw_Emp]

   as

  Select [Emp_ID] as EmpId

        ,[Emp_name] as EmpName

        ,[Emp_Sal] as EmpSalary

        ,[Age] as Age

        ,[Gender] as Gender

        ,[DeptId] as DeptId

            from [Employee].[dbo].[Employee_Test]

And View work like a real table

Select * from [dbo].[vw_Emp] where EmpSalary>1000