SQL SERVER – Statistics for Read-Only Database are in TempDB

Here is the question I received when I was explaining about Statistics in SQLLive360 event in Orlando.

stastsold SQL SERVER   Statistics for Read Only Database are in TempDB

“As we use database tables and run various queries on SQL Server, it automatically creates statistics and the statistics helps eventually queries to execute efficiently. I also understand that statistics are stored in the same database where original object (table, view) exists. In the case of the Read-Only Database, what actually happens? Does Read-Only Database have statistics? If they can have statistics, how do they store them as database is already a Read-Only database?”

Well, this is a fantastic question as I have never blogged about it before. Here is how I would like to sum up Read-Only Database and statistics.

Read-Only Database and Statistics

Just like any other database Read-Only Database can also have statistics. In all of the other cases the statistics are stored in the database where the original object is stored but in the case of the Read-Only Databases, statistics are stored in TempDB. The behavior of the statistics created on Read-Only Database is absolutely same as they are created on any other database. However, there is a one thing to remember – whenever we restart the server the TempDB is reset to original state hence, every single time the server is restarted the statistics are built again from scratch for your Read-Only Database in TempDB.

Script to list Statistics

Here is a script which we can use to list all the statistics from any database.

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats;

Script to list Statistics for Read-Only Database

Here is a script which we can use to list all the statistics from any database.

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE is_temporary = 1;

Column is_temporary  is set to 1 when statistics are created on the Read-Only Database and they are stored in the TempDB. If you restart the server statistics will go away and will be created when needed again.

Example

Let us see an example, where we will create a database and later on populate that with the data. Once we have data in the table, we will make it read only and check if there are any statistics on that data table or not. We will not find any statistics on that table as we have so far not executed any query. Now let us execute a query on the table and once again check the status of statistics. You will find necessary stats created on the table. When you pay attention to the last column it will have is_temporary = 1, this means that the statistics has been created in TempDB.

-- Create Database
CREATE DATABASE [Read-Only]
GO
-- Use Database
USE [Read-Only]
GO
-- Create Table
CREATE TABLE [Shift](
[ShiftID] [tinyint] NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[StartTime] [time](7) NOT NULL,
[EndTime] [time](7) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
GO
-- Insert Table
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (1, N'Day', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (2, N'Evening', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (3, N'Night', GETDATE(), GETDATE(), GETDATE())
GO
-- Make Database Read Only
ALTER DATABASE [Read-Only] SET READ_ONLY
GO
-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM   sys.stats
WHERE  is_temporary = 1
GO
-- Simple Select Statement
SELECT *
FROM [Shift]
WHERE ShiftID = 1
GO
-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM   sys.stats
WHERE  is_temporary = 1
GO
-- Clean up
USE MASTER
GO
DROP DATABASE [Read-Only]
GO

tempstats SQL SERVER   Statistics for Read Only Database are in TempDB

I have executed all of my scripts in SQL Server 2012. Let me know what you think about this subject.

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

SQL SERVER – Guest Post by Sandip Pani – SQL Server Statistics Name and Index Creation

sandippani SQL SERVER   Guest Post by Sandip Pani   SQL Server Statistics Name and Index Creation Sometimes something very small or a common error which we observe in daily life teaches us new things. SQL Server Expert Sandip Pani (winner of Joes 2 Pros Contests) has come across similar experience. Sandip has written a guest post on an error he faced in his daily work. Sandip is working for QSI Healthcare as an Associate Technical Specialist and have more than 5 years of total experience. He blogs at SQLcommitted.com and contribute in various forums. His social media hands are LinkedIn, Facebook and Twitter.


Once I faced following error when I was working on performance tuning project and attempt to create an Index.

Mug 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name ‘Ix_Table1_1′ already exists on table ‘Table1′.

The immediate reaction to the error was that I might have created that index earlier and when I researched it further I found the same as the index was indeed created two times. This totally makes sense. This can happen due to many reasons for example if the user is careless and executes the same code two times as well, when he attempts to create index without checking if there was index already on the object. However when I paid attention to the details of the error, I realize that error message also talks about statistics along with the index. I got curious if the same would happen if I attempt to create indexes with the same name as statistics already created. There are a few other questions also prompted in my mind. I decided to do a small demonstration of the subject and build following demonstration script.

The goal of my experiment is to find out the relation between statistics and the index. Statistics is one of the important input parameter for the optimizer during query optimization process. If the query is nontrivial then only optimizer uses statistics to perform a cost based optimization to select a plan. For accuracy and further learning I suggest to read MSDN.

Now let’s find out the relationship between index and statistics. We will do the experiment in two parts. i) Creating Index ii) Creating Statistics

We will be using the following T-SQL script for our example.

IF (OBJECT_ID('Table1') IS NOT NULL)
DROP TABLE Table1
GO
CREATE TABLE Table1
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO

We will be using following two queries to check if there are any index or statistics on our sample table Table1.

-- Details of Index
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, Name AS IndexName, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO
-- Details of Statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, Name AS StatisticsName
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO

When I ran above two scripts on the table right after it was created it did not give us any result which was expected.

statindex1 SQL SERVER   Guest Post by Sandip Pani   SQL Server Statistics Name and Index Creation

Now let us begin our test.

1) Create an index on the table

