Kaynak:

Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.


GÖRÜNÜMLER (VIEWS)

Herhangi bir veritabanından istediğimiz kayıtları listeleyebilmek için sorgular kullanırız. Örneğin bir sorgu ile tüm çalışanlar içinden maaşı belli bir değerin üstünde olanları listemek isteyebiliriz. Sık kullanılan sorguları sürekli baştan yazmanın önüne geçmek için görünümler (views) geliştirilmiştir. Her defasında maaşı belli bir değerin üstünde olan çalışanları listeleyen sorguyu baştan yazmak yerine, bu sorgu bir görünüm (view) olarak tanımlanır ve gerektiğinde bu görünüm (view) tıpkı bir tablo gibi kullanılabilir. Aslında görünümler (view) içlerinde sadece sorgu tutan ve çağrıldıklarında bu sorguyu icra ederek kayıt döndüren sanal tablolardır.

Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.

Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.

Kaynak: Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.


Views
Creating Views

CREATE VIEW [Orders Qry] AS
SELECT O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate, O.RequiredDate,
O.ShippedDate, O.ShipVia, O.Freight, O.ShipName, O.ShipAddress, O.ShipCity,
O.ShipRegion, O.ShipPostalCode, O.ShipCountry,
C.CompanyName, C.Address, C.City, C.Region, C.PostalCode, C.Country
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID

CREATE VIEW MyTopCities AS
SELECT DISTINCT TOP 10 PERCENT ShipCity, ShipRegion
FROM Orders
ORDER BY ShipCity

Encrypt / Decrypt Views

CREATE VIEW MyTopCities
WITH ENCRYPTION
AS
SELECT DISTINCT TOP 10 PERCENT ShipCity, ShipRegion
FROM Orders
ORDER BY ShipCity

sp_helptext MyTopCities

The object comments have been encrypted.

Decrypt the View with the Public Domain Stored Procedure DECRYPT2K

EXEC dbo.DECRYPT2K MyTopCities,'V'

Updateable Views

CREATE VIEW FormaggiProductsView
AS
SELECT ProductID, ProductName, SupplierID
FROM Products
WHERE SupplierID = 14
WITH CHECK OPTION

Indexed Views


Using Views to Control User Access to Data



Posted: August 05, 2002




This tip is one of a series from SQL Server Magazine. For additional tips, visit the SQL Server Tips and Tricks Center.

Q. I have a Microsoft Access 2000 application that is populated by a back-end SQL Server 2000 database. To prevent Access users from viewing all the data in a SQL Server 2000 table, I want to use a view that displays only the data rows the user has been authorized to see. Can I create a view that limits user access to the SQL Server data?

A. Yes. You can create a view that limits user access to SQL Server data if each user logs in to Access with a unique user ID. The following sample statement creates such a view:

CREATE VIEW v_data AS
SELECT <column_list>
FROM dbo.mytable AS a
INNER JOIN dbo.authtable AS b
ON (a.Pkey = b.DataKey
AND b.userid = suser_sname())

This view restricts access based on userid. It requires you to maintain a table (authtable) with the user name matched to specific primary keys in the data table (mytable). If your situation is less complex and does not require you to manage row access for multiple users, you could insert the userid column into the data table, as the following code shows:

CREATE VIEW v_data AS
SELECT <column_list>
FROM dbo.mytable AS a
WHERE a.userid = suser_sname()

What Are Your Nested Views Doing?
by Brian Moran, brian@sqlmag.com

Nested views--views that are called by another view--invariably start off with the best of intentions. Theoretically, there's nothing wrong with using a nested view as long as you're aware of the performance implications. Theoretically, the optimizer eliminates the intermediate abstraction of a nested view, ending up with the same query execution plan it would have devised if nested views hadn't been involved. But in practice, nested views create more problems than they solve.

Let's look at example of how a nested view is born. Joe, a well-meaning developer, creates a perfectly good view that encapsulates a complex business rule--CustomerRule123. Joe's view, ShowCustomerInformation, joins three tables. A few weeks later, Sue sees the view and thinks, "Perfect. That's almost the exact data set I need for a report. I'll use ShowCustomerInformation as my base schema and simply add the other two joins I need." Sue thinks she can recreate the entire join in a new, top-level view. And she assumes that encapsulating the first view is beneficial in that it lets her report automatically pick up any changes to the logic in CustomerRule123. No problems, so far.

