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 VIEW 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.
SELECT *
FROM s
p_htk;
Updated VIEW
You can edit VIEW in SQL Server without deleting the new creation using the ALTER VIEW command.
Syntax
ALTER VIEW [ten_schema.] ten_view AS
[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}
SELECT bieu_thuc
FROM bang
WHERE d
ieu_kien;
For example
ALTER VIEWsp_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
You should read it
- The difference between web server and app server
- FUNCTION (Function) in SQL Server
- Create VPN Server on Windows 8
- How to decentralize users in MS SQL Server
- PROCEDURE (Procedure) in SQL Server
- Network basics: Part 3 - DNS Server
- Instructions for setting up and managing FTP Server on Windows 10
- Install Windows Server 2003 and create a backup server
May be interested
- Things to know about Samsung Smart View applicationhave 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 2017the 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 10if 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 8no 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?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 Serverin 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 Serverthis is a step by step guide to installing ms sql server.
- How to view RAM RAM on a PCview 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 2019from 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 servernetwork 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.