SQL Server Index Myth 1 : Databases Don’t Need Indexes

Along with this, there are developers who believe their databases are somehow unique from other databases. The following are some reasons that are heard from time to time:

• “It’s a small database that won’t get much data.”
• “It’s just a proof of concept and won’t be around for long.”
• “It’s not an important application, so performance isn’t important.”
• “The whole database already fits into memory; indexes will just make it require more memory.”
• “I am going to use this database only for inserting data; I will never look at the results.”

Each of these reasons is easy to break down. In today’s world of big data, even databases that are expected to be small can start growing quickly as they are adopted. Besides that, small in terms of a database is definitely in the eye of the beholder. Any proof-of-concept or unimportant database and application wouldn’t have been created if there weren’t a need or someone wasn’t interested in expending resources for the features. Those same people likely expect that the features they asked for will perform as expected. Lastly, fitting a database into memory doesn’t mean it will be fast. As was discussed in previous chapters, indexes provide an alternative access path to data, with the aim of decreasing the number of pages required to access the data. Without these alternative routes, data access will likely require reading every page of a table.

These reasons may not be the ones you hear concerning your databases, but they will likely be similar. The general idea surrounding this myth is that indexes don’t help the database perform better. One of the strongest ways to break apart this excuse is by demonstrating the benefits of indexing against a given scenario.

To demonstrate, let’s look at the code in Listing 1. This code sample creates the table MythOne. Next, you will find a query similar to one in almost any application. In the output from the query, in Listing 2, the query generated 1,496 reads.

SELECT * INTO MythOne FROM Sales.SalesOrderDetail;
GO
SET STATISTICS IO ON
SET NOCOUNT ON
GO
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal
FROM MythOne
WHERE CarrierTrackingNumber = '4911-403C-98';
GO
SET STATISTICS IO OFF
GO

Table 'MythOne'. Scan count 1,
logical reads 1496, 
physical reads 0, 
read-ahead reads 0,
lob logical reads 0, 
lob physical reads 0,
lob read-ahead reads 0.

CREATE INDEX IX_CarrierTrackingNumber ON MythOne (CarrierTrackingNumber)
GO
SET STATISTICS IO ON
SET NOCOUNT ON
GO
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal
FROM MythOne
WHERE CarrierTrackingNumber = '4911-403C-98';
GO
SET STATISTICS IO OFF
GO

Table 'MythOne'. Scan count 1,
logical reads 15 physical reads 0, 
read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.