Alas, a few months later, someone else creates a new view, BeatsTheHeckOutOfMeData, that's now the first-level view, and Joe's original view, ShowCustomerInformation, is now buried five views down. This new view returns a result set that answers a simple query. However, if you print all the code that each nested view contains, you'll get 8 pages of SQL, and you'll see that the tangle of nested views joins multiple instances of tables together in ways that yield a horrible execution plan. This monster view takes 8 seconds to run.

Careful analysis might show that you could write the query based on BeatsTheHeckOutOfMeData as a clean four-way join that eliminates several messy and expensive rules--and returns in 250ms. But because of developer turnover during the past few months, nobody knows what BeatsTheHeckOutOfMeData is doing. The developers know only that the view returns the data users need. No one has time to detangle the messy web of nested views, and no one wants to change the inner views and risk breaking something somewhere else in the application. So, the monolithic monstrosity called BeatsTheHeckOutOfMeData lives on.

This example might seem extreme, but I often see similar situations at customer sites that regularly use nested views. When I'm called into help solve performance problems at such sites, I dig into my bag of tricks to find the queries that are consuming the most resources. I then run across an innocuous-looking query such as

SELECT CustomerId, PlusABunchOfOtherColumns
FROM BeatsTheHeckOutOfMeData
WHERE Id @TargetId

and I think to myself, "That looks like an easy enough query to tune." Imagine my disappointment when I understand the mess I've gotten myself into. My heart sinks even further when I ask what the nested view does and the developer I'm working with says, "Beats the heck out of me."

Views are an incredibly powerful tool in the database world when they're in the right hands. And nested views aren't inherently evil provided you consider the implications of the scenario I've outlined for you here. I'd rather avoid the problems caused by granting developers the right to directly create nested views. Trained DBAs and architects who can weigh the pros and cons of using a deeply nested view in a controlled manner might use them wisely. But I prefer to make nested views off-limits to the core development team for projects I have architectural responsibility over.

Kaynak:

Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.


Indexed Views in SQL Server 2000
DougCarpenter on 10/18/2000 in App Design
In this excellent article Doug covers the basics of creating an Indexed View. He also goes into detail on sizing considerations and when and when not to use an Indexed View.

--------------------------------------------------------------------------------

With SQL Server 2000 (Enterprise Edition), Microsoft has introduced the concept of Indexed Views, which can make your applications and queries run faster in the right circumstances.


Why Indexed Views?
Views have been available throughout the history of Microsoft SQL Server. However, using views that return very large result sets can lead to poor performance, as the result set is not indexed and the entire result must be table scanned if the view is used in a join or a subquery of a T-SQL command. Additionally, products like Oracle have come out with the concept of a Materialized View that give an additional performance boost by being able to have indexes built on a view. So in the continuing evolution of the SQL Server product line and in response to Oracle’s Materialized View, Microsoft SQL Server 2000 has a new feature called the View Index. View Indexes give the product the capability to define an index on a view. Additionally, SQL Server View Indexes are dynamic in that changes to the data in the base tables are automatically reflected in the indexed view. Also the SQL Server query optimizer will try to use an indexed view even if the view is not referenced in the from clause of a T-SQL command. These features are not available in Oracle’s Materialized Views.


Before SQL Server 2000, what was a View?
Typically a view is thought of as a virtual table, or a stored query. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views. This T-SQL select command is stored as a database object (a view). Developers can use the results from the view by referencing the view name in T-SQL statements the same way they would reference a real table. When referenced, the stored T-SQL that represents the view is merged with the referencing T-SQL code and executed to come up with the final results. Views have additional benefits of:


Views provide a security mechanism by subsetting the data by rows (All Active Customers, all customers in a certain state).

Views provide a security mechanism by subsetting the data by columns (Payroll fields not shown in the Employee Phone List View).

Views can simplify complex queries into a single reference. Complex Join operations that can make a normalized database design of several tables into a single row in the result set of the view. This is great for reporting tools like Crystal and Cognos.

Views give us aggregation capabilities (Min, Max, Count, Sum) where the data is not stored but calculated.

Views can create other calculated fields based on values in the real underlying tables.

Views can reference another view as one its “Base Tables”.

Views can hide the complexity of partitioned data (Sales from 1998 are in the 1998 table, Sales from 1999 are in the 1999 table, Sales from 2000 are in the Current Table) .

Views can be updateable in certain situations (only update to 1 of the base tables!).

Views do not incur overhead of additional permanent storage.
What are SQL Server 2000 Indexed views?
Views in SQL Server 2000 are very similar to those in previous versions with a few major exceptions when using Indexed views. When a clustered index is created on the view, SQL Server immediately allocates storage space to store the results of the view. You can then treat the view like any other table by adding additional nonclustered indexes.


