Limitation of the Views in SQL server

SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

Recently, I was about the limitations of views. I started to make a list and realized that there are many limitations of the views. Let us start with the first well-known limitation.

Order By clause does not work in View. I agree with all of you  who say that there is no need of using ORDER BY in the View. ORDER BY should be used outside the View and not in the View. This example is another reason why one should not use ORDER BY in Views.

Here is the quick example for the same. I have used sample database AdventureWorks for the example.

USE AdventureWorks
GO-- First Run regular query and observeSELECT *FROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID DESCGO-- Create view with same T-SQL ScriptIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))DROP VIEW [dbo].[vw_ViewLimit1]
GOCREATE VIEW vw_ViewLimit1AS
SELECT *FROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID DESCGO/*
Above Query will throw following error
Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5
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.
*/
-- Create view with same T-SQL Script without ORDER BYIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))DROP VIEW [dbo].[vw_ViewLimit1]
GOCREATE VIEW vw_ViewLimit1AS
SELECT *FROM Sales.SalesOrderDetail
GO-- Use Order by clause outside of the views
-- Create view with same T-SQL Script without ORDER BYSELECT *FROM vw_ViewLimit1ORDER BY SalesOrderDetailID DESCGO

If you try to include ORDER BY in View, it will throw the following error

Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5
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.

The above error itself explains how one can use ORDER BY in view. It suggests that if we use ORDER BY with TOP, we can surely use ORDER BY. If we want all the rows of the table, we can use TOP with 100 PERCENT. Let us try to modify our view with the usage of TOP 100 PERCENT and ORDER BY. This does not throw any error.

-- Create view with TOP 100 PERECENT and ORDER BYIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))DROP VIEW [dbo].[vw_ViewLimit1]
GOCREATE VIEW vw_ViewLimit1AS
SELECT TOP 100 PERCENT *FROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID DESCGO-- Select from viewSELECT *FROM vw_ViewLimit1
GO

However, when you observe the resultset, you will notice that table is not ordered DESC, which is specified by SalesOrderDetailID column, as it should be. Let us examine the execution plan. You will not notice that there is no SORT operation at all.

I have heard many people talking about workaround, where they use some other number less than 100 in the TOP clause. Let us do a small test with 99.99 PERCENT and see the type of result we get.

-- Create view with TOP 99.99  PERECENT and ORDER BYIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))DROP VIEW [dbo].[vw_ViewLimit1]
GOCREATE VIEW vw_ViewLimit1AS
SELECT TOP 99.99 PERCENT *FROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID DESCGO-- Select from viewSELECT *FROM vw_ViewLimit1
GO

Now let us check the result.We can clearly see that the result is Ordered by Column specified in the view.

However, as we are using TOP and 99.99, there is very little chance that we may not get all the rows from the table. Let us check the count of the rows in original table and Views.

-- Match the countsSELECT COUNT(*) ViewCountFROM vw_ViewLimit1
GOSELECT COUNT(*) OriginalCountFROM Sales.SalesOrderDetail
GO

From the count, it is clear that View has not returned all the rows because of the TOP specified. If table was a small table with less than 10,000 rows, this view might have not missed any rows, but in this case, where there are lots of rows, the View has missed rows.

Summary: It is not advisable to use ORDER BY in Views. Use ORDER BY outside the views. In fact, the correct design will imply the same. If you use TOP along with Views, there is a good chance that View will not return all the rows of the table or will ignore ORDER BY completely.

*******************************************************************************************************************************

SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2

*******************************************************************************************************************************

Note: I have updated the title based on feedback of Davide Mauri (Solid Quality Mentors). Thank you for your help.

Let’s see another reason why I do not like Views. Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, we will have to modify them first. This means any query that does not need this column will start having the column’s data additionally. This will lead to added network traffic as well as it will reduce the performance of the part where the View is used. This further leads to a conclusion: it may not be good idea to alter the View in order to add an additional column if the View happens to be used at multiple places. An alternative solution would be adding the column outside the View. However, this solution can be very expensive.

In today’s limitation of the View, we will see how adding an additional column outside the view can be very expensive, whereas the same situation does not happen with regular T-SQL query.

Let us first create a View using a sample database called AdventureWorks.

