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 descgo
    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_onTempTableas
    select     MobileName,CompanyNamefrom ##MobileDetailsgo
    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_OrdersNorthwindAs
    select     OrderID,CustomerID,ShipCity,ShipCountryfrom orders orderby OrderID descgo
    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_OrdersNorthwindAs
    select    top 100 OrderID,CustomerID,ShipCity,ShipCountryfrom orders orderby OrderID descgo
    Now select the records from the View and run this query.
    Select* from vw_OrdersNorthwind
     
    image1.jpg
  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 then 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_OrdersNorthwindAs--select OrderID,CustomerID,ShipCity,ShipCountry from orders selectcount(*) from ordersgo
    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_OrdersNorthwindAs--select OrderID,CustomerID,ShipCity,ShipCountry from orders select count(*) As Total from ordersgo

select * from vw_OrdersNorthwind