View in SQL Server is a virtual table which is used to create web API. Suppose there is a company which has more than 2 departments. Client of this company would like to know the data of employee. But company management would not like to share salary details of Employee to the client, In this case back end developer we will create one view in SQL Server that will not contain salary column. For better understanding we will create two tables as EmployeeDetails and Department in database like the following,

  1. create table Empdetails
  2. (
  3. eid int Primary key, ename varchar(20), Designation varchar(20), Salary int
  4. )
  5. create table Department
  6. (
  7. Did int, DName varchar(20), eid int foreign key references Empdetails(eid)
  8. )
  9. Inserting values in Database
  10. Insert into Empdetails values
  11. (
  12. 1, ’Sandeep’, ’S / w Dev.’, 25000
  13. )
  14. Insert into Department values
  15. (
  16. 101, ’Development’, 1
  17. )
EmployeeDetails
Figure: EmployeeDetails table

Department
Figure: Department table

Now back end developer will create one web API to use view-in-sql-server. Here's the syntax or the same,

Syntax
  1. Create view-in-sql-server view-in-sql-server_name
  2. As
  3. Select column name
  4. From tables name
  5. Where clause
Sample Example
  1. create view - in -sql - server ViewEmpdep
  2. as
  3. select e.ename, e.Designation, d.did, d.dname
  4. from Empdetails e, Department d
  5. where e.eid = d.eid
ViewEmpdep
Figure: ViewEmpdep

Now front end developer can write the code and send the Employee details to client. Suppose front end developer is writing code in C#.NET as,
  1. Using System.Data;
  2. Using System.Data.SqlClient;
  3. SqlConnection con = new SqlConnection(“Integrated security = true; Initial catalog = DatabaseDetails; data source = .”);
  4. SqlDataAdapter Da;
  5. Private void Page_Load()
  6. {
  7. Con.Open();
  8. String s = ”select * from ViewEmpdep”
  9. Da = new SqlDataAdaper(s, con);
  10. Dataset ds = new Dataset();
  11. Da.Fill(ds, ”Empdet”);
  12. DataGridView1.DataSource = ds.Tables[0];
  13. DataGridView1.DataBound
  14. Con.Close
  15. }
  16. MessageBox.Show(“Details has displayed”);
Now company can send the Employee details without Salary details to client.

Point to Remember: View is virtual table which is used to create web API. It is used to display the data from one and more than one tables. There are two types of View and these are:
  1. Simple View
  2. Complex View

Syntax for View is

  1. Create viewView_Name
  2. As
  3. Select column name
  4. From tables
  5. Where clause
  1. Simple View: Simple view is a type of view which is used to create web API for restrictions of data in a single table.

    Simple View Explanations

    EmployeeDetails
    Figure: Table EmployeeDetails

    There is a table EmployeeDetails which contains columns Eid, EName, Edistig and Salary. In this table we have to display EName, Edistig where Salary is greater than 20000.

    Query for Simple View is:
    1. Create View viewSimple As
    2. Select Eid, EName, Edistig
    3. FromEmployeeDetails
    4. Where Salary > 20000
  2. Complex View: Complex view is a type of view which is used to create web API for restrictions of data by using more than 1 tables.

    Complex View Explanations

    EmployeeDetails
    Figure: Table EmployeeDetails

    Department
    Figure: Table Department

    EmployeeAdress
    Figure: Table EmployeeAdress

    There are two tables, EmployeeDetails which contains columns Eid, EName, Edestig and Salary and table Department that contains column Did, DName, EId and Table EmployeeAddress contain Column EId, EAdress and ParentContact . In these table we have to display Ename, Edestig, Salary, DId, DName, EAdress, and ParentContact .

    Query for creating Complex View:
    1. Create View ViewComplex
    2. as
    3. selecte.ename, e.Designation, d.DId, d.DName, a.EAdress, a.ParentContact
    4. from EmpDetails e, Department d, EmployeeAdress a
    5. where e.EId = d.EId And
    6. e.EId = a.EId

Recap: View is a virtual table which is used to create Web API. There are 2 types of View and those are,

  1. Simple View and
  2. Complex view

Syntax

  1. CreateView View_Name
  2. As
  3. SelectColumns Name
  4. FromTables Name
  5. Where Clause