USE AdventureWorks
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))DROP VIEW [dbo].[vw_ViewLimit1]
GO-- Create View on sample tablesCREATE VIEW vw_ViewLimit1AS
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal],[ReferenceOrderID]FROM Sales.SalesOrderDetail sodINNER JOIN Production.TransactionHistory th ON sod.SalesOrderID =th.ReferenceOrderID
GO

Now let us compare the performance of the view with same SELECT statement used in the view.

/* Now compare the execution plan of the view and same definition of T-SQL statement */
-- Filter View with WHERE conditionSELECT *FROM vw_ViewLimit1WHERE SalesOrderDetailID > 111111
GO-- Regular SELECT statement with WHERE conditionSELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal],[ReferenceOrderID]FROM Sales.SalesOrderDetail sodINNER JOIN Production.TransactionHistory th ON sod.SalesOrderID =th.ReferenceOrderIDWHERE SalesOrderDetailID > 111111
GO

You will notice that the performance of the View and the SELECT statement is the same since the query cost of both statements is 50%.

Let us assume that we have to retrieve one more row from the table used in the View. As explained in the first paragraph, altering the View by adding a column to that View may actually lead to unnecessary data for the query using the View, but not looking for that column. The natural alternative solution to this is to use JOIN and add the column to the Views. In the case of T-SQL, we would not have to do the same since we will just go ahead and add the column to the statement.

/* Now let us try to retrieve the column which is not in View */
/* When you compare the performance you will notice View is more expensive*/
-- View with extra columnSELECT v1.*
,th.[Quantity]FROM vw_ViewLimit1 v1INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID =th.ReferenceOrderIDWHERE SalesOrderDetailID > 111111
GO-- Select statement with extra columnSELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal],[ReferenceOrderID],th.[Quantity]FROM Sales.SalesOrderDetail sodINNER JOIN Production.TransactionHistory th ON sod.SalesOrderID =th.ReferenceOrderIDWHERE SalesOrderDetailID > 111111
GO-- Clean upIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))DROP VIEW [dbo].[vw_ViewLimit1]
GO

We can now check the performance of both queries using the execution plan.  This will clearly show that a regular T-SQL statement with an additional column is truly less expensive than View that is retrieving an additional column using JOIN.

Click on the image to enlarge it.

Again, if you are not using the View at multiple places, I suggest that you modify it just fine because doing so will not cause you to lose any performance.

*******************************************************************************************************************************

SQL SERVER – Index Created on View not Used Often – Limitation of the View 3

I have heard many people saying that if they create view and index on it, this will reduce the load on original table as all the subsequent queries on view will not access the basic table. This is not true always and the view may not give you the performance optimizations which you are looking for.

In the following example, we will create the base table first. We will then create the view on top of it and create the index on the view. Subsequently, we will run the simple SELECT statement on base table and on the view. It is noticed that view does not pick up the index created on the view, but uses the base index created on the original table. It will also expand the underlying table and use the index created on base table. If you assume that after creating the index on view, the view is materialized and has no relation with original table, then this can very well be incorrect.

USE tempdb
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[SampleView]'))DROP VIEW [dbo].[SampleView]
GOIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))DROP TABLE [dbo].[mySampleTable]
GO-- Create SampleTableCREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))INSERT INTO mySampleTable (ID1,ID2,SomeData)SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),ROW_NUMBER() OVER (ORDER BY o2.name),o2.nameFROM sys.all_objects o1CROSS JOIN sys.all_objects o2
GO-- Create Index on TableCREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable(ID1 ASC)GO-- Select from tableSELECT ID1,ID2,SomeDataFROM mySampleTable
GO-- Create ViewCREATE VIEW SampleViewWITH SCHEMABINDINGAS
SELECT ID1,ID2,SomeDataFROM dbo.mySampleTable
GO-- Create Index on ViewCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO-- Select from viewSELECT ID1,ID2,SomeDataFROM SampleView
GO

Now let us observe the execution plan and the usage of index along with query.

Querying Original Table

Querying View

You might have noticed that the View used the same index as original table and did not use the index created on the table. The reason is as follows: the optimizer has decided that the original index will be more beneficial in query plan than the newly created index. I tried clearing the cache, but the same result is obtained every time.

If you really want your view to act as an independent table and have no relation with original underlying table, then you can use the hint [noexpand] and the view will not expand. We will see this in some other article in future.

*******************************************************************************************************************************

SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4

This is very well known limitation of the View.

Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.

