Syntax and principles for using SQL View

Use Northwind//using the Northwind database

GO

CREATE VIEW Vwsample

As

SELECT CustomerID, CompanyName, ContactName from CUSTOMERS

GO

Use a View

SELECT * from Vwsample

Drop a View

DROP VIEW Vwsample

3.

Creating views with the Schemabind Option

With this option, the Shema of the table referenced in the view cannot be changed.

Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that MA Y Change the table schema.

Notice important points while creating a view with SCHEMABINDING OPTION:

• The objects should is referred to by their owner names [both part name].
• SELECT * is not permitted.

Here's an example for a view with the Schemabind OPTION:

CREATE VIEW Vwsample

With SCHEMABINDING

As

SELECT

CustomerID,

CompanyName,

ContactName

From DBO. CUSTOMERS--Double part name [Ownername.objectname]

GO

4.

Creating views with the encryption option (generally do not use this option)

This option encrypts the definition of the view. Users are not being able to see the definition of the View by 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 would be a stored in a encrypted format in the system table named ' syscomments '.

5.

Indexed views

Create index for a view

SQL SERVER allows an index to being created on a View. wow! Previous versions of SQL SERVER would not be allow-to-do. But one important point-to-be noted this is, the first index on the View should are a UNIQUE CLUSTERED index only. SQL SERVER "Do not allow" to "create any" other "index unless you" a UNIQUE CLUSTERED index defined on the view .

Let's check out a 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 would create a unique clustered index on the View.

6.almost any SQL can issued natively can is coded into a view; there is exceptions, however. For example, the UNION operator can is used in a view and you cannot create a trigger on a view. The union operation is not possible in view 7.The text of any view can is retrieved from the SQL Server system catalog using the system procedure sp_helptext (unles s The view is created specifying with encryption). For example, this statement: use the command to view the definition of the view (you can also use this command to view the definition of stored procedure) sp_helptext Sample_view might return T He following output: Text CREATE VIEW Sample_view as SELECT title, au_fname, au_lname from titles, titleauthor, authors WHERE TITL es.title_id=titleauthor.title_id and authors.author_id=titleauthor.author_idIt is also possible to rename a view using the system procedure sp_rename.
8.You should create a view when you are sure you want to use it.
Views should is created only when they achieve a specific, reasonable goal. Each view should has a specific application or business requirement the IT fulfills before it is created. That requirement should is documented somewhere, preferably in a data dictionary or repository.
There is seven primary uses for which views excel. These is:

1.Security:to provide row and column level security
Note:view can be called inuser_name () to obtain information about the currently logged-on user, so that each logged-on user sees a different view
2.Efficient Access: To ensure efficient access paths
The use of proper joins criteria and predicates on indexed columns can is coded into the view.
when creating a view, pay special attention to the efficiency of the SQL statement. 3.Complexity: To mask complexity from the user
4.Derived Data:To ensure proper data derivation
The view can contain the computed column, which is easy to 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 is 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 is accomplished without views
The final view usage situation might actually is the most practical usage for views-when views is the only solution!
9.needlessly create SQL Server objects that is not necessary. The cost of using viewin terms of views, for every unnecessary view this is created SQL Server would insert rows into the following system C Atalog Tables:syscolumns, syscomments, sysdepends, sysobjects, sysprocedures, and sysprotects. If Uncontrolled view creation is permitted, disk usage would increase, I/O problems can occur, and inefficient catalog Orga Nization may result.
View naming Convention "Therefore, it stands to reason that views should utilize the same naming conventions as is used for tables."But for background developers, it can be beneficial to include identities in the name of the view.
11.Always specify column Names-creating views SQL Server provides the option of specifying new Column Names for the View or defaulting to the same column names as the underlying base table (s). It is always advisable to explicitly specify view column names instead of allowing them to default,
Reference introduces the basic syntax of view http://www.sql-server-performance.com/nn_views.asp
(Good article, recommended!) Use view guidelines (when you should use view, some experience, naming etc 7) using views in Microsoft SQL Server, by Craig S. Mullins http://www.craigsmullins.co m/cnr_0299b.htm

Syntax and principles for using SQL View