Views in SQL Server 2000

Now when we know what a view is, and when it’s a good idea to use one, let’s create one. And if you thought it was difficult, you will get surprised. To create a view, you actually don’t have to know more than how to select data from a table.

CREATE VIEW [< owner >.] view_name [ ( column [ , …n ] ) ]
[ WITH < view_attribute >[ , …n ] ]
AS
select_statement
[ WITH CHECK OPTION ]

< view_attribute > ::=
        { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

Create a simple view

Before we create our first view, we should know some rules about the select statement. It cannot:

Also, not everyone can create a view. You must be a member of the fixed database roles db_owner or db_ddladmin. Or, a member of the sysadmin server role, or db_owner database role must have given you permissions to create a view by running this:


GRANT CREATE VIEW TO [SqlServer01\Kristofer]

The above statement will grant Kristofer on the server SqlServer01 permissions to create views.

I thought it would be a good idea to create the vwProductSales I used earlier as an example, and that is also the one you can see in the picture in the Introduction section. So, start Query Analyzer, and logon as a user with permissions to create a view. Then run this:


USE Northwind
GO
CREATE VIEW vwProductSales
AS
        SELECT Products.ProductName, SUM(OD.UnitPrice*OD.Quantity*(1-OD.Discount)) AS Total
        FROM [Order Details] AS OD
        INNER JOIN Products ON OD.ProductID = Products.ProductID
        GROUP BY Products.ProductName
GO

As you can see, it’s not difficult at all! It’s basically a SELECT statement.

Now, say that we by some reason want this ordered by the Total column. Again, think about a view as a virtual table, so there is actually no reason to do it (do you usually sort your data in your tables when you insert data? Me neither!), but just for fun:

USE Northwind
GO
CREATE VIEW vwProductSalesSorted
AS
        SELECT Products.ProductName, SUM(OD.UnitPrice*OD.Quantity*(1-OD.Discount)) AS Total
        FROM [Order Details] AS OD
        INNER JOIN Products ON OD.ProductID = Products.ProductID
        GROUP BY Products.ProductName
        ORDER BY Total DESC
GO

Output:
Server: Msg 1033, Level 15, State 1, Procedure vwProductSalesSorted, Line 7 The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

As you can see by the output, we were not allowed to do this. On the other hand, if we were only interested in the best selling products, we could write this:

USE Northwind
GO
CREATE VIEW vwProductSalesTop10
AS
        SELECT TOP 10 Products.ProductName, SUM(OD.UnitPrice*OD.Quantity*(1-OD.Discount)) AS Total
        FROM [Order Details] AS OD
        INNER JOIN Products ON OD.ProductID = Products.ProductID
        GROUP BY Products.ProductName
        ORDER BY Total DESC
GO

As you can see, this worked. We did not violate the rules of a view, and this was also one of the advantages of using a view, to select only the data we were interested in.

I’ve told you several times that a view is a virtual table, and the only thing that is stored in the database is the select statement. It is in fact stored in the Syscomments system table. And to prove this, run this statement:

USE Northwind
GO
SELECT [text] 
FROM syscomments 
WHERE id = OBJECT_ID('vwProductSales')

Note: If you did not get the same result, it might be because Query Analyzer is configured to return results to grids. You can change it to return result to text from Tools->Options, and then click on the Results tab.

As you can see, there it is, our view! But of course, Microsoft do not force us to write all this to get information about a view (or a rule, a default, an unencrypted stored procedure, user-defined function, trigger). The system stored procedure sp_helptext does this for us.

EXEC sp_helptext vwProductSales

The same goes for sp_depends, which gives us information about what other objects our view depends on.

EXEC sp_depends vwProductSales

With Encryption

As we saw, it is very simple to see the actually code for the view. Usually, this is fine, but say that you develop an e-commerce system, probably using a database, with some views. You ship it to your customer, but you do not want them to be able to see the code for the view. Then you can use the ENCRYPTION attribute. In fact, all objects containing code can be encrypted. So, let’s create an encrypted version of our view above.

USE Northwind
GO
CREATE VIEW vwProductSalesEncrypted
WITH ENCRYPTION
AS
        SELECT Products.ProductName, SUM(OD.UnitPrice*OD.Quantity*(1-OD.Discount)) AS Total
        FROM [Order Details] AS OD
        INNER JOIN Products ON OD.ProductID = Products.ProductID
        GROUP BY Products.ProductName
GO

EXEC sp_helptext vwProductSalesEncrypted

Now you think that this is secure, and your customer will never be able to find out the code for our encrypted view. You couldn’t be more wrong. But it is beyond the scope of this article to show you how to decrypt the view; I just wanted to tell you that it is possible.

Views in SQL Server 2000, Part 2

Create a view

Create a simple view

With Encryption