To test this, we will create a view where we will use SELECT * and select everything from the table. Once the view is created, we will add a column to the view. We will test that even though we have used SELECT *, the view does not retrieve the newly added column. Once we refresh the view using SP_REFRESHVIEW, it will start retrieving the newly added column.

Run the following T-SQL script in SQL Server Management Studio New Query Window:

USE AdventureWorks
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[LimitView4]'))DROP VIEW [dbo].[LimitView4]
GO-- Create ViewCREATE VIEW LimitView4AS
SELECT *FROM HumanResources.Shift
GO-- Select from original tableSELECT *FROM HumanResources.Shift
GO-- Select from ViewSELECT *FROM LimitView4
GO-- Add Column to original TableALTER TABLE HumanResources.ShiftADD AdditionalCol INTGO-- Select from original tableSELECT *FROM HumanResources.Shift
GO-- Select from ViewSELECT *FROM LimitView4
GO-- Refresh the viewEXEC sp_refreshview 'LimitView4'GO-- Select from original tableSELECT *FROM HumanResources.Shift
GO-- Select from ViewSELECT *FROM LimitView4
GO-- Clean upALTER TABLE HumanResources.ShiftDROP COLUMN AdditionalCol
GO

Above query will return following resultset.

The same limitation exits in the case of deleting the column as well. This is a very well-known issue with the Views. The resolutions of these issues are as follows:

1. Refresh the views using sp_refreshview stored procedure
2. Do not use SELECT * but use SELECT columnnames
3. Create view with SCHEMABINDING; this way, the underlying table will not get modified.

I am enjoying writing this series of blog posts on the limitation of the views. Do let me know if there is any other limitation you would like to read on this blog.

*******************************************************************************************************************************

SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5

One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example here.

USE tempdb
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[SampleView]'))DROP VIEW [dbo].[SampleView]
GOIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))DROP TABLE [dbo].[mySampleTable]
GO-- Create SampleTableCREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))INSERT INTO mySampleTable (ID1,ID2,SomeData)SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),ROW_NUMBER() OVER (ORDER BY o2.name),o2.nameFROM sys.all_objects o1CROSS JOIN sys.all_objects o2
GO-- Create ViewCREATE VIEW SampleViewWITH SCHEMABINDINGAS
SELECT COUNT(*) TableCount, ID2FROM dbo.mySampleTableGROUP BY ID2
GO-- Create Index on ViewCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO/* Above statement will thrown an error
Msg 10136, Level 16, State 1, Line 1
Cannot create index on view "tempdb.dbo.SampleView" because it uses the aggregate COUNT. Use COUNT_BIG instead.
*/
-- Aleter View to replace COUNT with BIG_COUNTALTER VIEW SampleViewWITH SCHEMABINDINGAS
SELECT COUNT_BIG(*) TableCount, ID2FROM dbo.mySampleTableGROUP BY ID2
GO-- Now let us create Index again - this time successfullyCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO

Here is a screenshot of an error that occurred when Views used COUNT(*) and there was an attempt to create an Index on it.

If you are wondering about the reason behind allowing COUNT_BIG and COUNT, here is a quick explanation for this. Itzik Ben-Gan explained me the reason for this situation. He has also clarified the reasons in his book series, ‘Inside T-SQL‘. Here is a concise summary of the explanation. This response from Itzik is produced unaltered and with his permissions:

If the query is a grouped query, SQL Server needs to keep track of the count in each group in order to known whether a group needs to be modified or removed altogether upon DELETE/UPDATE of rows against the underlying tables. As for why the COUNT_BIG and not just COUNT, since SQL Server materializes the counts along with the rest of the view’s data, I guess this has to do with support for groups with more rows than the maximum four-byte integer.

BTW, unrelated to views but along similar lines, see what happens if you add to a clustered table more than the maximum four-byte integer number of rows with the same non-unique clustered index key. The uniqueifiers SQL Server uses internally to distinguish between rows with the same clustered index key is a four-byte integer. Once it overflows, you get error 666 and are not allowed to add more rows with the same clustered index key.

Now, with uniqueifiers for clustering keys I understand the choice to go for four bytes since there are great space savings and therefore read performance benefits as a result, and we are talking about an extreme case for this to happen . But with grouped queries, usually the number of groups is not too large, but groups themselves can be large. Imagine a situation where you try to add more rows to a table that has an indexed view and SQL Server rejects the insert because of a four-byte int overflow in the target group count.

