The only differences between using the ALTER VIEW statement and the CREATE VIEW statement are:

• ALTER VIEW expects to find an existing view, where CREATE doesn't
• ALTER VIEW retains any permissions that have been established for the view
• ALTER VIEW retains any dependency information

The second of these is the biggie. If you perform a DROP, and then use a CREATE, you have almost the same effect as using an ALTER VIEW statement. The problem is that you will need to entirely re-establish your permissions on who can and can't use the view.

Dropping Views

It doesn't get much easier than this:

DROP VIEW <view name>, [<view name>,[ ...n]]

And it's gone.

Creating and Editing Views in EM

For people who really don't know what they are doing, this has to be a rather cool feature in EM. Building views is a snap, and you really don't have to know all that much about queries in order to get it done.

To take a look at this, fire up EM, open up the Northwind database sub-node of the Databases node and right-click on Views:

Now select New View...:

This View Builder is part of the daVinci tools that became part of SQL Server back in 7.0. There are four panes - each of which can be independently turned on or off:

• The Diagram pane
• The Grid pane
• The SQL pane
• The Results pane

For those of you who have worked with Access at all, the Diagram pane works much as it does in Access queries. You can add and remove tables, and even define relationships. Each of those added tables, checked columns, and defined relationships will automatically be reflected in the SQL pane in the form of the SQL required to match the diagram. To identify each of the icons on the toolbar, just hover your mouse pointer over them for a moment or two, and you will get a Tooltip that indicates the purpose of each button.

For demonstration purposes, add the Customers, Orders, Order Details, and Products tables.

You can add tables either by right-clicking in the Diagram pane (the top one in the picture on the previous page) and choosing Add Table or by clicking on the Add table toolbar button (the rightmost one). Note that, for version 7.0, in order to be able to add a table, the Diagram pane must be the active pane. If in doubt, just click your mouse with the pointer somewhere inside the Diagram pane - the Add table toolbar button should then become enabled (if it wasn't already). This works just fine in 2000 without worrying about which pane is active.

Note that I turned off the Grid, SQL, and Results panes to save space here. I then went through and checked the boxes for the columns that I wanted to include. If I had the Grid pane up, then you would have seen each column appear in the Grid pane as I selected it. With the SQL pane up, you would have also seen it appear in the SQL code.

In case you haven't recognized it yet, we're building the same view that we built as our first complex view (CustomerOrders_vw). The only thing that's tricky at all is the computed column (ExtendedPrice). To do that one, either we have to manually type the equation into the SQL pane, or we can type it into the Column column in the Grid pane along with its alias:

When all is said and done, the view builder gives us the following SQL code:

SELECT dbo.Customers.CompanyName, dbo.Orders.OrderDate, 
   dbo.[Order Details].ProductID, dbo.[Order Details].UnitPrice,
   dbo.[Order Details].Quantity, dbo.Products.ProductName, 
   dbo.[Order Details].Quantity * dbo.[Order Details].UnitPrice AS 
      ExtendedPrice
FROM  dbo.Customers INNER JOIN
   dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID 
     INNER JOIN
   dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order 
      Details].OrderID
     INNER JOIN
   dbo.Products ON dbo.[Order Details].ProductID =
     dbo.Products.ProductID

While it's not formatted the same, if you look it over, you'll find that it's basically the same code we wrote by hand!

If you've been struggling with learning your T-SQL query syntax, you can use this tool to play around with the syntax of a query. Just drag and drop some tables into the Diagram pane, select the column you want from each table, and, for the most part, SQL Server will build you a query - you can then use the syntax from the view builder to learn how to build it yourself next time.

Now go ahead and save it as CustomerOrders2_vw and close the view builder.

Editing Views in EM

To edit a view, we have a couple of choices.

First, we can just double-click on it. If we double-click on our new CustomerOrders2_vw view, we'll get something a little different from we might expect at this point:

We're back to straight code. From a user interface standpoint, this stinks. Not because we're back to code, but that we have an inconsistent interface. Oh well, again - they didn't consult with me first...

You can edit the code as you see fit, then just click OK or Apply in order to make the change.

Your other option is probably going to be a bit more appealing - it's back to the editor we used the first time around. To use this, just right-click on your view and choose Design View. You'll be greeted with the same friendly query designer that we used with our query when it was created.