Image of Navigational Map linked to Home / Contents / Search A Room with a View

by Brett Sheppard - GUI Computing
Image of Line Break

I recently joined a project where the database being used is SQL Server 7 and my excitement levels went through the roof when I came upon the numerous features it offered. Being a SQL Server virgin, unlike most of my colleagues here, it struck me that as as I muddle my way through it all, I would provide a bit of an insight into the learning curve of this (very) average programmer. So if you're a SQL guru, look away now, as they say on TV.

If not, then let's start this journey by looking first at SQL Server Views. I guess I could call this article Views 101 as I will try and explain what views are, why they are a great part of SQL Server, and how to go about making a simple view.

A view is like a virtual table. It doesn't contain data like a table but it does know what is required from it when it is called upon. The best and simplest definition I have heard for it is a "stored definition". When you create a view, SQL Server sorts out which tables are required, how they are joined, what columns and rows are needed and generally ensures that it has everything it needs if it is called. So why not just try and get the data on the fly through ADO or some similar method while your program is running? Basically, because if SQL Server is already aware of what is required by this view at design time, it will increase the speed of your program when it doesn't have to work out tasks such as the above, time and time again. Quicker processing time means everyone is happy. Also, the way in which SQL Server lets you join tables and create queries with views at design time is a great deal simpler than trying to get your head around a query with 4 inner joins for example.

A view also allows you to define a set of data that you want to use regularly from several different places. For example, you may have a table of orders and a table of clients, but whenever you retrieve an order (for printing, editing, cancelling or any other processing) you also want to get some basic client details for whichever client the order is for. Instead of writing the SQL to request this data in several places (perhaps in several different systems) you can define this data 'set' as a view on your server and that way it's easier to call and all your code can benefit from it.

Well, enough of me ranting and raving about the joys of views. Let's take a closer look at how you can create one of these babies in SQL Server 7.0. First, fire up your SQL Server Enterprise Manager.

Something along the lines of the screen above will appear at which time you go to your required database and select the views node. I am using the standard NorthWind database. Right clicking in the views window will give you a pop up window with which you can select New View.

You will be presented with four panes in alayout that is more than similar to the Query Designer in Access, as you can see.

The top pane is the Diagram Pane where you can add and remove the required tables for your view, select required fields for your view, and it also displays the relationships between your tables. The second pane is the grid pane that shows what fields you have selected for your view in tabular form. The third pane is the actual SQL statement and the fourth pane is the results pane that shows the records and columns you selected in the previous three panes.

So, let's create a view which lists all customers, and their orders, the date of the orders and the employee with who they dealt with. In the Diagram Pane add the Customers, Orders, Employees and OrderDetails Tables. You can see how SQL Server automatically shows the different relationships between the tables. Then, select CompanyName from Customers, the OrderDate from Orders, all Columns from OrderDetails and the First and LastName fields of Employees. As you do this, you will notice that changes are also reflected in the Grid Pane and the SQL Pane. You can also edit the SQL Pane to display the First and Last name of the employee as just one column instead of two by entering in the following : SELECT Employees.FirstName + ' ' + Employees.LastName AS [Full Name]

Now we are ready to see our resultant recordset. Select Run (The button with the Exclamation Mark) and if all goes well, the Results Pane will now be populated with the correct data. You can then save your view (remember, the data is not saved, just what SQL needs to know when you call this view from your code.) Now when you want to use this view while your program is running, your program will be sped up by the fact that SQL does not have to go through the process of working out what tables have to be joined together, is it a legal SQL statement etc.

So, how do you call this view from your program. Basically, the same way you would a table, or any SQL statement passed from the client app. Lets say I want to populate a combo box with the names of every employee who was included in my view using ADO.

Private Sub PopulateComboBox()
   Dim rs As New ADODB.Recordset
   Dim sSQL As String
   Dim sConn As String
   On Error GoTo PopulateComboBoxErr
   sSQL = "SELECT DISTINCT [Full Name] FROM vwWhackySQL"
   ' Your own connection string here.	
   sConn = "Provider=MSDASQL;DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;DATABASE=NorthWind"
   rs.Open sSQL, sConn, adOpenForwardOnly, adLockReadOnly, adCmdText
   While Not rs.EOF
      cboCrazyCombo.AddItem rs![Full Name]
   cboCrazyCombo.ListIndex = 0
   Set rs = Nothing

   Exit Sub
   MsgBox "Error in Sub : Ra Ra Ra", vbExclamation, "Error"
   Resume PopulateComboBoxEnd
End Sub

Nice and Easy. Views also allow you to include aggregate information such as Sum and Count. For example, in the example above, we could provide a Total Price by selecting the UnitPrice and the Quantity (both from the OrderDetails table) and aliasing it as TotalPrice. Now think how handy this would be if you had values from different tables that you wanted to manipulate together, or count the number of items in one table, depending on whether it met certain conditions from another table. It would make populating a grid with all your information easy as pie. One limitation with using views is that you cannot use OrderBy or Into keywords in your SQL statements so just be careful of that.

Another couple of more advanced uses with views is that you can use it to create sub queries. A subquery is a select query that returns a single value that is found inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Therefore you could do such things as existance checking on your views data. Also, you are not limited to just creating views on tables. Views can also be used to reference another view which means you can do tasks such as restricting information available to one type of user, and then have even greater restrictions placed on that view to a user with less permissions or less requirements to see a wider range of information.

There you go. Easy as falling of a log. I know this because I've fallen off a wide variety of logs. If you have never checked views out before I highly recommend taking the time and effort into exploring them further. I believe they make programming easier and they might just give your program that added zip we are all looking for.

Written by: Brett Sheppard
February '99

Image of Arrow linked to Next Article
Image of Line Break