I hope it is clear now. If you want to learn more about this, you can continue reading his bookInside T-SQL.

*******************************************************************************************************************************

SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6

If you want to create an Indexed View, you ought to know that UNION Operation is now allowed in Indexed View. It is quite surprising at times when the UNION operation looks very innocent and seems that it cannot be used in the View.

Before an in-depth understanding this subject, let me show you a script where UNION is now allowed in Indexed View:

USE tempdb
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[SampleView]'))DROP VIEW [dbo].[SampleView]
GOIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))DROP TABLE [dbo].[mySampleTable]
GO-- Create SampleTableCREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))INSERT INTO mySampleTable (ID1,ID2,SomeData)SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),ROW_NUMBER() OVER (ORDER BY o2.name),o2.nameFROM sys.all_objects o1CROSS JOIN sys.all_objects o2
GO-- Create ViewCREATE VIEW SampleViewWITH SCHEMABINDINGAS
SELECT ID1,ID2,SomeDataFROM dbo.mySampleTableWHERE ID1 < 1000UNION
SELECT ID1,ID2,SomeDataFROM dbo.mySampleTableWHERE ID2 < 1000
GO-- Create Index on ViewCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO/* Above statement will thrown an error
Msg 10116, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.SampleView' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
*/
-- Aleter View to replace COUNT with BIG_COUNTALTER VIEW SampleViewWITH SCHEMABINDINGAS
SELECT ID1,ID2,SomeDataFROM dbo.mySampleTableWHERE ID1 < 1000 OR ID2 < 1000
GO-- Now let us create Index again - this time successfullyCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO

During the script, the following ERROR would occur if you try to create the index while UNION operation is in the view:

Msg 10116, Level 16, State 1, Line 1
Cannot create index on view ‘tempdb.dbo.SampleView’ because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.

In contrast to this converting the UNION to OR operation would give the same result, plus it would allow you to create an index on the View. Well, our example is one in which we are able to re-write the script with OR clause. However, keep in mind that there can be cases where it is not possible to re-write and you might end up not using Views with Index.

*******************************************************************************************************************************

SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7

One of the requirements of Indexed View is that it has to be created ‘WITH SCHEMABINDING’. If the View is not created with that clause, it would not let you create an index on that View. Moreover, if you try to create a View with schemabinding, it would not allow you to create the database.

-- Create DBUSE MASTERGOCREATE DATABASE TEST1CREATE DATABASE TEST2
GO-- Table1USE Test1
GOCREATE TABLE TABLE1 (ID INT)GOUSE Test2
GO-- Table2CREATE TABLE TABLE2 (ID INT)GOUSE Test1
GO-- Create ViewCREATE VIEW CrossDBViewWITH SCHEMABINDINGAS
SELECT t1.ID AS t1id, t2.ID AS t2idFROM Test1.dbo.Table1 t1INNER JOIN Test2.dbo.Table2 t2 ON t1.ID = t2.ID
GO/*
Error:
Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4
Cannot schema bind view 'CrossDBView' because name 'Test1.dbo.Table1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
*/
-- Clean upDROP VIEW CrossDBView
GOUSE MASTERGODROP DATABASE TEST1DROP DATABASE TEST2
GO

When you try to create the View, it would throw the following error:

Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4
Cannot schema bind view ‘CrossDBView’ because name ‘Test1.dbo.Table1′ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

*******************************************************************************************************************************

SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8

This blog post was previously published over here. I am republishing it in the series Limitation of the Views with a few modifications.

While reading the white paper Improving Performance with SQL Server 2008 Indexed Views, I noticed that it says outer joins are NOT allowed in the indexed views. Here, I have created an example to demonstrate why this is so.

Rows can logically disappear from an Indexed View based on OUTER JOIN when you insert data into a base table. This makes the OUTER JOIN view to be increasingly updated, which is relatively difficult to implement. In addition, the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

The reader was confused with my answer and wanted me to explain it further. Here is the example that I have quickly put together to demonstrate the behavior described in the above statement:

USE tempdb
GO-- Create Two TablesCREATE TABLE BaseTable (ID1 INT, Col1 VARCHAR(100))CREATE TABLE JoinedTable (ID2 INT, Col2 VARCHAR(100))GO-- Insert Values in TablesINSERT INTO BaseTable (ID1,Col1)SELECT 1,'First'UNION ALLSELECT 2,'Second'GOINSERT INTO JoinedTable (ID2,Col2)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 4,'Fourth'GO-- Use Outer JoinSELECT jt.*FROM BaseTable btRIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2WHERE bt.ID1 IS NULLGO

