Limitations of view

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.

11.   We can't use count (*) in a view creation query.