Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is my first time handling a large database, and I was wondering why the two queries below are different in terms of execution plan.

I have setup an index on a specific table:

CREATE NONCLUSTERED INDEX [IX_UBHSD_xploc_trandt2] ON [dbo].[UBHSD]
(
    [xploc] ASC, [trandt2] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
        DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
        ON [PRIMARY]
GO

When I ran this query, it uses the index.

SELECT COUNT(*) as cnt 
FROM [dbo].[UBHSD] 
WHERE [xploc] != '' AND [trandt2] >= CAST('2012-01-01' AS DATETIME)

enter image description here

Here's a larger one.

But when I ran a query without using aggregate:

SELECT *
FROM [dbo].[UBHSD] 
WHERE [xploc] != '' AND [trandt2] >= CAST('2008-01-01' AS DATETIME)

enter image description here

Here's a larger one.

I was expecting that both queries would use an index. But the second query performs a full table scan.

Why is this happening? Did I miss something?

share|improve this question
3  
You have SELECT * in the second, that's the crucial difference. You ask for columns in the result which are not included in the index. –  ypercube Sep 5 '13 at 11:26
    
@ypercube thanks for the comment. I though indexes work only for filtering mechanism. If that's the case, any columns that are needed should have an index? Sorry for this, I'm new with thing. –  FireExit Sep 5 '13 at 12:54
add comment

closed as off-topic by Mark Storey-Smith, StanleyJohns, dezso, bluefeet, Paul White Sep 6 '13 at 11:45

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – StanleyJohns, dezso, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

You've used SELECT * which, presumably, has more columns than xploc and trandt2. So SQL Server can easily derive the count by traversing the index pages. It can't get the other columns that aren't in the index from there, so it has to go to the table.

Depending on how many columns are in the table (and a host of other factors), SQL Server can usually make one of two choices in a case like this:

  1. Perform a seek (which looks like a range scan in your case), and then perform key/RID lookups to retrieve the other columns not covered by the index.
  2. Suck it up and perform a scan.

In your case there are probably enough rows satisfied by the WHERE clause that it is actually more efficient to perform the scan.

Some ways to make this more efficient:

  1. Stop using SELECT * - just list the columns you need.
  2. Once you have whittled down to the actual columns you need, change the index to INCLUDE the other columns in the actual SELECT list you need.
  3. You could consider making an index filtered by adding a WHERE clause, depending on whether this exact query is one that is run frequently (and if there is a lot of data pre-2012).

As an aside, why is there no clustered index on this table?

share|improve this answer
1  
+1 for point #1 –  Kermit Sep 5 '13 at 12:32
    
Thank you for the response sir. There is a clustered index and it's the primary key. However, how will I improve this query? All the columns are needed since I'm migrating from very old database into a new one. The old database was now hosted on sql server 2008. What possible things will I do so I can optimize the query more? –  FireExit Sep 5 '13 at 12:52
    
@FreshPrinceOfSO which no.1 do you mean, the first or the second. if that's the second, I need all columns since I'm migrating to a new database. –  FireExit Sep 5 '13 at 12:56
1  
@FireExit #2.1 Why are you concerned with an index being used if you're migrating to a new database? Are you planning on doing a migration every 30 minutes? –  Kermit Sep 5 '13 at 13:03
    
@JW웃 are you and FireExit the same person? –  Aaron Bertrand Sep 5 '13 at 15:09
show 1 more comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.