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)
But when I ran a query without using aggregate:
SELECT * FROM [dbo].[UBHSD] WHERE [xploc] != '' AND [trandt2] >= CAST('2008-01-01' AS DATETIME)
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?