Sunday, 8 November 2015

Top 7 differences between Table and View in SQL

In order to undertand the database it is essential that one should know the difference between table and view. Below listed are few differences between table and view.
Table contains rows and columns, columns representing fields and rows containing the records whereas View is an imaginary or virtual table which contains rows and columns, just like a real table and is created from one or more than one table by joins, with selected columns.
Tables always occupy space in the memory database whereas Views never occupies space in the memory for storing data.
Table can have limited number of columns and unlimited number of rows whereas View is an extract from a database. For example, we have tables like Department, Technology, Sales, Personal Details, Academic details etc which has multiple columns and rows. But, we usually requires certain number of columns from each table to create a report in such cases rather than writing complex SQL query to fetch specific records  or columns from these tables, we create a View which saves time as requires less time to fetch data from View.
Tables are the actual database objects that hold rows whereas Views are "imaginary tables" or "Logical tables" that are created based on the actual tables.
Views are created to provide security mechanism. For example, we have a table which hold customers important personal details and various teams are working on this table. In such cases, we create View with selected number of records or fields so team can access the data for their operations. Only the users having the access to tables and modify the data, users having access to View can view data but cannot update it.
Views executes fasters than the Tables as Views are extract from tables and requires less time to execute or perform action.
Tables can be updated, deleted and modified whereas Views may be updated, modified and updated depending upon the permissions provided to user.
What is a need for View:
Views are used in two different scenarios.
  1. Whenever you required to give permissions on some information in a table to other users without providing access directly to the table we create views and give access on the view which provides security. E.g. Team A, Team B and Team C is working on the same project. But, due to some security constraint we don’t want to give permission to Team B and C to see the Salary, DOB of an EMPLOYEE.
  2. Sometimes we may be using complex queries for retrieving data from tables which may be complicated for us to write the query each time in such cases you can create a view using a query and then start querying on the view directly.
       E.g.  Create table EMPLOYEE
       (Emp_Idinteger not NULL, FName Varchar (25),LName  Varchar (25), Salary decimal (10, 2),Dept       Varchar (50), DOB date,Address Varchar (150))
        Create view EMPLOYEE_VIEW as
       (Select Emp_Id, FName, LName,Dept, Addressfrom EMPLOYEE)

No comments:

Post a Comment