SQL SERVER – Indexed View always Use Index on Table

This blog post is written in response to T-SQL Tuesday hosted by Shankar Reddy.

I have been recently writing about Views and their Limitations. While writing this article series, I got inspired to write about SQL Server Quiz Questions. You can view the Quiz Question posted over here.

In SQL Server 2005, a single table can have maximum 249 non clustered indexes and 1 clustered index. In SQL Server 2008, a single table can have maximum 999 non clustered indexes and 1 clustered index. It is widely believed that a table can have only 1 clustered index, and this belief is true. I have some questions for all of you. Let us assume that I am creating view from the table itself and then create a clustered index on it. In my view, I am selecting the complete table itself.

USE tempdb
GO
-- Create sample Table
CREATE TABLE mySampleTable(ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable(ID1,ID2,SomeData)
SELECT TOP 1000 ROW_NUMBER()OVER (ORDER BY o1.name),
ROW_NUMBER()OVER (ORDER BY o1.name DESC),
o1.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create Index on Table
CREATE UNIQUE CLUSTERED INDEX [IX_SampleView] ON mySampleTable
(
ID1 ASC
)
GO
-- Create sample 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
-- Enable Execution Plan using CTRL + M
SELECT ID1,ID2,SomeData
FROM mySampleTable
GO
SELECT ID1,ID2,SomeData
FROM SampleView
GO
-- Clean up
DROP VIEW SampleView
DROP TABLE mySampleTable
GO

Now run the following script and answer these questions:

Q1. Does the table use an index created on itself?
Q2. Does the view use an index created on itself?
Q3. Do both the queries use the same index? If yes, why? If no, why not?

The answers are very clear.

The answers are very clear.

A1: Yes
A2: No
A3: Read the rest of the blog! or visit BeyondRelationa.com (where you can win iPAD as well).

Usually, the assumption is that Index on table will use Index on table and Index on view will be used by view. However, that is the misconception. It does not happen this way. In fact, if you notice the image, you will find the both of them (table and view) use both the index created on the table. The index created on the view is not used. The reason for the same as listed in BOL.

The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables. You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don’t initially reference the view explicitly. You can get the actual cost of the query with NOEXPAND and compare it to the actual cost of the query plan that doesn’t reference the view. If they are close, this may give you the confidence that the decision of whether or not to use the indexed view doesn’t matter.

Various answers to this question are given over here.

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

About these ads

SQL SERVER – SQL Quiz – The View, The Table and The Clustered Index Confusion

My very good friend, Jacob Sebastian, is running a month-long SQL Quiz Series where the best-of-the-best experts from around the globe would be the quiz masters. They will ask one question every day, and users are expected to answer them correctly. The winning prizes include cool gadgets like iPAD, Kindle and many more.

I am one of the quiz masters, and my question is published here: The View, The Table and The Clustered Index Confusion. I have asked there three questions. However, the real important question is:

Bonus Question: Does this mean that my table has two effective clustered indexes now?

The best answer to this question will be featured on this blog with due credit. If you have not answered the question yet, here is a hint for you:

In the SQL Server 2008 enterprise version, there is an additional feature where the Index View somehow started to become very helpful to parents object (table enclosed in the View).

However, the question can be answered evaluated in any version of SQL Server. The winner will be judged by the version number and answer. I have carefully picked the question so those who have different versions will have different answers.

What’s your answer then?

If you are interested in reading more about Views here is my quick blog post which discusses my recent articles on limitations of the views.

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

SQLAuthority News – Monthly Roundup of SQLAuthority Blog Posts

Since I started the monthly round up of the blog post, I have received many positive feedback. I plan to continue doing this month refresher every month now. This rounds ups are my mirror and informs me what I have been doing whole month. Here is quick look at the last month.

The month started very interesting with my daughter’s birthday SQLAuthority News – Fathers and Daughters. As this was very first birthday it was very special for me. I had great time enjoying with her quality time and it was all fun. I am an MVP and I am one proud one. I think MVP demonstrates community dedication and extreme passionate for community, I explained my understanding in the article SQLAuthority News – What is an MVP? – How to become an MVP?.

There was very interesting conversation on the subject of Soft-Delete SQL SERVER – Soft Delete – IsDelete Column – Your Opinion and really think community participated very well on the subject. I am also involved in training very unique fast track data ware house course and I have made few notes over here SQL SERVER – Few Notes on Fast Track Data Warehouse.

Not everything was as great as I expected during the whole month, I was really abused by Indian Airlines Spicejet I finally wrote about their harassment over SQLAuthority News – Spicejet Complaint – Update – No Outcome.If you want to read complete story, you can read my earlier article on the same subject SQLAuthority News – Spicejet Complain – Do Not Fly with Spicejet.

All DBA face common problems when their log file grow too big, I tried to answer the question in article SQL SERVER – How to Stop Growing Log File Too Big.

How can I not write on my favorite subject of indexing in any month.

I have also written series of limitation of the view. Here is the complete series.

I would like to request your feedback on the limitation of the view blog post series.

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

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

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

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
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit2]'))
DROP VIEW [dbo].[vw_ViewLimit2]
GO
-- Create View on sample tables
CREATE VIEW vw_ViewLimit1
WITH SCHEMABINDING
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
-- Another View created on the view using the same view cread earlier
CREATE VIEW vw_ViewLimit2
WITH SCHEMABINDING
AS
SELECT
[SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],v1.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],v1.[ReferenceOrderID]
,th.[Quantity]
FROM dbo.vw_ViewLimit1 v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Following statement will fail as view contains another view
CREATE 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.
*/

So here it is — one more limitation. The reason for this is that another view over a view is difficult to maintain. The workaround is very simple as explained in the error messages itself. Instead of creating nesting View, just bring over the code of the inner view to the outer view. After this, it will work just fine, letting you create an index (considering the earlier 10 limitations). How many of you know this issue?

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

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

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

Views have many limitations and the list is limitless!

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

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

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…
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 Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

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

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON 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 View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT
e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON 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 error
CREATE 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 Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO
-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT
e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Attempt to Create Index on View will thrown an error
CREATE 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 up
DROP VIEW myJoinView
DROP TABLE Employee
GO

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

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)