index on computed column ignored

Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)
If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)