What are the requirements for Indexed views?
There are several requirements that you must take into consideration when using Indexed views.

View definition must always return the same results from the same underlying data.

Views cannot use non-deterministic functions.

The first index on a View must be a clustered, UNIQUE index.

If you use Group By, you must include the new COUNT_BIG(*) in the select list.

View definition cannot contain the following

TOP

Text, ntext or image columns

DISTINCT

MIN, MAX, COUNT, STDEV, VARIANCE, AVG

SUM on a nullable expression

A derived table

Rowset function

Another view

UNION

Subqueries, outer joins, self joins

Full-text predicates like CONTAIN or FREETEXT

COMPUTE or COMPUTE BY

Cannot include order by in view definition


Notice that Indexed Views change the very essence of what a view was before this version of Sql Server. First, the data represented by the view is actually stored in the database. Secondly, the view definition must always return the same results for the same underlying data and all functions and expressions must be deterministic no matter what the current session settings.

To make sure that you can meet this requirement, the following session options must be set when you create an index view, when you modify any of the tables included in an indexed view or when the optimizer decides to use the indexed view as part of a query plan. Session Options that must be on
ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YEILDS_NULL

QUOTED_IDENTIFIERS
Session options that must be off
NUMERIC_ROUNDABORT
Functions like GetDate(), rand() are non-deterministic because of different session settings can return different values and the settings for one user may not be the same as for another. The list of deterministic and non-deterministic functions will be included in the final version of Books Online for SQL Server 2000. (Look for topic Deterministic and Nondeterministic Functions in the Books Online)

Besides these restrictions, the underlying tables that make up the view must be protected from schema changes. Part of the syntax of the create view command is the “with SCHEMABINDING” phrase. This is required to create a View Index and this will prevent the dropping or altering of tables participating in an Indexed View. Note that dropping the clustered index of an Indexed View will return it to the standard SQL view as it was as described above in the section Before SQL Server 2000, what was a View?

How do I create an Indexed View?
Make sure that session properties are properly set.

Create a deterministic view with new SCHEMABINDING syntax.

Create unique Clustered Index.

Optionally, create additional nonclustered indexes.
Below you will find the code that you can paste into the Sql Server Query Analyzer to test this yourself. This example is based on the Northwind sample database.

-- Use the northwind database
USE NORTHWIND
GO

-- Make sure that all of the session settings are set properly
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
go

-- Create the view, it must comply with the rules (deterministic)
CREATE VIEW PRODUCTS_BY_CUSTOMER WITH SCHEMABINDING AS
select customers.companyname,
products.productname,
sum(odetail.unitprice*odetail.quantity) as TotalPurchase,
count_big(*) as cnt
from dbo."order details" as odetail
inner join dbo.orders as omain
on omain.orderid = odetail.orderid
INNER join dbo.customers as customers
on customers.customerid = omain.customerid
INNER join dbo.products as products
on products.productid = odetail.productid
group by
customers.companyname,
products.productname
go

-- the following statement will cause an error if the view has not been
-- indexed
--EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'
--Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
--Views do not have space allocated.

