Saturday, 10 October 2015

Difference between Table and View

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



Table
View
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)

2 comments:

  1. only select is allowed on view ? Is it true. I dont think so..refer this link http://www.dotnet-tricks.com/Tutorial/sqlserver/b4H8260312-Different-Types-of-SQL-Server-Views.html

    ReplyDelete
    Replies
    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
      AS
      SELECT s.CountryId,c.CountryName,s.StateName FROM [dbo].[tblState] s inner join
      [dbo].[tblCountry] c on c.CountryId = s.CountryId
      GO

      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

      Delete

Others

You will like to read it