Create following index on the table.

CREATE NONCLUSTERED INDEX Ix_Table1_1 ON Table1(Col1)
GO

Now let us use above two scripts and see their results.

statindex2 SQL SERVER   Guest Post by Sandip Pani   SQL Server Statistics Name and Index Creation

We can see that when we created index at the same time it created statistics also with the same name.

Before continuing to next set of demo – drop the table using following script and re-create the table using a script provided at the beginning of the table.

DROP TABLE table1
GO

2) Create a statistic on the table

Create following statistics on the table.

CREATE STATISTICS Ix_table1_1 ON Table1 (Col1)
GO

Now let us use above two scripts and see their results.

statindex3 SQL SERVER   Guest Post by Sandip Pani   SQL Server Statistics Name and Index Creation

We can see that when we created statistics Index is not created. The behavior of this experiment is different from the earlier experiment.

Clean up the table setup using the following script:

DROP TABLE table1
GO

Above two experiments teach us very valuable lesson that when we create indexes, SQL Server generates the index and statistics (with the same name as the index name) together. Now due to the reason if we have already had statistics with the same name but not the index, it is quite possible that we will face the error to create the index even though there is no index with the same name.

A Quick Check

To validate that if we create statistics first and then index after that with the same name, it will throw an error let us run following script in SSMS. Make sure to drop the table and clean up our sample table at the end of the experiment.

-- Create sample table
CREATE TABLE TestTable
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO
-- Create Statistics
CREATE STATISTICS IX_TestTable_1 ON TestTable (Col1)
GO
-- Create Index
CREATE NONCLUSTERED INDEX IX_TestTable_1 ON TestTable(Col1)
GO
-- Check error
/*Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'IX_TestTable_1' already exists on table 'TestTable'.
*/
-- Clean up
DROP TABLE TestTable
GO

While creating index it will throw the following error as statistics with the same name is already created.

statindex4 SQL SERVER   Guest Post by Sandip Pani   SQL Server Statistics Name and Index Creation

In simple words – when we create index the name of the index should be different from any of the existing indexes and statistics.

Click to Download Scripts

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

SQL SERVER – Fix: Error: Msg 1904, Level 16 The statistics on table has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32

Earlier I wrote an article where I demonstrated that an index with more than 16 column is not possible. Here is the link to the article. After reading the same article I received email from user suggesting does it mean that statistics can be only created on only 16 columns. Well, answer is NO. One can create statistics on total of 32 columns, where as the limit of creating index is only 16 columns (and 900 bytes).

Here is the quick example where when attempted to create statistics on 33 columns is generating error but when statistics are created on 32 columns it works successfully.

Set up Script:

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE Test1
(ID1 INT,  ID2 INT, ID3 INT,
ID4 INT, ID5 INT, ID6 INT,
ID7 INT, ID8 INT, ID9 INT,
ID10 INT, ID11 INT, ID12 INT,
ID13 INT, ID14 INT, ID15 INT,
ID16 INT, ID17 INT, ID18 INT,
ID19 INT, ID20 INT, ID21 INT,
ID22 INT, ID23 INT, ID24 INT,
ID25 INT, ID26 INT, ID27 INT,
ID28 INT, ID29 INT, ID30 INT,
ID31 INT, ID32 INT, ID33 INT)
GO

Here is the example when index created on 33 columns it gives error.

CREATE STATISTICS [Stats_Test1]
ON [dbo].[Test1]
([ID1], [ID2], [ID3], [ID4], [ID5],
[ID6], [ID7], [ID8], [ID9], [ID10],
[ID11], [ID12], [ID13], [ID14], [ID15],
[ID16], [ID17], [ID18], [ID19], [ID20],
[ID21], [ID22], [ID23], [ID24], [ID25],
[ID26], [ID27], [ID28], [ID29], [ID30],
[ID31], [ID32], [ID33]
)
GO

Msg 1904, Level 16, State 2, Line 1
The statistics ‘Stats_Test1′ on table ‘dbo.Test1′ has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32.

Here is the example when index created on 32 columns it works perfectly fine.

