How to create and delete an MSSQL view.

Learn how to create and delete MSSQL View using SQL Server Management Studio and Transact-SQL.

Concept of MSSQL View.

• Data that is processed and displayed by the user by selecting all desired data from one or more tables (or other views) in the relational database.
• The view corresponds to the derivation of the relational model of the relational database, and the MSSQL View is primarily used for the following purposes.
• Data Restriction:` Displays limited access to only data that is allowed to the user.
• Processing data: You can refine or change data derived from the original table through a view and show it to the requester.
• Security: View allows you to expose only certain columns and hide sensitive data.
• For example, sensitive personal information allows you to view data through the MSSQL View.

How to create a View

How to create View through SQL Server Management Studio

1-1. Object explorer - Database- Views - Right-click - select new view.

1-2. Select the table to use for View.

1-3. Select a column to use for View.

1-4. Enter the name of the View you want to create and click the OK button.

1-5. Reload Object explorer.

Create View Create View using Transaction.

2-1. In the SQL Run window, type the source from which you can create a view and click EXCUTE.

USE [sampleDB]
GO
CREATE view [sampleViewTsql] AS
 SELECT [id]  
       ,[name]  
       ,[age]  
       ,[hp_number]  
       ,[writeDate]  
 FROM DBO.userInfo
; 

2-2. Check the view created in Object explorer.

How to delete View

Delete View through SQL Server Management Studio.

3-1. Object explorer - Database- Views - right mouse - Delete Select.

3-2. Click the OK button in the Delete Object.

Delete an MSSQL View created using Transact-SQL

4-1. In the SQL Run window, type the source, and click EXCUTE.

USE [sampleDB]
GO 
DROP View [userInfoFortsql]
GO

4-2. Refresh in the Object explorer, and make sure that the View has been dropped.

Clean up the MSSQL View

• Database View may have performance issues, but it is widely used in practice,
  so you need to be familiar with basic knowledge.
• MSSQL View has limitations when you change it using alter view differently from the usual table.
• MSSQL View has restrictions when you insert or delete data.
• MSSQL View restricts direct modification of data to logical areas.
• MSSQL View is often used to show some materials.