Limitations of View in SQL Server 2008

Hi Folks,

This article is quite related to View Limitation in SQL Server 2008. Although it's one of the core features of SQL Server, there are many limitations associated with it.

A few that I have encountered are listed below:

1. You can't create a parameterized view, in other words you can't create a view with a parameter.
   For example:
   
   CreateView vw_OrdersNorthwind
   @OrderID  int
   As
   select CustomerID,ShipCity,ShipCountryfrom orders orderby OrderID desc
   go
   
   Error: It will give you an incorrect syntax error.
    
2. Views are not based on temporary tables, if we try to create one then it gives us a massage.
   
   An example is as follows:
   
   Step 1: Temp table creation
   
   createtable ##MobileDetails
   ( ID int NOTNull ,MobileNamenvarchar(50),CompanyName nvarchar (50))
   
   Step 2: Insert a few records into the TempTable:
   
   insertinto  ##MobileDetailsvalues (1,'Galaxy S2','Samsung')
   insertinto  ##MobileDetailsvalues (1,'Nokia Lumia','Nokia')
   insertinto  ##MobileDetailsvalues (1,'IPhone5','IPhone');
   insertinto  ##MobileDetailsvalues (1,'Blackberry Z10','Blackberry');
   
   Step 3: Creation of a view on the TempTable.
   
   createview vw_onTempTable
   as
   select MobileName,CompanyNamefrom ##MobileDetails
   go
   
   An error prompted by the SQL Server after running the preceding command.
   
   Msg 4508, Level 16, State 1, Procedure vw_onTempTable, Line 3
   Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
    
3. You can't use an order by clause at the time of view creation. Kindly have a look at an example below:
   
   Let's run the following command in the query editor of SQLServer:
   
   CreateView vw_OrdersNorthwind
   As
   select OrderID,CustomerID,ShipCity,ShipCountryfrom orders orderby OrderID desc
   go
   
   It issues the error:
   
   Msg 1033, Level 15, State 1, Procedure vw_OrdersNorthwind, Line 3
   The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
   
   It clearly specifies to use TOP or FOR XML in your TSQL.
   
   Now I make some changes in the preceding query and run it again.
   
   CreateView vw_OrdersNorthwind
   As
   selecttop 100 OrderID,CustomerID,ShipCity,ShipCountryfrom orders orderby OrderID desc
   go

   Now select the records from the View and run this query.
   
   Select* from vw_OrdersNorthwind
    
   
   

4. All the tables referenced by the view must be in the same database as the view.
5. 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.
6. If you add any new column to a table tehn it would not be reflected in the View untill you won't run the 
   
   EXEC sp_refreshview 'ViewName'.
   
   Don't use Select *, just use a select specific columnnames
   It's a best practice to create a view with SCHEMABINDING using this, the base table will not be modified.
    
7. You can't use count (*) in a view creation query, for example:
   
   CreateView vw_OrdersNorthwind
   As
   --select OrderID,CustomerID,ShipCity,ShipCountry from orders 
   selectcount(*) from orders
   go
   
   It also forces you to supply any column value.
   
   Msg 4511, Level 16, State 1, Procedure vw_OrdersNorthwind, Line 4
   Create View or Function failed because no column name was specified for column 1.

        Workaround of this issue is  use the following syntax:

CreateView vw_OrdersNorthwind
As
--select OrderID,CustomerID,ShipCity,ShipCountry from orders 
select count(*) As Total from orders
go

select * from vw_OrdersNorthwind