CREATE STATISTICS [Stats_Test1]
ON [dbo].[Test1]
([ID1], [ID2], [ID3], [ID4], [ID5],
[ID6], [ID7], [ID8], [ID9], [ID10],
[ID11], [ID12], [ID13], [ID14], [ID15],
[ID16], [ID17], [ID18], [ID19], [ID20],
[ID21], [ID22], [ID23], [ID24], [ID25],
[ID26], [ID27], [ID28], [ID29], [ID30],
[ID31], [ID32]
)
GO

Well, in simple words, one can create statistics on 32 columns. Please additionally, note, the scope of this blog is not to discuss if that is good or bad. The purpose of this post is notice the Index can have maximum 16 columns but statistics can have 32 columns.

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

SQL SERVER – Find Details for Statistics of Whole Database – DMV – T-SQL Script

statistics Img SQL SERVER   Find Details for Statistics of Whole Database   DMV   T SQL ScriptI was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future. Again, using DMV is the right thing to do moving forward. I quickly wrote following script which gives a lot more information than sp_helpstats.

USE AdventureWorks
GO
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);

stats DMV SQL SERVER   Find Details for Statistics of Whole Database   DMV   T SQL Script

If you have better script to retrieve information about statistics, please share here and I will publish it with due credit.

Update: Read follow up excellent blog post by Jason Brimhall.

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

SQL SERVER – Puzzle – Statistics are not Updated but are Created Once

After having excellent response to my quiz – Why SELECT * throws an error but SELECT COUNT(*) does not?I have decided to ask another puzzling question to all of you.

I am running this test on SQL Server 2008 R2. Here is the quick scenario about my setup.

  • Create Table
  • Insert 1000 Records
  • Check the Statistics
  • Now insert 10 times more 10,000 indexes
  • Check the Statistics – it will be NOT updated

Note: Auto Update Statistics and Auto Create Statistics for database is TRUE

Expected Result – Statistics should be updated – SQL SERVER – When are Statistics Updated – What triggers Statistics to Update

Now the question is why the statistics are not updated?

The common answer is – we can update the statistics ourselves using

UPDATE STATISTICS TableName WITH FULLSCAN, ALL

However, the solution I am looking is where statistics should be updated automatically based on algorithm mentioned here.

Now the solution is to ____________________.

Vinod Kumar is not allowed to take participate over here as he is the one who has helped me to build this puzzle.

I will publish the solution on next week. Please leave a comment and if your comment consist valid answer, I will publish with due credit.

Here is the script to reproduce the scenario which I mentioned.

-- Execution Plans Difference
-- Create Sample Database
CREATE DATABASE SampleDB
GO
USE SampleDB
GO
-- Create Table
CREATE TABLE ExecTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Insert One Thousand Records
-- INSERT 1
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Display statistics of the table - none listed
sp_helpstats N'ExecTable', 'ALL'
GO
-- Select Statement
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City  = 'New York'
GO
-- Display statistics of the table
sp_helpstats N'ExecTable', 'ALL'
GO
 SQL SERVER   Puzzle   Statistics are not Updated but are Created Once
-- Replace your Statistics over here
-- NOTE: Replace your _WA_Sys with stats from above query
DBCC SHOW_STATISTICS('ExecTable', _WA_Sys_00000004_7D78A4E7);
GO
--------------------------------------------------------------
-- Round 2
-- Insert Ten Thousand Records
-- INSERT 2
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Select Statement
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City  = 'New York'
GO
-- Display statistics of the table
sp_helpstats N'ExecTable', 'ALL'
GO
-- Replace your Statistics over here
-- NOTE: Replace your _WA_Sys with stats from above query
DBCC SHOW_STATISTICS('ExecTable', _WA_Sys_00000004_7D78A4E7);
GO
-- You will notice that Statistics are still updated with 1000 rows SQL SERVER   Puzzle   Statistics are not Updated but are Created Once
-- Clean up Database
DROP TABLE ExecTable
GO
USE MASTER
GO
ALTER DATABASE SampleDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE SampleDB
GO

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

SQLAuthority News – Solid Quality Journal – Importance of Statistics

My article on “Important of Statistics” has been published in Solid Quality Journal.

Statistics are a key part of getting solid performance. In this article we will go over the basics of the statistics and various best practices related to Statistics. We will go over various frequently asked questions like when to update statistics and difference between sync and async update of statistics. We will also discuss the pros and cons of the statistics update.

I have answered one very important questions in this article:

Should keep Auto Create Statistics and Auto Update Statistics settings true/on?

Download Importance of Statistics

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

SQL SERVER – Copy Statistics from One Server to Another Server

