The Properties of a View

After a view has been created, either by you or someone else, you may find out that it has an unnecessary column, it needs a missing column, it includes unnecessary records, or some records are missing. Fortunately, you can change the structure or the code of a view. This is referred to as altering a view. You have various options:

• To visually change a view, in the Object Explorer of Microsoft SQL Server Management Studio, you can right-click the view and click Design. In the Server Explorer of Microsoft Visual Studio, you can right-click the view and click Open View Definition.
  From the view window, you can add or remove the columns. You can also change any options in one of the sections of the window. After modifying the view, save it and close it
• To change the code of a view, in the Object Explorer of Microsoft SQL Server Management Studio, right-click it and click Edit. After editing the view's code, you can save it
• From the Object Explorer of Microsoft SQL Server Management Studio, you can right-click the view, position the mouse on Script View As -> ALTER To -> New Query Editor Window

The basic formula to programmatically modify a view is:

ALTER VIEW ViewName
AS
SELECT Statement

You start the alteration with the ALTER VIEW expression followed by the name of the view. After the name of the view, use the AS keyword to specify that you are ready to show the change. After the AS keyword, you can then define the view as you see fit. For example, you can create a SELECT statement that includes a modification of the existing code or a completely new statement.

In the view we created to show a list of men of a table, we included a column for the sex. This column is useless or redundant because we already know that the list includes only men. Here is an example of altering the view to remove (or rather omit) the Sex column of the Persons table:

ALTER VIEW dbo.ListOfMen
AS
SELECT dbo.Persons.FirstName, dbo.Persons.LastName
FROM   dbo.Sexes INNER JOIN dbo.Persons
ON     dbo.Sexes.SexID = dbo.Persons.SexID
WHERE  (dbo.Sexes.Sex = 'Male');

Instead of modifying a view, if you find it altogether useless, you can remove it from its database. You have various options. To delete a view:

• In the Object Explorer in Microsoft SQL Server Management Studio, right-click the name of the view and click Delete. The Delete Object dialog box would display to give you the opportunity to confirm your intention or to change your mind
• In the Object Explorer in Microsoft SQL Server Management Studio, right-click the view, position the mouse on Script View As -> DROP To New Query Editor Window
• In Microsoft SQL Server Management Studio, you can open an empty query window associated with the database that has the undesired view. From the Template Explorer, in the View node, drag Drop View and drop it in the query window
• In the Server Explorer in Microsoft Visual Studio, under the Views node of the database, you can right-click the view and click Delete. A message box would display, asking you whether you are sure you want to delete the view. You can decide to continue or change your mind

The formula to programmatically delete a view is:

DROP VIEW ViewName

On the right side of the DROP VIEW expression, enter the name of the undesired view and execute the statement. You will not be warned before the interpreter deletes the view. If you are programmatically creating a Windows application, of course you can use a conditional statement to assist the user with deciding whether to continue deleting the view or not.