Limitations of Views in SQL Server

1. You can't create a parameterized view, in other words you can't create a view with a parameter.
2. We can't create view on temporary table.
3. All the tables referenced by the view must be in the same database as the view.
4. 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.
5. If you add any new column to a table then it would not be reflected in the View until you won't execute the below queryEXEC 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.
    
6. You can't use count (*) in a view creation query, for example:

CREATE VIEW vwEmployeeDetails

AS

SELECT COUNT(*) FROM Employee

GO

 Error Message : Msg 4511, Level 16, State 1, Procedure vwEmployeeDetails,        

 Line 10

 Create View or Function failed because no column name was specified for column 1.

Workaround for this issue shown below.

          CREATE VIEW vwEmployeeDetails

          AS

          SELECT COUNT(*) AS Total FROM Employee

          GO

   7.We can't use an order by clause at the time of view creation.

         CREATE VIEW vwEmployeeDetails

         AS

         SELECT EmpId, Name, Department, Salary FROM Employee ORDER BY     

         EmpId    DESC

         GO

   Error Message:

    Msg 1033, Level 15, State 1, Procedure vwEmployeeDetails, Line 5

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table      

    expressions, unless TOP, OFFSET or FOR XML is also specified.

       It clearly specifies to use TOP or FOR XML in your TSQL. Lets make some changes as    

       shown below and run the query again.

     CREATE VIEW vwEmployeeDetails

     AS

     SELECT TOP 5 EmpId, Name, Department, Salary FROM Employee ORDER BY EmpId     

     DESC

     GO

     SELECT * FROM vwEmployeeDetails

     

     

EmpId	Name	Department	Salary
10	Rajesh	PHP	4300
9	Airi	PHP	4300
8	Neha	PHP	2000
7	Harinder	PHP	4000
6	Bharat	Design	3040