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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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 Tables
CREATE TABLE BaseTable (ID1 INT, Col1 VARCHAR(100))
CREATE TABLE JoinedTable (ID2 INT, Col2 VARCHAR(100))
GO
-- Insert Values in Tables
INSERT INTO BaseTable (ID1,Col1)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO
INSERT INTO JoinedTable (ID2,Col2)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
GO
-- Use Outer Join
SELECT jt.*
FROM BaseTable bt
RIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2
WHERE bt.ID1 IS NULL
GO

The script above will give us the following output:

-- Now Insert Rows in Base Table
INSERT INTO BaseTable (ID1,Col1)
SELECT 3,'Third'
GO
-- You will notice that one row less retrieved from Join
SELECT jt.*
FROM BaseTable bt
RIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2
WHERE bt.ID1 IS NULL
GO
-- Clean up
DROP TABLE BaseTable
DROP 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.”

This series is indeed getting very interesting. What are your suggestions?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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 DB
USE MASTER
GO
CREATE DATABASE TEST1
CREATE DATABASE TEST2
GO
-- Table1
USE Test1
GO
CREATE TABLE TABLE1 (ID INT)
GO
USE Test2
GO
-- Table2
CREATE TABLE TABLE2 (ID INT)
GO
USE Test1
GO
-- Create View
CREATE VIEW CrossDBView
WITH SCHEMABINDING
AS
SELECT
t1.ID AS t1id, t2.ID AS t2id
FROM Test1.dbo.Table1 t1
INNER 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 up
DROP VIEW CrossDBView
GO
USE MASTER
GO
DROP DATABASE TEST1
DROP 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.

After looking at all these limitations, have you thought of other constraints that View has? If you know any other interesting limitations, please share them here through your comments.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE 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.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000
UNION
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID2 < 1000
GO
-- Create Index on View
CREATE 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_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000 OR ID2 < 1000
GO
-- Now let us create Index again - this time successfully
CREATE 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.

In this series, I have been writing about many limitations and their explanation. Now here are my questions for you:

  • What do you think is the reason behind these limitations?
  • Why UNION is not allowed in the View?

I will publish your answer with due credit on the blog.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE 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.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
COUNT(*) TableCount, ID2
FROM dbo.mySampleTable
GROUP BY ID2
GO
-- Create Index on View
CREATE 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_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
COUNT_BIG(*) TableCount, ID2
FROM dbo.mySampleTable
GROUP BY ID2
GO
-- Now let us create Index again - this time successfully
CREATE 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 book Inside T-SQL.

Let me know what you think of these limitations, as well as your opinions about the example.

Reference: Pinal Dave (http://blog.sqlauthority.com)

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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[LimitView4]'))
DROP VIEW [dbo].[LimitView4]
GO
-- Create View
CREATE VIEW LimitView4
AS
SELECT
*
FROM HumanResources.Shift
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Add Column to original Table
ALTER TABLE HumanResources.Shift
ADD AdditionalCol INT
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Refresh the view
EXEC sp_refreshview 'LimitView4'
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Clean up
ALTER TABLE HumanResources.Shift
DROP 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.

Reference: Pinal Dave (http://blog.sqlauthority.com)

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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE 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.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create Index on Table
CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable
(
ID1 ASC
)
GO
-- Select from table
SELECT ID1,ID2,SomeData
FROM mySampleTable
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
-- Select from view
SELECT ID1,ID2,SomeData
FROM 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.

Let me know if you want to read about any other limitation of the view.

Reference: Pinal Dave (http://blog.sqlauthority.com)

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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
-- Create View on sample tables
CREATE VIEW vw_ViewLimit1
AS
SELECT
[SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
FROM Sales.SalesOrderDetail sod
INNER 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 condition
SELECT *
FROM vw_ViewLimit1
WHERE SalesOrderDetailID > 111111
GO
-- Regular SELECT statement with WHERE condition
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE 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 column
SELECT v1.*
,
th.[Quantity]
FROM vw_ViewLimit1 v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Select statement with extra column
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
,th.[Quantity]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Clean up
IF 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.

Reference: Pinal Dave (http://blog.sqlauthority.com)