-- Check to see if the indexes can be created
if ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1
BEGIN
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX PRODUCTS_BY_CUSTOMER_UNIQUE ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME, PRODUCTNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_1 ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_2 ON
PRODUCTS_BY_CUSTOMER(PRODUCTNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

END

Please note the ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1 in the above code listing. This command will tell you if all of the requirements for indexing a view have been met so that you can programmatically determine if a view can be indexed or not.

Also note that no space is allocated in the database for this view until the clustered index is created. If you try to use the SP_SPACEUSED stored procedure on a view that is not indexed, you get an error. The results of the SP_SPACEUSED commands that are sprinkled throughout the above code listing gives the following results on my test machine. # of Rows Data Index Total Used

After Clustered Index Created 1685 168 kb 16 kb 184 kb

After NonClustered Index 1 1685 168 kb 168 kb 336 kb

After NonClustered Index 2 1685 168 kb 320 kb 488 kb


How do I use the Indexed View?
You can use the view like you would any other view. Also, the SQL Server query optimizer will attempt to use a View Index even if the view is not referenced in the from clause, although you can override this behavior with the Expand Views hint.

From the sample created in the above code example, you could use the view as follows

Example 1: select * from products_by_customer

Example 1 above lets the query optimizer determine whether or not to use the view and its indexes or to use the base tables. Surprising on my test machine, this example uses the base tables, not the Indexed View. The query optimizer is a complex piece of technology but it isn’t always perfect. Based on my testing with this sample data in the Northwind database, I had to use the (noexpand) hint seen in the next example to force the optimizer to use the View Index. The speed of this on my test machine was about 3 times faster with 1685 records. By increasing the number of records in the base tables (orders 3000 records and order details 224,696 records), I found that the query optimizer did use the View Index without specifying the hint and the resulting query speeds where approximately 50 times faster. The # of records in the view, after adding all of these records in the base tables, was 1880 records. I conclude that the query optimizer with a small number of records in the base table (Orders had about 830 and order details had about 2155 records when I started) lean towards using the base tables instead of the View index. More testing would be needed to nail down the break even point but this just points out why the hints are still around and how much faster performance can be when the View Indexes are used.

Example 2: select * from products_by_customer with (noexpand)

Example 2 uses a hint to force the query optimizer to consider only the view and its indexes in the execution plan.

Example 3: select * from products_by_customer option (Expand Views)

Example 3 uses a hint to force the query optimizer to expand all indexed views into their underlying Select statements so the optimizer won’t consider any View Indexes in the execution plan.


When would I want to use a View Index?
If you have an application that is a Data-Mart, Data-Mining or decision-support type application, you can improve performance with View Indexes. Applications that do any of the following may benefit as well:
Joins and aggregations of big tables
Repeated patterns of queries
Repeated aggregations on the same or overlapping sets of columns
Repeated joins of the same tables on the same keys
Also, situations where you might consider de-normalizing a set of tables by storing aggregate information in the parent table may be good situations to consider creating an aggregate view on the child table and creating the appropriate View Index. In essence, the Indexed View replaces your de-normalized fields and all the work of keeping this de-normalized aggregate field up to date is taken care of by the database engine.


When would I NOT want to use a View Index?
You obviously cannot use a View Index if you need to include syntax in the view definition that is not allowed. It seems to me that Top, Min, Max, Count, using another view, union, subqueiries and outer joins are serious restrictions that would disqualify a large number of views that I might want to optimize using the View Index.

Storage may also be a major consideration as the data in the view is physically and permanently stored not only in its base table, but in the clustered index of the View Index. Effectively, the data is stored twice, once in its base table and once in the clustered index of the Indexed View.

Also, On-Line Transaction Processing systems (OLTP) will actually suffer performance loss if you try to use View Indexes. Databases with frequent inserts, updates, deletes suffer as these commands must not only process the table, but any associated Indexed Views that the base table is participating in. Also, views that are simply subsets of rows or columns with no aggregation or computation provide no benefit over a standard SQL view or a T-SQL command directly against the base tables. This additional overhead to update the data in the clustered index of the view I believe is the reason that the clustered index must be unique for an Indexed view. It uses this unique value to quickly update the appropriate record in the Indexed View. Without a unique index, the processing for updating the Indexed View records could be unacceptably long.


What are the performance benefits?
As I indicated earlier, I experienced query times 3 times quicker using the Indexed Views over the same query not using the Indexed Views on the sample data in the NorthWind database. With a much bigger data set and with the same database objects defined, I got query times as much as 50 times faster. Microsoft has reported performance improvements of 10 to 100 times with applications that access indexed views instead of base tables. I also experimented with using other queries that did not directly reference the Indexed View and got similar performance gains when the optimizer selected the Indexed View over other possible execution plans.


Summary
As you can see, even with its restrictions, the View Index is a powerful new tool in the SQL Server Developer’s toolbox. Because the optimizer can use a View Index, you won’t even have to change your existing T-SQL to take advantage of the performance benefits of the View Index. So take into consideration the information above when evaluating whether a View Index is right for your application.

Kaynak:

Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.


VIEW (GÖRÜNÜŞ) OLUŞTURMAK



Viewlerde tablolardaki bilgilere erişmenin bir yoludur. Viewler sayesinde çok kolonlu bir tablonun sadece belli kolonlarını listeletebilir ve kolonlar üzerinde hesaplamalar yaptırabilirsiniz. Örneğin tablonuzda diğer iki kolonun çarpımı olan yeni bir kolonu view ile rahatlıkla oluşturabilirsiniz. Gerçek tabloyla hiç ilişkisi olmayan tablolar oluşturup kullanıcıların bu tablo üzerinde işlem yapmasını sağlayabilirsiniz. Birden fazla tabloyu bir view ile birleştirebilirsiniz. Bu özellikle ilişkisel (relational) bir yapı kurduğunuzda oldukça yararlı olmaktadır. Şimdi notu 50'den büyük olan öğrencilerin listesini veren bir view oluşturalım.



CREATE VIEW Gecenler_View AS

SELECT Ad,Soyad,Ders_Adı,Ders_Notu

FROM Ogrenci

WHERE Ders_Notu>=50



Yukarıda Gecenler_View isminde Ogrenci tablosundan notu 50'den büyük ve eşit olan öğrencilerin listesini veren bir görünüm oluşturduk.

Viewlerin oluşturulmasında Select deyimini kullandık. Ancak viewlerin bazı kısıtlamaları vardır.

· ORDER BY,COMPUTE BY veya COMPUTE sözcükleri kullanılmaz.

· INTO kullanılmaz.



CREATE VIEW deyiminin CHECK ve WITH ENCRYPTION adında iki parametresi daha vardır. CHECK ile veri değişiklilerini kontrol edebilir, WITH ENCRYTION ile oluşturulan görünüm metnini şifrelenmesi(encryption) işlemini yapabilirsiniz.



CREATE personel_zam_view

WITH ENCRYTION

AS

SELECT Personel_Adı, Kademesi,Gorevi, Maası*1.2

FROM Personel

WHERE Kademesi=2



Yukarıda Personel tablosunda yer alan ve Kademesi 2 olan memurların maaşlarına %20 zam yaptık ve bu metni SQL Server'in syscomment tablosuna şifrelenmiş olarak kaydetmiş olduk.

Viewlerle güvenlik işlemini daha kolay sağlayabilirsiniz. Kullanıcılara view üzerinde izinler verdiğiniz zaman; onlara gerçek tablo üzerinde izin vermiş olmuyorsunuz. Diyelim ki sadece Öğrenci Adı ve Soyadı bilgilerini içeren bir view oluşturup, Öğrencilerin özel bilgilerini içermeyen bu tabloyu kullanıcılara açabilirsiniz
[/align]

Kaynak:

Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.

Make Your Security Access-Friendly


Try this trick to give user the permission they need to update SQL Server records via Microsoft Access.


Savvy DBAs know that keeping users out of your tables is one of the key steps towards keeping your data secure. Instead, you should assign data permissions to users via the views in your database. For example, you can give a particular user permissions to update a view, and they'll be able to change the data exposed by that view even without any permissions on the underlying tables. This gives you the flexibility to customize data access permissions or even change them on the fly by simply altering views, while avoiding giving users blanket permissions on tables. With views it's easy to enforce row- or column-level security in special situations.



But if you're using Access 2000 or Access 2002 data projects (ADPs) as a front-end to your SQL Server database, you'll find that there's a catch to this scheme. It turns out that Access won't make proper use of view-based permissions. Using an Access 2002 ADP, a SQL Server 2000 database, integrated Windows security, and a user with update permissions on a view but not on the underlying table, you'll find that the user can update the data just fine through SQL Query Analyzer, but not if they're using Access!


Don't despair. Here's a little-known trick to make this work. What's happening is that Access uses ADO to get to SQL Server, and ADO (and the underlying OLE DB layer) tries to optimize updates on views by sending the updates directly back via the underlying tables. This is fine (and fast) in most cases, but it totally defeats the security setup I've described. To make things work the right way, make a property setting that tells OLE DB to use the view itself to do the updates.


The trick: Add the WITH VIEW_METADATA clause to your view definition. You might define a view in the pubs database this way:


CREATE VIEW dbo.vwAllAuthors
WITH VIEW_METADATA
AS
SELECT *
FROM dbo.authors


It's the WITH VIEW_METADATA clause that does the trick.


I tested this fix in both Access 2000 and Access 2002 ADPs, and in both cases I can update data via a view even without permission on the underlying tables after making this change (provided, of course, that I've got permission on the view). One caution, though: The WITH VIEW_METADATA clause is new to SQL Server 2000—you won't be able to use this technique with older versions of SQL Server.

Mike Gunderloy, MCSE, MCSD, MCDBA, is a contributing editor for MCP Magazine. He is the author of numerous books and articles on database and development topics. You can contact Mike about "Make Your Security Access-Friendly" at mikeg1@larkfarm.com.
_________________

Kaynak:

Guests cannot see links in the messages. Please register to forum by clicking href="member.php?action=register">here to see links.


Professional SQL Server 2000 Programming Part 5 - Protecting Code: Encrypting Views
Powered By Beginners.co.uk


Category : SQL
Submitted By : Wrox Books

Published Date : 22nd February 2001
Viewed : 9821 times

From Book : Professional SQL Server 2000 Programming

If you're building any kind of commercial software product, odds are that you're interested in protecting your source code. Views are the first place we see the opportunity to do just that.




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.