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


Usually when developers are building applications, one or more databases are created to store data for the application. In many development processes, the focus is on adding new features with the mantra “Performance will work itself out.” An unfortunate result is that there are many databases that get developed and deployed without indexes being built because of the belief that they aren’t needed.

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s