My Thursday commentary for the SQL Server Magazine Update e-newsletter discussed quiet changes in SQL Server 2005 and I just realized there's another one I wanted to mention. If you use Indexed Views at all, you're probably aware that there is a set of SET options that must on set appropriately in order for your indexed views to work as ...

I had been thinking of this post all day, and then noticed that Denis wrote a post with almost the same name. I was worried he might have written about something similar, but it turns out not to be the case. A group of colleagues have been having a discussion about this topic recently, which was spurred by the fact that the  Microsoft ...

I recently received a question about the storage of nonclustered index keys. I am assuming you are aware of the fact that if your table has a clustered index, SQL Server uses the clustered index key (all of its columns, if it is a composite index) as a 'bookmark' in your nonclustered indexes,  to allow your nonclustered indexes to uniquely ...

Yet another fragmentation post, to answer a question asked in response to a previous answer.... SQL_Girl is still confused about DETAILED vs LIMITED, and I don't blame her. She reported that the BOL says:  ''The nonleaf levels of indexes are only processed when mode = DETAILED. ''  But I had said: ''The second misunderstanding involves ...

  A couple of questions arrived regarding my earlier post about fragmentation on the system tables, and since the questions really don't have anything to do with system tables, I felt they deserved a new post. 1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages).  According to many source, this ...

This is not a post about Jim Gray, much as I wish I had some news to share. It's about one of the reasons you should be really careful about using undocumented trace flags. I always try to warn people, whether I'm writing or speaking, about the dangers inherent when using any undocumented feature. Undocumented means unsupported! Microsoft retains ...

  What is the difference between statistics and indexes? Do auto created statistics indicate that you should build an index? I will be posting answers to some of the interesting questions I get from my students and my readers (these are not two disjoint sets, of course) on the new SQLCommunity site. Check out this the answer to this ...

When an issue comes up more than once in a short period of time, I figure it is a message from the Universe. I might even assume it is a gift of blog fodder. So I won't turn down the gift, even if it means another juicy post before the end of year, much to Denis' disappointment. The issue is SQL Server 2005 ''Included Columns''. There was a post ...

Got this question from a newsgroup today. The answer is pretty simple, just use the dynamic management view sys.dm_db_index_physical_stats. I'm posting this here mostly so I have somewhere to refer to when asked this question... I prefer to have a helper function to get the index ...