CODEWITHSUNDEEP
sqlsql-serverindexing
Alice
Alice

Reputation: 103

SQL Server index included columns

I need help understanding how to create indexes. I have a table that looks like this

My query looks like this:

SELECT * 
  FROM table1 
 WHERE name = 'sam'
  1. What's the correct way to create an index for this with included columns?

  2. What if the query has a order by statement?

      SELECT * 
    FROM table1 
   WHERE name = 'sam'
ORDER BY id DESC

  3. What if I have 2 parameters in my where statement?

      SELECT * 
    FROM table1 
   WHERE name = 'sam'
     AND age > 12

Upvotes: 2

Views: 3430

Answers (4)

JNK
JNK

Reputation: 65147

If ID is your primary and/or clustered index key, just create an index on Name, Age. This will cover all three queries.

Included fields are best used to retrieve row-level values for columns that are not in the filter list, or to retrieve aggregate values where the sorted field is in the GROUP BY clause.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332541

The correct way to create an index with included columns? Either via Management Studio/Toad/etc, or SQL (documentation):

CREATE INDEX idx_table_1 ON db.table_1 (name) INCLUDE (id)

What if the Query has an ORDER BY

The ORDER BY can use indexes, if the optimizer sees fit to (determined by table statistics & query). It's up to you to test if a composite index or an index with INCLUDE columns works best by reviewing the query cost.

If id is the clustered key (not always the primary key though), I probably wouldn't INCLUDE the column...

What if I have 2 parameters in my where statement?

Same as above - you need to test what works best for your query. Might be composite, or include, or separate indexes.

But keep in mind that:

  • tweaking for one query won't necessarily benefit every other query
  • indexes do slow down INSERT/UPDATE/DELETE statements, and require maintenance
  • You can use the Database Tuning Advisor (DTA) for index recommendations, including when some are redundant

Recommended reading

I highly recommend reading Kimberly Tripp's "The Tipping Point" for a better understanding of index decisions and impacts.

Upvotes: 5

platon
platon

Reputation: 5340

Since I do not know which exactly tasks your DB is going to implement and how many records in it, I would suggest that you take a look at the Index Basics MSDN article. It will allow you to decide yourself which indexes to create.

Upvotes: 0

Juris Malinens
Juris Malinens

Reputation: 1271

If inserts are rare, create as much indexes as You want.

For first query create index for name column.

Id column I think already is primary key...

Create 2nd index with name and age. You can keep only one index: 'name, ag'e and it will not be much slower for 1st query.

Upvotes: -1

Related Questions