Browse the archive
Statistics auto-updates are generally a good thing–auto-update of statistics is the default behavior in SQL Server. Having the freshest information about the data “shape” helps to inform the best selection of an execution plan for queries. Except for when it …
Continue readingQuestion I just learned that indexes can be disabled on a table. Even the clustered index can be disabled. After that, users can’t access the index or the data if clustered index. When will we disable an index? I just don’t understand the use case.
Continue readingWe’ve all been there. You’ve got a query where the JOIN or WHERE predicate is not SARGable. You’ve read about how this can be a problem, and how bad it is for performance. Alas, you cannot change the query. Sometimes this reason is political, sometimes it’s …
Continue readingThese vines are clustered by Cabernet Sauvignon. #### First off, let's remember the difference between clustered & nonclustered indexes The clustered index is organized by the key columns. It also includes every other column as part of the row structure (ie, …
Continue readingQuestion In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object’s bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index key at all …
Continue readingQuestion Does it ever make sense to have a clustered and non clustered index for the same column I inherited a database and a web service that goes with it. Looking at the indexes for duplicates I see an two indexes that point at the same column (1 column and …
Continue readingSQL Server supports two kinds of data compression on rowstore data. Columnstore compression is for a different day. You can compress heaps, clustered indexes (on both tables and views), and nonclustered indexes (on both tables and views). Compression …
Continue readingSQL Server supports two kinds of data compression on rowstore data. Columnstore compression is for a different day. You can compress heaps, clustered indexes (on both tables and views), and nonclustered indexes (on both tables and views). Compression …
Continue readingSQL Server supports two kinds of data compression on rowstore data. Columnstore compression is for a different day. You can compress heaps, clustered indexes (on both tables and views), and nonclustered indexes (on both tables and views). Compression …
Continue readingAt work, we make extensive use of views & synonyms. The reasons are complex (and fodder for another post), but here’s a simplified example: Pricing data is updated multiple times per day, and we want to keep multiple generations of the pricing table online. By …
Continue reading
