NULL Returned for Populated Column

Here is a strange one:

The following partitioned table (partitioned for 31 days) with a single nonclustered, nonunique index is behaving improperly on a select. A standard select using the index returns all NULL values for altitude yet if I do a select for the TOP x rows ordered by reporttime desc for not null altitudes, I see the values stored in the column. There are over 5,000,000 rows in the table with a relatively even distribution over each partition (except partition 1 which is kept empty).

CREATE TABLE [dbo].[APRSTrack](
[CallsignSSID] [varchar](9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ReportTime] [datetime] NOT NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[Icon] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Course] [smallint] NULL,
[Speed] [int] NULL,
[Altitude] [int] NULL
) ON [onemonth]([ReportTime])

CREATE NONCLUSTERED INDEX [IX_APRSTrack] ON [dbo].[APRSTrack]
(
[CallsignSSID] ASC,
[ReportTime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [onemonth]([ReportTime])

The failing select statement is:

SELECT *
FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime

and the working select statement is:

select top 100 *
from APRSTrack
where (not (altitude is null)) and reporttime >= '2006-06-06'
order by reporttime desc

The execution plan for the first lookup is:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2.67004" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00992778" StatementText="SELECT *&#xD;&#xA; FROM [Ham Radio].[dbo].[APRSTrack]&#xD;&#xA;where callsignssid = 'EA3ABN-9' and reporttime &gt;= '2006-06-06'&#xD;&#xA;order by reporttime" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="60">
<RelOp AvgRowSize="54" EstimateCPU="1.11608E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00992778">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="PtnIds1007" />
<ColumnReference Column="Bmk1000" />
</OuterReferences>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
<RelOp AvgRowSize="38" EstimateCPU="2.67004E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032852">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Column="PtnIds1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="PtnIds1007" />
<ScalarOperator ScalarString="RangePartitionNew([Ham Radio].[dbo].[APRSTrack].[ReportTime],(1),'2006-05-07 00:00:00.000','2006-05-08 00:00:00.000','2006-05-09 00:00:00.000','2006-05-10 00:00:00.000','2006-05-11 00:00:00.000','2006-05-12 00:00:00.000','2006-05-13 00:00:00.000','2006-05-14 00:00:00.000','2006-05-15 00:00:00.000','2006-05-16 00:00:00.000','2006-05-17 00:00:00.000','2006-05-18 00:00:00.000','2006-05-19 00:00:00.000','2006-05-20 00:00:00.000','2006-05-21 00:00:00.000','2006-05-22 00:00:00.000','2006-05-23 00:00:00.000','2006-05-24 00:00:00.000','2006-05-25 00:00:00.000','2006-05-26 00:00:00.000','2006-05-27 00:00:00.000','2006-05-28 00:00:00.000','2006-05-29 00:00:00.000','2006-05-30 00:00:00.000','2006-05-31 00:00:00.000','2006-06-01 00:00:00.000','2006-06-02 00:00:00.000','2006-06-03 00:00:00.000','2006-06-04 00:00:00.000','2006-06-05 00:00:00.000','2006-06-06 00:00:00.000')">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-07 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-08 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-09 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-10 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-11 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-12 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-13 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-14 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-15 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-16 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-17 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-18 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-19 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-20 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-21 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-22 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-23 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-24 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-25 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-26 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-27 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-28 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-29 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-30 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-31 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-01 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-02 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-03 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-04 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-05 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="34" EstimateCPU="0.000159937" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328494">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Index="[IX_APRSTrack]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'EA3ABN-9'">
<Const ConstValue="'EA3ABN-9'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2006-06-06 00:00:00.000'">
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator ScalarString="(32)">
<Const ConstValue="(32)" />
</ScalarOperator>
</ColumnReference>
</PartitionId>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="35" EstimateCPU="0.000251393" EstimateIO="0.003125" EstimateRebinds="1.67004" EstimateRewinds="0" EstimateRows="1" LogicalOp="RID Lookup" NodeId="7" Parallel="false" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="0.00663141">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1000]">
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@.2" ParameterCompiledValue="'2006-06-06'" />
<ColumnReference Column="@.1" ParameterCompiledValue="'EA3ABN-9'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Any ideas?

Pete Loveall
AME Corp.

This is definitely an issue with the nonclustered index. I changed the index to a clustered index and everything works fine. In fact, the table originally had a clustered index just on reporttime and IX_APRSTrack was a nonclustered index. When I dropped the reporttime clustered index, all entries added after that point were improperly displayed in any query using the nonclustered index. Once I changed the nonclustered index to a clustered index, every row displays properly now including those that did not display properly previously.

SQL 2005 query has some definite issues with nonclustered indexes. Some of those issues may be related to partioned indexes on partitioned tables, but the nonclustered seeks are definitely wrong. The indexes and databases check just fine using CHECKDB. I also notice that doing a search using a nonclustered index which should be confined to one or two partitions is actually done across all partitions (see the execution plan above). The same SELECT done against a clustered index restricts the lookup to the relavent partitions.

This table has a high rate of insertions which make the clustered index undesireable. However, if that is what must be done until Microsoft fixes the query generator, we'll limp along.

Microsoft, are you listening?

Pete Loveall
AME Corp.