Saturday, 10 October 2015

Difference between Table and View

Below list is major difference between Table and View in SQL Server;

Table contains Data.
View doesn’t contains data.
Table is physically existence.
View doesn’t physically existence. It’s like virtual table.
You can perform insert, update, delete and Select for it.
You can perform only Select operation on View.
Table can contain of one table records.
View can contain the data of multiple tables.
Create table EMPLOYEE
ID    Int,
Name  varchar (100),
Salary      decimal (10, 2),
DOB   dateTime
Create view EMP_VIEW as
(Select ID, Name, Salary, DOB From EMPLOYEE)


  1. only select is allowed on view ? Is it true. I dont think so..refer this link

    1. If you have created the view from single table than it is possible but if you have created view from multiple table it will create problem.
      Suppose you have two table country and state,
      you created view as

      CREATE VIEW dbo.conState
      SELECT s.CountryId,c.CountryName,s.StateName FROM [dbo].[tblState] s inner join
      [dbo].[tblCountry] c on c.CountryId = s.CountryId

      it will give error.
      Msg 4405, Level 16, State 1, Line 1
      View or function 'conState' is not updatable because the modification affects multiple base tables