SQL 2008。
我有一个测试表：
create table Sale
(
SaleId int identity(1, 1)
constraint PK_Sale primary key,
Test1 varchar(10) null,
RowVersion rowversion not null
constraint UQ_Sale_RowVersion unique
)
我用 10k 测试行填充它。
declare @RowCount int = 10000
while(@RowCount > 0)
begin
insert Sale default values
set @RowCount -= 1
end
我运行这两个查询：
-- Query #1
select *
from Sale
where RowVersion > 0x000000000001C310
-- Query #2
declare @LastVersion rowversion = 0x000000000001C310
select *
from Sale
where RowVersion > @LastVersion
我不明白为什么这两个查询有不同的执行计划。
查询#1 针对 UQ_Sale_RowVersion 索引进行索引查找。
查询 #2 对 PK_Sale 进行索引扫描。
我希望查询#2 进行索引查找。
我希望得到一些帮助。
谢谢。
[Edit]
尝试使用 datetime2 而不是 rowversion。同样的问题。
我尝试强制使用索引（查询#3）
select *
from Sale with (index = IX_Sale_RowVersion)
where RowVersion > @LastVersion
这似乎显示与查询 #1 相同的查询执行计划，但执行计划显示此查询 #3 是所有这 3 个查询中最昂贵的。
[Edit]执行计划：
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.1"
Build="10.50.1600.1">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="-- Query #1

select *
from Sale
where RowVersion > 0x000000000001C310

-- Query #2

"
StatementId="1"
StatementCompId="1"
StatementType="SELECT"
StatementSubTreeCost="0.00657038"
StatementEstRows="1"
StatementOptmLevel="FULL"
QueryHash="0xE442FF9A4A2A630A"
QueryPlanHash="0x347569CFDEF2A13F"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
ParameterizedText="(@1 varbinary(8000))SELECT * FROM [Sale] WHERE [RowVersion]>@1">
<StatementSetOptions QUOTED_IDENTIFIER="true"
ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="true"
ANSI_NULLS="true"
ANSI_PADDING="true"
ANSI_WARNINGS="true"
NUMERIC_ROUNDABORT="false"/>
<QueryPlan CachedPlanSize="24"
CompileTime="1"
CompileCPU="1"
CompileMemory="136">
<RelOp NodeId="0"
PhysicalOp="Nested Loops"
LogicalOp="Inner Join"
EstimateRows="1"
EstimateIO="0"
EstimateCPU="4.18e-006"
AvgRowSize="28"
EstimatedTotalSubtreeCost="0.00657038"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</OuterReferences>
<RelOp NodeId="1"
PhysicalOp="Index Seek"
LogicalOp="Index Seek"
EstimateRows="1"
EstimateIO="0.003125"
EstimateCPU="0.0001581"
AvgRowSize="19"
EstimatedTotalSubtreeCost="0.0032831"
TableCardinality="10000"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</OutputList>
<IndexScan Ordered="1"
ScanDirection="FORWARD"
ForcedIndex="0"
ForceSeek="0"
NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Index="[UQ_Sale_RowVersion]"
IndexKind="NonClustered"/>
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="0x000000000001C310">
<Const ConstValue="0x000000000001C310"/>
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp NodeId="3"
PhysicalOp="Clustered Index Seek"
LogicalOp="Clustered Index Seek"
EstimateRows="1"
EstimateIO="0.003125"
EstimateCPU="0.0001581"
AvgRowSize="16"
EstimatedTotalSubtreeCost="0.0032831"
TableCardinality="10000"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
</OutputList>
<IndexScan Lookup="1"
Ordered="1"
ScanDirection="FORWARD"
ForcedIndex="0"
ForceSeek="0"
NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Index="[PK_Sale]"
TableReferenceId="-1"
IndexKind="Clustered"/>
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[AdventureWorks].[dbo].[Sale].[SaleId]">
<Identifier>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@1"
ParameterCompiledValue="0x000000000001C310"/>
</ParameterList>
</QueryPlan>
</StmtSimple>
<StmtSimple StatementText="
declare @LastVersion rowversion = 0x000000000001C310

"
StatementId="2"
StatementCompId="2"
StatementType="ASSIGN"/>
<StmtSimple StatementText="
select *
from Sale
where RowVersion > @LastVersion"
StatementId="3"
StatementCompId="3"
StatementType="SELECT"
StatementSubTreeCost="0.0328005"
StatementEstRows="3000"
StatementOptmLevel="FULL"
QueryHash="0xE442FF9A4A2A630A"
QueryPlanHash="0x0C6238F821406F2B"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<StatementSetOptions QUOTED_IDENTIFIER="true"
ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="true"
ANSI_NULLS="true"
ANSI_PADDING="true"
ANSI_WARNINGS="true"
NUMERIC_ROUNDABORT="false"/>
<QueryPlan CachedPlanSize="16"
CompileTime="1"
CompileCPU="1"
CompileMemory="144">
<RelOp NodeId="0"
PhysicalOp="Clustered Index Scan"
LogicalOp="Clustered Index Scan"
EstimateRows="3000"
EstimateIO="0.0216435"
EstimateCPU="0.011157"
AvgRowSize="28"
EstimatedTotalSubtreeCost="0.0328005"
TableCardinality="10000"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</OutputList>
<IndexScan Ordered="0"
ForcedIndex="0"
NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Index="[PK_Sale]"
IndexKind="Clustered"/>
<Predicate>
<ScalarOperator ScalarString="[AdventureWorks].[dbo].[Sale].[RowVersion]>[@LastVersion]">
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@LastVersion"/>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