The script above will give us the following output:

-- Now Insert Rows in Base TableINSERT INTO BaseTable (ID1,Col1)SELECT 3,'Third'GO-- You will notice that one row less retrieved from JoinSELECT jt.*FROM BaseTable btRIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2WHERE bt.ID1 IS NULLGO-- Clean upDROP TABLE BaseTableDROP TABLE JoinedTable
GO

After running this script, you will notice that as the base table gains one row, the result loses one row. Going back to the white paper I mentioned earlier, I believe this is an expensive way to manage the same issue as to why it is not allowed in Indexed View.

Additionally, SQL Server Expert Ramdas provided excellent explanations regarding NULL and why resultset maintenance is expensive, over here.

“A disadvantage of outer joins in SQL is that they generate nulls in the result set. Those nulls are indistinguishable from other nulls that are not generated by the outer join operation. There is no “standard” semantics for nulls in SQL but in many common situations, the appearance of nulls in outer joins doesn’t really correspond to the way nulls are returned and used in other places. Therefore, the presence of nulls in outer joins creates a certain amount of ambiguity.”

*******************************************************************************************************************************

SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9

Previously, I wrote an article about SQL SERVER – The Self Join – Inner Join and Outer Join, and that blog post seems very popular because of its interesting points. It is quite common to think that Self Join is also only Inner Join, but the reality is that it can be anything. The concept of Self Join is very useful that we use it quite often in our coding. However, this is not allowed in the Index View. I will be using the same example  that I have created earlier for the said article.

Let us first create the same table for an employee. One of the columns in this table contains the ID of the manger, who is an employee of that company, at the same time. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need to use Self Join.

USE TempDb
GO-- Create a TableCREATE TABLE Employee(EmployeeID INT PRIMARY KEY,Name NVARCHAR(50),ManagerID INT)GO-- Insert Sample DataINSERT INTO EmployeeSELECT 1, 'Mike', 3UNION ALLSELECT 2, 'David', 3UNION ALLSELECT 3, 'Roger', NULLUNION ALLSELECT 4, 'Marry',2UNION ALLSELECT 5, 'Joseph',2UNION ALLSELECT 7, 'Ben',2
GO-- Check the dataSELECT *FROM Employee
GO

We will now utilize Inner Join to find the employees and their managers’ details.

-- Inner JoinSELECT e1.Name EmployeeName, e2.name AS ManagerNameFROM Employee e1INNER JOIN Employee e2ON e1.ManagerID = e2.EmployeeID
GO

Now let us try to create View on the table. This will allow well construction of the View without any issues associated with it.

-- Create a ViewCREATE VIEW myJoinViewWITH SCHEMABINDINGAS
SELECT e1.Name EmployeeName, e2.name AS ManagerNameFROM dbo.Employee e1INNER JOIN dbo.Employee e2ON e1.ManagerID = e2.EmployeeID
GO

Now let us try to create a Clustered Index on the View.

-- Attempt to Create Index on View will thrown an errorCREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]([EmployeeName] ASC)GO

Unfortunately, the above attempt will not allow you to create the Clustered Index, as evidenced by an error message. It will throw following error suggesting that SELF JOIN is now allowed in the table.

Msg 1947, Level 16, State 1, Line 2
Cannot create index on view “tempdb.dbo.myJoinView”. The view contains a self join on “tempdb.dbo.Employee”.

The generic reason provided is that it is very expensive to manage the view for SQL Server when SELF JOIN is implemented in the query.

If any of you has a better explanation of this subject, please post it here through your comments, and I will publish it with due credit.

The complete script for the example is given below:

