“Included columns” for non-clustered indexes explained

Microsoft SQL Server has the feature to use “Included columns” for the creation of non-clustered indexes. This feature and its benefits and disadvantages will be explained in the following article. Included columns can be used to create a covering indexes without including all the data into the key columns. This covering index has the advantage […]

Reindexing a database

How much will it cost or save to rebuild that index? (SQL Oolie)

What's the true and permanent cost of lowering the Fill Factor of an index in SQL Server? It's a lot more than many people think. 9 year SQL Server MVP veteran, Jeff Moden, demonstrates how to calculate the extra space that will be used by lowering the Fill Factor and saved by increasing it.

A Self-Tuning Fill Factor Technique for SQL Server – Part 2

Introduction Fill Factor is a parameter most of us have ignored because there is no definitive way to calculate what the correct value should be. Part 1 of this series describes a new technique to dynamically determine index fill factors for all indexes within a database. Part 2 covers an analysis of the data collection […]

Some T-SQL INSERTs DO Follow the Fill Factor! (SQL Oolie)

With origins from the world of “Submarine ‘Dolphin’ Qualification” questions, an “Oolie” is a difficult question to answer, or the knowledge or fact needed to answer such a question, that may or may not pertain to one's duties but tests one's knowledge of a system or process to the limit. Introduction Contrary to what many […]

What happens when you cancel or kill a resumable index creation?

SQL Server 2019 adds resumable online index creation, and it’s pretty spiffy according to Brent.

Are nulls stored in a nonclustered index?

When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it.

Speedy Table Variables

Can deleting rows make a table…bigger?

Michael J. Swart posted an interesting question: he had a large table with 7.5 billion rows and 5 indexes. When he deleted 10 million rows, he noticed that the indexes were getting larger, not smaller.

Indexing Computed Columns