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 example

 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 example

 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

Syntax

 DROP VIEW ten_view; 

ten_view

VIEW's name wants to delete.

For example

 DROP VIEW sp_htk; 

This DROP VIEW command will delete VIEW named sp_htk in SQL Server.

Previous article: DROP TABLE command in SQL Server

The following article: GLOBAL TEMPORARY TABLE in SQL Server

5 ★ | 2 Vote

May be interested

  • Things to know about Samsung Smart View applicationThings to know about Samsung Smart View application
    have you ever wondered what is samsung smart view? if the answer is yes, please join us to find out details about this application through the following article.
  • New points in SQL Server 2017New points in SQL Server 2017
    the sql server 2017 version is primarily connected to linux, bringing the power of sql to linux. in short, you can install sql server 2017 on linux, using sql server 2017 on linux-based docker containers. sql server 2017 also allows you to choose development languages, develop it on-premise or cloud-based.
  • Instructions for setting up and managing FTP Server on Windows 10Instructions for setting up and managing FTP Server on Windows 10
    if you want to create a private cloud for sharing and converting large files without restrictions, you can create an ftp server (file transfer protocol server) on your windows 10 computer.
  • Create VPN Server on Windows 8Create VPN Server on Windows 8
    no need to install any additional applications, you can easily 'turn' your computer into a vpn server if you're using windows 8. in this way, you can share data from the computer. as a simple lan system in the form of remote access. & a
  • What is the future of server virtualization?What is the future of server virtualization?
    server virtualization can help combat poor server performance, make better use of computing capabilities, limit energy consumption and improve data center flexibility.
  • Learn about the architecture of MS SQL ServerLearn about the architecture of MS SQL Server
    in the previous articles, you already know briefly about sql server, how to install sql server on the computer. in this section we will learn about the architecture of sql server.
  • Instructions for installing MS SQL ServerInstructions for installing MS SQL Server
    this is a step by step guide to installing ms sql server.
  • How to view RAM RAM on a PCHow to view RAM RAM on a PC
    view ram ram on your computer is very simple, just a few simple steps, you can monitor all ram parameters, not just bus ram. today, software tips will introduce you how to view ram bus on computers, laptops with the fastest and most accurate way.
  • How to install DNS Server on Windows Server 2019How to install DNS Server on Windows Server 2019
    from microsoft, the domain name system (dns) is one of the industry standard protocol sets that includes tcp / ip, along with dns client and dns server that provide name resolution services that map names to the ip addresses of computers. .
  • Install Windows Server 2003 and create a backup serverInstall Windows Server 2003 and create a backup server
    network management documentation server 2003 as details of how to install, create a companion server in case the main server is malfunctioning, create a domain, join the client to the domain, the conection {remote desktop from the client data to server}, set adsl router parameters to server, turn off 1 working machine immediately.