SQL view syntax and principles

1.
View is only the stored SQL statement
Views are nothing
But saved SQL statements, and are sometimes referred as "virtual tables ".

Keep in mind that views cannot store data (partition t for indexed views); rather
They only refer to data present in tables.

2.
Create a view

Use northwind // use the northwind database

Go

Create view vwsample

As

Select customerid, companyName, contactname from
MERs

Go

Use a view

Select * From vwsample

Drop a view

Drop view vwsample

3.

Creating views with
Schemabind Option

After this option is used, the Shema of the table referenced in the view cannot be changed.

Creating a view
The schemabinding option locks the tables being referred by the view and
Prevents any changes that may change the table schema.

Notice two important
Points while creating a view with schemabinding option:

• The objects
  Shocould be referred to by their owner Names [two part name].
  
  
• Select * is not
  Permitted.

Here's an example
A view with the schemabind option:

Create view vwsample

With schemabinding

As

Select

Customerid,

CompanyName,

Contactname

From DBO. Customers -- two part name [ownername. objectname]

Go

4.

Creating
Views with
Encryption option (this option is generally not used)

This option encrypts
The definition of the view. users will not be able to see the definition
The view after it is created.

Use northwind

Go

Create view vwsample

With Encryption

As

Select

Customerid,

CompanyName,

Contactname

From DBO. Customers

Go

Select *

From syscomments

Where ID from sysobjects where xtype =
'V' and

The view definition
Will be stored in an encrypted format in the system table named 'syscomments '.

5.

Indexed views

Create an index for a view

SQL Server 2000
Allows an index to be created on a view. Wow! Previous versions of SQL Server
Will not allow you to do this. But one important point to be noted here is
That the first index on the view shocould be a unique clustered index only.
SQL Server 2000 will not allow you to create any other index unless you have
An unique clustered index defined on The View.

Let's check out
Sample example for an indexed View:

Create view vwsample

As

Select

Customerid,

CompanyName,

Contactname

From DBO. Customers

Go

Create unique clustered index indclustered

On northwind. DBO. vwsample (customerid)

Go

The above statement
Will create a unique clustered index on The View.

6.

Almost any SQL that can be issued natively can be coded
Into a view; there are exceptions, however. For example,
The Union operator can not be used in a view and you
Cannot create a trigger on a view.
View cannot perform Union operations

7.
The text
Of any view can be retrieved from the SQL Server System
Catalog using the system procedure sp_helptext (unless
The view was created specifying with encryption).
Example, this statement:
 
Use the command to view the view definition (you can also use this command to view the definition of Stored Procedure)
Sp_helptext sample_view

Might return the following output:

Text
Create view sample_view
As select title, au_fname, au_lname
From titles, titleauthor, authors
Where titles. title_id = titleauthor. title_id
And authors. author_id = titleauthor. author_id

It is also possible to rename a view using the System
Procedure sp_rename.

8. You should create a view only when you are sure to use it.
Views shoshould be created only when they achieve
Specific, reasonable goal. Each view shoshould have
Specific application or business requirement that it
Fulfills before it is created. That requirement shocould be
Incluented somewhere, preferably in a data dictionary or
Repository.
There are seven primary uses for which views excel. These are:

1. Security : To provide row and column level security
Note: You can call User_name () to obtain the information of the current login user, so that each login user can view different views
2. Efficient access : To ensure efficient access paths
The use of proper join criteria and
Predicates on indexed columns can be coded into the view.

When creating a view, pay special attention to the efficiency of SQL statements.
3. Complexity : To mask complexity from the user
4. Derived Data: To ensure proper data derivation
View can contain computed columns for ease of use.
5. Domain support : To provide domain support
A domain basically
Identifies the valid range of values that a column can
Contain.

Some of the functionality of domains can be implemented
Using views and the with check option clause.
6. Column renaming : To rename columns, and
7. Single solution View: To provide solutions which can not be accomplished without views

The final view usage situation might actually be the most
Practical usage for views-When views are the only
Solution!

9.
Do
Not needlessly create SQL Server objects that are not
Necessary.
Cost of using View

In terms of views, for every unnecessary view that is
Created SQL server will insert rows into the following
System catalog tables: syscolumns, syscomments,
Sysdepends, sysobjects, sysprocedures, and sysprotects.
If uncontrolled view creation is permitted, disk usage
Will increase, I/O problems can occur, and inefficient
Catalog organization may result.

10. View naming convention
"
Therefore, it stands to reason that views shocould utilize
The same naming conventions as are used for tables ."
However, it may be advantageous for background developers to add an identifier to the view name.

11.
Always specify column names
When creating views SQL Server provides the option of specifying new
Column names for the view or defaulting to the same column names as
Underlying base table (s). It is always advisable to explicitly specify
View column names instead of allowing them to default,

Reference
Describes the basic view syntax.
Http://www.sql-server-performance.com/nn_views.asp

(GoodArticle, Recommended !)Guiding principles for using view (when to use view, some experience, and naming)
Using views in Microsoft SQL Server, by Craig S. mulig
Http://www.craigsmullins.com/cnr_0299b.htm