What's the difference between views and tables with MSSQL

Views are are abstractions of select statements, that are stored in the system. The views contain rows and column, but the data is not stored in the views. The views contain only the definition of what it will look like. The view definition is a bunch of select statement. Views can contain SQL functions, from and where. Views cannot contain order by.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. SELECT emp_short,trans_date,debit,credit
  2. FROM trans02;
  3. go
[Get Code]
emp_short trans_date              debit                 credit
--------- ----------------------- --------------------- ---------------------
000190    2007-06-01 00:00:00.000 NULL                  125.00
000110    2007-06-01 00:00:00.000 NULL                  100.00
000190    2007-06-02 00:00:00.000 25.00                 NULL
000030    2007-07-15 00:00:00.000 357.00                NULL
000030    2007-07-16 00:00:00.000 NULL                  22.00
000030    2007-07-18 00:00:00.000 NULL                  41.00
000030    2007-07-20 00:00:00.000 NULL                  29.50
000110    2007-07-20 00:00:00.000 42.00                 NULL
000110    2007-07-22 00:00:00.000 66.00                 NULL
000110    2007-07-23 00:00:00.000 77.00                 NULL
000010    2007-07-23 00:00:00.000 11.00                 NULL
000010    2007-07-24 00:00:00.000 22.56                 NULL
000010    2007-07-25 00:00:00.000 33.69                 NULL

(13 row(s) affected)

Views

  1. CREATE VIEW view_trans02 AS
  2. (
  3. SELECT emp_short, trans_date, debit, credit
  4. FROM   trans02
  5. )
  6. go
[Get Code]
(1 row(s) affected)
  • This created the view view_trans02 that refers to the table trans02.

Performance of view vs table

  1. SELECT * FROM view_trans02;
  2. go
[Get Code]
emp_short trans_date              debit                 credit
--------- ----------------------- --------------------- ---------------------
000190    2007-06-01 00:00:00.000 NULL                  125.00
000110    2007-06-01 00:00:00.000 NULL                  100.00
000190    2007-06-02 00:00:00.000 25.00                 NULL
000030    2007-07-15 00:00:00.000 357.00                NULL
000030    2007-07-16 00:00:00.000 NULL                  22.00
000030    2007-07-18 00:00:00.000 NULL                  41.00
000030    2007-07-20 00:00:00.000 NULL                  29.50
000110    2007-07-20 00:00:00.000 42.00                 NULL
000110    2007-07-22 00:00:00.000 66.00                 NULL
000110    2007-07-23 00:00:00.000 77.00                 NULL
000010    2007-07-23 00:00:00.000 11.00                 NULL
000010    2007-07-24 00:00:00.000 22.56                 NULL
000010    2007-07-25 00:00:00.000 33.69                 NULL

(13 row(s) affected)
  1. SELECT emp_short, trans_date, debit, credit
  2. FROM trans02;
  3. go
[Get Code]
emp_short trans_date              debit                 credit
--------- ----------------------- --------------------- ---------------------
000190    2007-06-01 00:00:00.000 NULL                  125.00
000110    2007-06-01 00:00:00.000 NULL                  100.00
000190    2007-06-02 00:00:00.000 25.00                 NULL
000030    2007-07-15 00:00:00.000 357.00                NULL
000030    2007-07-16 00:00:00.000 NULL                  22.00
000030    2007-07-18 00:00:00.000 NULL                  41.00
000030    2007-07-20 00:00:00.000 NULL                  29.50
000110    2007-07-20 00:00:00.000 42.00                 NULL
000110    2007-07-22 00:00:00.000 66.00                 NULL
000110    2007-07-23 00:00:00.000 77.00                 NULL
000010    2007-07-23 00:00:00.000 11.00                 NULL
000010    2007-07-24 00:00:00.000 22.56                 NULL
000010    2007-07-25 00:00:00.000 33.69                 NULL

(13 row(s) affected)
  • The results are identical! We knew that.
  • Actually, when MSSQL execute the view it replaces the view name with the actual select statement that is the definition of the view.
  • See below for the execution plans
Execution Plan for View vs Table
Execution Plan for View vs Table
Execution Plan Cost and SQL Expansion for View vs Table
Execution Plan Cost and SQL Expansion for View vs Table
  • As you can see from: Execution Plan Cost and SQL Expansion for View vs Table at the bottom in the Output List, the query optimizer has just replaced the view with actual select statement from the view.
  • The performance of the view is identical to the performance of the actual select statement against the tables.