How to Create a View

In order to create a view, use the “Create View” statement using the following convention:

[cc lang=”sql”]
CREATE VIEW dbo.vStoreAccountNum
AS
SELECT AccountNumber
FROM Sales.Customer
WHERE CustomerType = ‘S’
[/cc]

Views are essentially stored SELECT Statements. They do not offer any speed advantage over regular SELECT statements. And they are not faster than a SELECT statement executed from within a stored procedure. They are mostly good for hiding complicated logic, or for providing an additional layer of abstraction from your source tables as a security measure. Along with a good indexing strategy, views can be used to simplify highly normalized tables.

The more joins within a view, and the more columns it returns back, the slower it is going to be. If you use views, try to make them only perform one function and only return back a finite number of columns. You don’t want views that return back a lot of columns when the majority of your queries only use one or two columns.

Here are some Do’s and Don’ts:

• Do use views if you want Data Analysts to access common queries.
• Do make sure all the join columns in a view contain indexes.
• Do Not call views from within views.
• Try not to return a lot of columns within a view if the view is referenced a lot.
• Do Not use SELECT * within a view.  Specify the columns names explicitly.

Indexed (or Materialized Views)

A materialized view is a view that has been indexed and has essentially been transformed into a dynamically updated table.  This kind of view requires a special clause called SchemaBinding which disallows changes to occur on the base tables it references.  An indexed view requires a column that can be used as a unique index, which in turn acts as the clustered index for which all the data is aligned.

I personally try not to create indexed views because of the potential for maintenance nightmares.  When schemabinding is placed on a view, none of the tables it references can be changed unless the view is dropped.  Once the base tables are modified, the view needs to be recreated.  The other issue I have found with indexed views, is the optimizers unwillingness to utilize them.  Instead, most of the time in order to get the optimizer to use the indexed view, I would have to specify the WITH NOEXPAND hint.  If I did not, then the optimizer would use the base tables instead.  This is cumbersome because in the cases I’ve needed to use an indexed view, my goal was to rename the original table referenced by many procedures, and create an indexed view in it’s stead, only to find I would have to modify the procedures also to use the NOEXPAND hint.

Nevertheless, they do have uses, for the most part however, I prefer using staging tables that I can maintain on my own, and that get updated on set schedules.

If you are going to use a schemabound view, specify the following:

[cc lang=”sql”]
CREATE VIEW dbo.vStoreAccountNum
WITH SCHEMABINDING
AS
SELECT AccountNumber
FROM Sales.Customer
WHERE CustomerType = ‘S’
[/cc]

Now that the view is schemabound, you need to create a unique clustered index on it.

[cc lang=”sql”]
CREATE UNIQUE CLUSTERED INDEX IDX_vStoreAccountNum ON dbo.vStoreAccountNum(AccountNumber)
[/cc]

Now the view is schema bound. Some notes on SchemaBound views.

1. They cannot contain non deterministic functions EX: GETDATE(), DateDiff()
2. You cannot use Count() instead use COUNT_BIG()
3. You cannot use UNION
4. You cannot use DISTINCT
5. You cannot use SubQueries
6. You cannot use Self-Joins
7. You cannot use Complex Aggregate function

As you can see, make sure not to code yourself into a corner using Indexed Views.