I was recently working on a performance tuning project in Dubai (yeah I was able to see the tallest tower from the window of my work place). I had a very interesting learning experience there. There was a situation where we wanted to receive the schema of original database from a certain client. However, the client was not able to provide us any data due to privacy issues. The schema was very important because without having an access to underlying data, it was a bit difficult to judge the queries etc. For example, without any primary data, all the queries are running in 0 (zero) milliseconds and all were using nested loop as there were no data to be returned. Even though we had CPU offending queries, they were not doing anything without the data in the tables. This was really a challenge as I did not have access to production server data and I could not recreate the scenarios as production without data.

Well, I was confused but Ruben from Solid Quality Mentors, Spain taught me new tricks. He suggested that when table schema is generated, we can create the statistics consequently. Here is how we had done that:

Once statistics is created along with the schema, without data in the table, all the queries will work as how they will work on production server. This way, without access to the data, we were able to recreate the same scenario as production server on development server.

statsscript1 SQL SERVER   Copy Statistics from One Server to Another Server

statsscript2 SQL SERVER   Copy Statistics from One Server to Another Server

statsscript3 SQL SERVER   Copy Statistics from One Server to Another Server

statsscript4 SQL SERVER   Copy Statistics from One Server to Another Server

statsscript5 SQL SERVER   Copy Statistics from One Server to Another Server

statsscript6 SQL SERVER   Copy Statistics from One Server to Another Server

statsscript7 SQL SERVER   Copy Statistics from One Server to Another Server

When observed at the script, you will find that the statistics were also generated along with the query.

You will find statistics included in WITH STATS_STREAM clause.

statsscript8 SQL SERVER   Copy Statistics from One Server to Another Server

What a very simple and effective script.

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

SQLAuthority News – Statistics and Best Practices – Virtual Tech Days – Nov 22, 2010

I am honored that I have been invited to speak at Virtual TechDays on Nov 22, 2010 by Microsoft. I will be speaking on my favorite subject of Statistics and Best Practices.

This exclusive online event will have 80 deep technical sessions across 3 days – and, attendance is completely FREE. There are dedicated tracks for Architects, Software Developers/Project Managers, Infrastructure Managers/Professionals and Enterprise Developers. So, REGISTER for this exclusive online event TODAY.

statistics SQLAuthority News   Statistics and Best Practices   Virtual Tech Days   Nov 22, 2010

Statistics and Best Practices
Timing: 11:45am-12:45pm
Statistics are a key part of getting solid performance. In this session we will go over the basics of the statistics and various best practices related to Statistics. We will go over various frequently asked questions like a) when to update statistics, b) different between sync and async update of statistics c) best method to update statistics d) optimal interval of updating statistics. We will also discuss the pros and cons of the statistics update. This session is for all of you – whether you’re a DBA or developer!

You can register for this event over here.

If you have never attended my session on this subject I strongly suggest that you attend the event as this is going to be very interesting conversation between us. If you have attended this session earlier, this will contain few new information which will for sure interesting to share with all.

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

SQL SERVER – Find Automatically Created Statistics – T-SQL

Earlier, I wrote about my experience at an organization here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script. This blog post briefly narrates another experience I had at the same organization.

When I was there, I also looked at the statistics and found something that I would like to bring into the limelight. As the developers ran many non-production queries on the production server, many statistics were automatically created on the table. These stats were not useful as they were created by several queries which ran one-time or ad-hoc. Because of this, we really had to identify all the useless statistics and drop them off in order to clean up the statement.

Note: If you are going to say that it is not advisable to do as explained, I suggest you read the circumstances of the client I told in the earlier blog post.

Running the following script will return all the automatically created statistics:

SELECT name, OBJECT_NAME(OBJECT_ID)
FROM sys.stats
WHERE auto_created = 1;

You can drop the useless statistics by running the following script on each table:

DROP STATISTICS TableName.StatisticsName

Please note that not all automatically created statistics are useless, although the statistics created by the ad-hoc workload may not be that useful.

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

SQL SERVER – Outer Join Not Allowed in Indexed Views

I recently received an email that contains a question from one of my readers. I have already replied the answer to his email, but I would still like to bring it to your attention and ask if you think I could have done any better with the example I gave.

The question was raised when the email sender read the white paper, Improving Performance with SQL Server 2008 Indexed Views. If you scroll all the way down through the said white paper, there are several questions and answers.

Q: Why can’t I use OUTER JOIN in an Indexed view?

A: 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 which 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:

outerjoinmiss1 SQL SERVER   Outer Join Not Allowed in Indexed Views

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

outerjoinmiss2 SQL SERVER   Outer Join Not Allowed in Indexed Views

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 mentioned earlier, I believe this is expensive to manage for the same reason why it is not allowed in Indexed View.

Let me know if you have a better example to demonstrate this behavior in the Outer Join.

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