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
    select    top 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

    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 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