All you have to do to encrypt your view is use the WITH ENCRYPTION option. This one has a couple of tricks to it if you're used to the WITH CHECK OPTION clause:

• WITH ENCRYPTION goes after the name of the view, but before the AS keyword
• WITH ENCRYPTION does not use the OPTION keyword

In addition, remember that if you use an ALTER VIEW statement, you are entirely replacing the existing view except for access rights. This means that the encryption is also replaced. If you want the altered view to be encrypted, then you must use the WITH ENCRYPTION clause in the ALTER VIEW statement.

Let's do an ALTER VIEW on our CustomerOrders_vw view that we created in Northwind. If you haven't yet created the CustomerOrders_vw view, then just change the ALTER to CREATE (don't forget to run this against Northwind):

ALTER VIEW CustomerOrders_vw
WITH ENCRYPTION
AS
SELECT  cu.CompanyName,
    o.OrderDate,
    od.ProductID,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS ExtendedPrice
FROM   Customers AS cu
INNER JOIN  Orders AS o
   ON cu.CustomerID = o.CustomerID
INNER JOIN  [Order Details] AS od
   ON o.OrderID = od.OrderID
INNER JOIN  Products AS p
   ON od.ProductID = p.ProductID

Now do an sp_helptext on our CustomerOrders_vw:

EXEC sp_helptext CustomerOrders_vw

SQL Server promptly tells us that it can't do what we're asking:

The object comments have been encrypted.

The heck you say, and promptly go to the syscomments table:

SELECT sc.text FROM syscomments sc
JOIN sysobjects so
 ON sc.id = so.id
WHERE so.name = 'CustomerOrders_vw'

But that doesn't get you very far either:



Note that I've chopped off the right hand side of this for brevity's sake, but I think you get the point - the data is pretty useless.

In short - your code is safe and sound. Even if you pull it up in EM you'll find it useless.

Make sure you store your source code somewhere before using the WITH ENCRYPTION option. Once it's been encrypted, there is no easy way to get it back. If you haven't stored your code away somewhere and you need to change it, then you may find yourself re-writing it from scratch.

About Schema Binding

Schema binding essentially takes the things that your view is dependent upon (tables or other views), and "binds" them to that view. The significance of this is that no one can make alterations to those objects (CREATE, ALTER) unless they drop the schema-bound view first.

Why would you want to do this? Well, there are a few reasons why this can come in handy:

• It prevents your view from becoming "orphaned" by alterations in underlying objects. Imagine, for a moment, that someone performs a DROP or makes some other change (even deleting a column could cause your view grief), but doesn't pay attention to your view. Oops. If the view is Schema Bound, then this is prevented from happening.
• To allow Indexed Views: If you want an index on your view, you must create it using the SCHEMABINDING option (We'll look at Indexed Views just a few paragraphs from now).
• If you are going to create a schema bound user defined function (and there are instances where your UDF must be schema bound) that references your view, then your view must also be schema bound.

Keep these in mind as you are building your views.

Making Your View Look Like a Table with VIEW_METADATA

This option has the effect of making your view look very much like an actual table to DB-LIB, ODBC and OLE-DB clients. Without this option, the meta-data passed back to the client API is that of the base table(s) that your view relies on.

Providing this metadata information is required to allow for any client-side cursors (cursors your client applications manages) to be updateable. Note that, if you want to support such cursors, you're also going to need to use an INSTEAD OF trigger.