Alter View with SQL Server

How to alter a SQL Server view with a T-SQL script? This code example allows you to modify an existing view. All you must do is use the common ALTER VIEW command. For example, to delete a column from the existing view or to add a column calculated from other columns in the source table.

Modify a SQL Server view with an Alter View script

Indeed, this example of a T-SQL script allows you to modify an existing view in a Microsoft database.

For example, to remove the Number of customers column, named NumberCustomers. So that it no longer appears in the list of columns in the new view.

The SQL Server V_Sales view therefore contains sales data. And the source code of the initial view is available in this article which explains how to create a SQL Server view. The source code of the view must be executed first to execute the following code without any error.

Execute the following code to alter the view as follows:

ALTER VIEW [dbo].[SalesByMonth] 
AS 
SELECT 
	[MonthName], 
	[NumberMonth],        
	SUM([NumberOfClients])   AS [NumberOfClients],      
	SUM([Amount_ET])      AS [Amount_ET],     
	SUM([Amount_IT])         AS [Amount_IT]
FROM [dbo].[Sales]
GROUP BY
	[MonthName], 
	[NumberMonth]
ORDER BY
	SUM([NumberOfClients]) DESC;

In fact, the columns are read from the source table. And a view makes it possible to select only the useful data.

Horizontally (with the selected columns) and vertically (with the filter on the data with the SELECT command).

Note that there is a very simple tip to make sure the select in the alter view script works perfectly. The goal is to develop the code view in two steps.

  • First is to test it as a simple selection till all columns are available and the select query is finished according to the project requirement.
  • Then integrate the select statement to the view script smoothly.

In conclusion, this simple T-SQL example allows you to change the list of columns used in the view.

If necessary, here is a script to perform a SQL Server insert or update also called a SQL Upsert.

Be the first to comment

Leave a Reply

Your email address will not be published.


*