Query does not use index when selecting all records 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] &gt;= CAST('2012-01-01' AS DATETIME)




Here's a larger one.

But when I ran a query without using aggregate:

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




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?
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:


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.
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:


Stop using SELECT * - just list the columns you need.
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.
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?