View is the simply subset of table which are stored
logically in a database means a view is a virtual table in the
database whose contents are defined by a query. View has lot of
advantage but it have some limitations as well.
Below are view limitations
1.
We can’t pass the parameter to a view.
Example:- here I am creating a simple view
create view
vAddress
as
select *
from Person.Address
|
View created successfully.
When we trying to create a parameterizes view
getting below error
create view
vAddress
@AddressID varchar(20)
as
select *
from Person.Address
|
Getting below error
2.
Rules and defaults can’t be associated with
view.
Views are virtual table it doesn’t tore anywhere.
We can’t implement the rules and default with view.
3.
The order by clause is invalid in view unless
Top or FOR XML is also specified
See the example:-
create view
vAddress
as
select *
from Person.Address
order by
1
|
Throwing below error
It means we need to use Top clause when we
are using order by clause in View. See below
4.
We can’t create Temporary table in view.
In view we can’t create a local or global
temp table. See the below example. Creating temp table
select *
into #TempAddress
from Person.Address
|
See the
result
Now creating a view using temp table.
It Mean we can’t create a view or function
using Temp table.
5.
We can’t perform outer joins in view.
6..An indexed view must be created with the SCHEMABINDING
option. This option prohibits the schema of the base tables from being
changed, for example adding or dropping a column.
7.
AGV, MAX, Min is not allowed.
8.
We can’t use ROLLUP, HAVING and CUBE in Group by
clause.
9.
All the tables referenced by the view must be in
the same database as the view.
10.
If you add any new column to a table tehn it
would not be reflected in the View until you won't run the
EXEC sp_refreshview 'ViewName'.
Don't use Select *, just use a select specific column names
It's a best practice to create a view with SCHEMABINDING using this, the base table will not be modified.
EXEC sp_refreshview 'ViewName'.
Don't use Select *, just use a select specific column names
It's a best practice to create a view with SCHEMABINDING using this, the base table will not be modified.
11.
We can't use count (*) in a view creation query.