USE TempDb
GO-- Create a TableCREATE TABLE Employee(EmployeeID INT PRIMARY KEY,Name NVARCHAR(50),ManagerID INT)GO-- Insert Sample DataINSERT INTO EmployeeSELECT 1, 'Mike', 3UNION ALLSELECT 2, 'David', 3UNION ALLSELECT 3, 'Roger', NULLUNION ALLSELECT 4, 'Marry',2UNION ALLSELECT 5, 'Joseph',2UNION ALLSELECT 7, 'Ben',2
GO-- Check the dataSELECT *FROM Employee
GO-- Inner JoinSELECT e1.Name EmployeeName, e2.name AS ManagerNameFROM Employee e1INNER JOIN Employee e2ON e1.ManagerID = e2.EmployeeID
GO-- Create a ViewCREATE VIEW myJoinViewWITH SCHEMABINDINGAS
SELECT e1.Name EmployeeName, e2.name AS ManagerNameFROM dbo.Employee e1INNER JOIN dbo.Employee e2ON e1.ManagerID = e2.EmployeeID
GO-- Attempt to Create Index on View will thrown an errorCREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]([EmployeeName] ASC)GO/*
Msg 1947, Level 16, State 1, Line 2
Cannot create index on view "tempdb.dbo.myJoinView". The view contains a self join on "tempdb.dbo.Employee".
*/
-- Clean upDROP VIEW myJoinViewDROP TABLE Employee
GO

*******************************************************************************************************************************

SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10

I have recently wrote many articles on the limitation of the views. In this article, I have tried to sum up all the keywords which are not allowed in the indexed view. If any of the following keyword is used in the View, Index is not possible to create on the same.

I think this list can be used as quick reference for anybody who wants to create view and index it to get best out of the views.

• ANY, NOT ANY
• Arithmetic on imprecise (float, real) values
• COMPUTE, COMPUTE BY
• Contradictions SQL Server can detect that mean the view would be empty (for example, where 0=1 and …)
• CONVERT producing an imprecise result
• COUNT(*)
• Derived tables (subquery in FROM list)
• DISTINCT
• EXISTS, NOT EXISTS
• Expressions on aggregate results (for example, SUM(x)+SUM(x))
• Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
• GROUP BY ALL
• Imprecise constants (for example, 2.34e5)
• Inline or table-valued functions
• MIN, MAX
• Nondeterministic expressions
• Non-Unicode collations
• OPENROWSET, OPENQUERY, OPENDATASOURCE
• OPENXML
• ORDER BY
• OUTER join
• References to a base table with a disabled clustered index
• References to a table or function in a different database
• References to another view
• ROWSET functions
• Self-joins
• STDEV, STDEVP, VAR, VARP, AVG
• Subqueries
• SUM on nullable expressions
• Table hints (for example, NOLOCK)
• text, ntext, image, filestream, or xml columns
• TOP
• UNION

After looking at the long list which contains Self Join, TOP, UNION, OUTER JOIN and many other useful keywords, one has to wonder how limited the usage of the query is in the view. One can not utilize the full potentials of the views.

*******************************************************************************************************************************

SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11

When I wrote the article about SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2, I had received a comment that said:

“If joining column is expensive to the view, why can’t I create a view over the view and create an index on it?”

The answer is simple: It’s actually another limitation of the View.

You cannot create an Index on a nested View situation. The following example where we can demonstrate the issue is attached below. In this example, there is already one view, and another view is created on the top of the view. When attempting to create an index on the outer view, it would not be allowed and would throw an error.

Let us see the example:

USE AdventureWorks
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))DROP VIEW [dbo].[vw_ViewLimit1]
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[vw_ViewLimit2]'))DROP VIEW [dbo].[vw_ViewLimit2]
GO-- Create View on sample tablesCREATE VIEW vw_ViewLimit1WITH SCHEMABINDINGAS
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal],[ReferenceOrderID]FROM Sales.SalesOrderDetail sodINNER JOIN Production.TransactionHistory th ON sod.SalesOrderID =th.ReferenceOrderID
GO-- Another View created on the view using the same view cread earlierCREATE VIEW vw_ViewLimit2WITH SCHEMABINDINGAS
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],v1.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal],v1.[ReferenceOrderID],th.[Quantity]FROM dbo.vw_ViewLimit1 v1INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID =th.ReferenceOrderIDWHERE SalesOrderDetailID > 111111
GO-- Following statement will fail as view contains another viewCREATE UNIQUE CLUSTERED INDEX [IX_vw_ViewLimit1] ON [dbo].[vw_ViewLimit2]([ProductID] ASC)GO/*
Msg 1937, Level 16, State 1, Line 1
Cannot create index on view 'AdventureWorks.dbo.vw_ViewLimit2' because it references another view 'dbo.vw_ViewLimit1'. Consider expanding referenced view's definition by hand in indexed view definition.
*/