What is SQL view? Why use View instead of a Table? Advantages and Drawbacks

What is SQL view?

The View is a virtual table, which not create physically, it create logically only. We insert, update & delete the records from a view.

In view there are no any physical relations in the database and the view is a virtual table based on the result set of an SQL statement.

Why use View instead of a Table?

A table contains data but the View dose not stored set of data values in a database and the view is only a SELECT statement.

A view can combine columns/rows from multiple tables.

Views are work like a security layers and we can handle security issues.

Views can be used as security mechanisms and its display only those data that you granting the permissions in this view.

Advantages of View:-

We can hide so of table columns.

Views can model complex joins easily.

Views are work like a security layers and we can handle security issues.

Drawbacks of View: -

When table is dropped or modified, view becomes inactive; it depends on the table objects.

If we querying from views, it will takes more time than directly querying from the tables.

We can lose information about relations (primary, foreign keys).

How to create View “Virtual Table”?

Syntax:-

-- CREATE VIEWS
CREATE VIEW View_Name 
AS
    SELECT Column_Name
    FROM Table_Name
WHERE your_Conditions

Example for Create View:-

-- CREATE ACTIVE CUSTOMERS VIEWS
CREATE VIEW vw_Active_Customers
AS
    SELECT Id, Name, CreatedOn, UpdatedBy
    FROM [dbo].[Customer]
WHERE IsCurrent =  1

-- SELECT ACTIVE CUSTOMERS VIEWS
SELECT * FROM [dbo].[vw_Active_Customers]

-- DROP VIEW 
DROP VIEW [dbo].[vw_Active_Customers]