Manage SQL Server views with T-SQL code

Scripts to manage SQL Server views: create, call, modify or delete views.

Views are a powerful tool for structuring and displaying data, but how do you manage SQL Server views with scripts? Here created with Transact-SQL scripts, they offer several advantages. Views allow for example an abstraction on the data of an MS SQL database. In other words, creating different views allows you to display different sets of data without changing the structure of the underlying tables.

Views can be created read-only and updated, and they can also be used to filter or aggregate data in a database. Views are useful for creating reports at an aggregated level. For example, to see the total revenue of all customers or the total orders of a company’s customers.

Table of Contents

1. Definition of SQL Server views

In SQL (Structured Query Language), a view is an object that allows you to select a set of data. This data set comes from other tables or other views, which are themselves based on tables. The selection is made on the columns and rows. This means that it is possible to:

Filter the rows vertically with a WHERE clause.
And select columns horizontally or generate them in the SELECT clause.

SQL views allow you to view all or part of the data contained in tables without directly accessing the content and structure of the table. They also allow you to aggregate data from multiple tables, by selecting columns with the same structures. The following are examples of code for creating, modifying, calling, or deleting simple views or partitioned views.

2. Creating the source table of the views

All the example scripts below are based on this sales table:

CREATE TABLE [dbo].[SALES] 
(
   [YearID] INT, 
   [MonthName] NVARCHAR(50), -- alphanumeric
   [MonthCurrent] BIT, -- Boolean, 0 or 1 , false / true
   [NumberMonth] TINYINT, -- very small integer, from 0 to 255
   [EmployeeNumber] SMALLINT, -- small integer, minimum -2^15, maximum 2^15
   [NumberOfClients] INTEGER, -- integer, minimum -2^31, maximum 2^31
   [NumberOfSales] BIGINT, -- big integer, minimum: -2^63, maximum 2^63
   [Amount_ET] NUMERIC(15,5), -- numeric, 15 digits, with 5 after the comma 
   [Amount_IT] DECIMAL(15,5) -- decimal, 15 digits, with 5 after the comma
);
GO

3. Create a SQL Server view in T-SQL

First, here is an example of a T-SQL script to create a simple view based on the sales table whose code is available above. Here is how to create a SQL Server view with a script to aggregate and display the monthly sales for all customer data in the table with the sales made.

CREATE VIEW [dbo].[V_Sales] 
AS 
SELECT 
   [MonthName], 
   [NumberOfClients], 
   [NumberOfSales], 
   [Amount_ET], 
   [Amount_IT] 
FROM    [dbo].[Sales];
GO

4. Modify a view with T-SQL code

Second, use this sample code to modify a view with ALTER VIEW. Here is an example of T-SQL code to modify a view. Check the existence of the view beforehand to avoid errors when executing SQL code in SSMS.

 -- Remove the NumberOfClients column from the view
ALTER VIEW [dbo].[V_Sales] 
AS 
SELECT
   [MonthName],
   [NumberOfSales],
   [Amount_ET], 
   [Amount_IT] 
FROM    [dbo].[Sales];
GO

Alter View with SQL Server

5. Drop a SQL Server view

However, to completely delete a view use the DROP VIEW command. Indeed, if the view does not exist, then the error Unable to find the object because it does not exist is displayed in the Database Management System (DBMS).

DROP VIEW [dbo].[V_Sales];
GO

6. Test if a SQL view exists to delete it without error

Indeed, it is advisable to test to avoid errors during the deletion of objects. To test the existence of views, use the IF EXISTS command and then the DROP command.

6.1 First option with versions older than SQL Server 2016

The script is executed in two steps using the IF test and the sys.objects view to check if an object of View type exists in the current database.

IF EXISTS (
  SELECT 1 FROM sys.objects
  WHERE  object_id = object_id(N'[dbo].[V_Sales]')
    AND type in (N'V') )
BEGIN 
  DROP VIEW [dbo].[V_Sales];
END;

6.2 Second option with MS SQL 2016 and newer

Simply use the DOP VIEW IF EXISTS in one unique line of code.

DROP VIEW IF EXISTS [dbo].[V_Sales];
GO

7. Create a Partitioned View using UNION ALL

Similarly, to combine data from multiple tables into a single partitioned view, use this example. In principle, with a simple select query combined with a data union. Note that the three selections on the tables (or views) must have the same structure. Here the goal is to combine three years of data in a unique view, called V_Sales_P.

CREATE VIEW [dbo].[V_Sales_P]
AS 
  SELECT [Year], [Month], [CustomerID], [SalesID], [Amount_ET], [Amount_IT]
  FROM [dbo].[Sales_2020]
  UNION ALL 
  SELECT [Year], [Month], [CustomerID], [SalesID], [Amount_ET], [Amount_IT]
  FROM [dbo].[Sales_2021]
  UNION ALL 
  SELECT [Year], [Month], [CustomerID], [SalesID], [Amount_ET], [Amount_IT]
  FROM [dbo].[Sales_2022]
;
GO

8. Create a SQL view with the encryption option

Thus encrypting a view simply hides the view code in the sys.comments system compatibility view. In this case, if the system uses Microsoft SQL replication, it does not publish the view.

The encryption option does not encrypt data, but only the structure.

 -- Remove the NumberOfClients column from the view
ALTER VIEW [dbo].[V_Sales_E]
WITH ENCRYPTION 
AS 
SELECT 
   [MonthName], 
   [NumberOfClients], 
   [NumberOfSales], 
   [Amount_ET], 
   [Amount_IT] 
FROM    [dbo].[Sales];
GO

This SQL Server tutorial explains how to manage views with T-SQL scripts. Views offer a lot of options and flexibility. It is therefore useful to use them extensively but useful in SQL development projects.

Create a SQL Server view with encryption