KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
SQL 2008. I have a test table: 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 ) I populate it with 10k test rows. declare @RowCount int = 10000 while(@RowCount > 0) begin insert Sale default values set @RowCount -= 1 end I run these two queries: -- Query #1 select * from Sale where RowVersion > 0x000000000001C310 -- Query #2 declare @LastVersion rowversion = 0x000000000001C310 select * from Sale where RowVersion > @LastVersion I can't figure out why these two queries have different execution plan. Query #1 does index seek against UQ_Sale_RowVersion index. Query #2 does index scan against PK_Sale. I want query #2 to do index seek. I would appreciate some help. Thank you. [Edit] Tried using datetime2 instead of rowversion. The same issue. I tried to force using index too (query #3) select * from Sale with (index = IX_Sale_RowVersion) where RowVersion > @LastVersion This seemed to show the same query execution plan as the query #1, but execution plan showed this query #3 as the most expensive among all those 3 queries. [Edit] Execution plan: <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"
Tags (comma-separated)
Save Edits
Cancel