SQL Server Index Myth 9 : Every Table Should Have a Heap/Clustered Index

Posted on September 27, 2016 by curryNcode

The last myth to consider is twofold. On the one hand, some people will recommend you should build all your tables with heaps. On the other hand, others will recommend that you create clustered indexes on all your tables. The trouble is that this viewpoint will exclude considering the benefits that each of the structures can offer on a table. The viewpoint makes a religious-styled argument for or against ways to store data in your databases without any consideration for the actual data that is being stored and how it is being used.

Continue reading →

SQL Server Index Myth 8 : Deleting form heaps results in unrecoverable space

Posted on September 27, 2016 by curryNcode

Heaps are an interesting structure in SQL Server. Heaps aren’t really an index but just a collection of pages for storing data. One of the index maintenance tasks that will be a part of the next chapter is recovering space from heap tables. As will be more deeply discussed in that chapter, when rows are deleted from a heap, the pages associated with those rows are not removed from the heap. This is generally referred to as bloat within the heap.
An interesting side effect of the concept of heap bloat is the myth that bloat never gets reused. The space stays in the heap and is not recoverable until the heap is rebuilt. Fortunately, for heaps and database administrators, this isn’t the case. When data is removed from a heap, the space that the data previously held is made available for future inserts into the table. Continue reading →

SQL Server Index Myth 5: Clustered Indexes Store Records in Physical Order

Posted on September 27, 2016 by curryNcode

One of the more pervasive myths commonly held is the idea that a clustered index stores the records in a table in their physical order when on disk. This myth seems to be primarily driven by confusion between what is stored on a page and where records are stored on those pages. There is a difference between data pages and records. As a refresher, you’ll see a simple demonstration that dispels this myth.

Continue reading →

SQL Server Index Myth 4: Any Column Can Be Filtered in Multicolumn Indexes

Posted on September 27, 2016 by curryNcode

The next common myth with indexes is that regardless of the position of the column in an index, the index can be used to filter for the column. As with the other myths discussed so far in this chapter, this one is also incorrect. An index does not need to use all the columns in a table. It does, however, need to start with the leftmost column in an index and use the columns from left to right, in order. This is why the order of the columns in an index is so important.

Continue reading →

SQL Server Index Myth 3: Online Index Operations Don’t Block

Posted on September 27, 2016 by curryNcode

One of the advantages of SQL Server Enterprise Edition is the ability to build indexes online. During an online index build, the table on which the index is being created will still be available for queries and data modifications. This feature can be extremely useful when a database needs to be accessed and maintenance windows are short to nonexistent.

Continue reading →