Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
in SQL Queries by 2 2 3

I am new to SQL Server, I need to know what're the main differences between Table and Views and when should I use views instead of tables?

below is a new table I have created in my database and I want to know when and why I should query view instead of the table?


1 Answer

0 like 0 dislike
by 23 26 40

View is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.

  • The query that defines the view can be from one or more tables or from other views in the current or other databases.
  • Views are generally used to focus, simplify, and customize the perception each user has of the database.
  • Views can be used used to reduce the complexity of the database and used for as security mechanisms by letting users access data through the view, without granting the users permission to directly access the underlying base tables of the view.
  • Views can be used to present aggregated data and hide detailed data.

Note :

  • If the view based on multiple tables and you will update the view it may not update the base table correctly.
  • You cannot pass a parameter to view
  • The ORDER BY clause is invalid in views
  • Views cannot be based on a temporary table

Here is the Example to create a view using SQL

Create View VW_ProductPrice_Less15
Select * from Product
Where Price < 15

To select data from view

Select * from VW_ProductPrice_Less15
If you don’t ask, the answer is always NO!