View in sql server

what is View in sql server ::

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object.


Limitations of Views

There are some limitations when using Views. They are:

* SELECT INTO cannot be used in the View

* Temporary tables cannot be used within the View

* Parameterized views does not exists i.e., you cannot pass parameters to the Views

* COMPUTE & COMPUTE BY clauses cannot be used within the View

Views and User-Defined Functions almost serve the same purpose. But the major difference is that User-Defined Function can accept parameters, whereas Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.
Syntax
CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]

AS select_statement [WITH CHECK OPTION]
Performance tuning

While views are often convenient to use, especially for restricting users from seeing data they should not see, they aren't always good for performance. So if database performance is your goal, avoid using views (SQL Server 2000/2005 Indexed Views are another story).
Views can slow down queries for several different reasons. For example, let's look at these two SELECT statements:
SELECT * FROM table_name

SELECT * FROM view_name
Which is faster? If you test it, you will find that the first SELECT statement is faster, although the execution plan for both of them will be the same. How can that be? This is because it takes SQL Server extra work (such as looking up data in the system tables) before it can execute the view. This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.
Another way views can hurt performance is when JOINs or UNIONs are used, and you don't intend to use all of the columns. This results in SQL Server performing unnecessary work (such as an unnecessary JOIN or UNION), slowing down the performance.
Views, like stored procedures, once they are run the first time, are optimized and their execution plan is stored in cache in case they need to be reused. But this is not reason enough to use a view.
Views, besides hurting performance, are not all that flexible when you are working with them. For example, they can't be changed on the fly, they can’t be used to sort data, and using them for INSERTs, UPDATEs and DELETEs is problematic. In addition, while views can be nested, this just compounds their problems, so avoid doing this.
Instead of using views, use stored procedures instead. They are much more flexible and they offer better performance.

Cheers,
Sid.