MS SQL Server: VIEW in SQL Server

The article explains how to create, update and delete VIEW in SQL Server with syntax and examples.

What is VIEW in SQL Server?

Basically, VIEW is a virtual table that doesn't really exist in SQL Server. It is generated by a query that combines 1 or more tables.

Create VIEW in SQL Server

Syntax

 CREATE VIEW  [ten_schema.]ten_view AS 
  [WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA} 
  SELECT bieu_thuc 
  FROM bang 
  [WHERE dieu_kien ]; 

ten_schema

This is the name of the schema (translating as schema or namespace) that the table belongs to.

ten_view

Name of VIEW wants to create.

ENCRYPTION

Encrypt the text of the ALTER VIEW command in sys.syscomments.

SCHEMABINDING

Make sure the table definitions are not modified to not affect VIEW.

VIEW_METADATA

Ensure SQL Server has metadata of VIEW.

bieu_thuc

Calculated columns or values want to add to VIEW.

state

Table definition VIEW. Must have at least 1 table in the FROM clause.

WHERE dieu_kien

Option. Conditions must meet so that the record is displayed in VIEW.

For instance,

 CREATE V IEW sp_htk AS 
  SELECT sanpham.id_sanpham, sanpham.ten_sanpham, hangtonkho.chatluong 
  FROM sanpham 
  INNER JOIN hangtonkho 
  ON sanpham.id_sanpham = hangtonkho.id_sanpham 
  WHERE sanph am.id_sanpham> = 1000; 

The above CREATE VIEW command will create a virtual table based on the result set of the SELECT statement. This VIEW will be named sp_htk.

You can now query VIEW as below.

 SELE CT * 
 FROM s p_htk; 

Updated VIEW

You can edit VIEW in SQL Server without deleting the new creation using the ALTER VIEW command.

Syntax

 ALT ER VIEW [ten_schema.] ten_view AS 
  [WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA} 
  SELECT bieu_thuc 
  FROM bang 
  WHERE d ieu_kien; 

For instance,

 ALTER VIEW  sp_htk AS 
  SELECT sanpham.ten_sanpham, hangtonkho.soluong 
  FROM sanpham 
  INNER JOIN hangtonkho 
  ON sanpham.id_sanpham = hangtonkho.id_sanpham 
  WHERE sanpham.id_sanpham >= 500 
  AND sanpham.id_san pham <= 1000; 

For example, the above ALTER VIEW command will update VIEW named sp_htk without deleting. VIEW needs to exist before it can execute the update command.

Delete VIEW