title: SQL Server: Estimated number of rows incorrect when WHERE data type not casted
body: <p>I'm debugging a performance issue in a SQL Server 2008 R2 (10.50.2500) data warehouse and I came across a "strange" behaviour in cardinality estimation. I have lots of staging tables with a rowversion column. The purpose of the column is to identify new/updated rows that are later loaded into the fact tables. All these tables have a clustered index on the rowversion column. I've manually updated the statistics of this index - using the WITH FULLSCAN option.</p>
  
  <p>In the queries that select the data from these staging tables it seems that I need to explicitly cast the WHERE condition into the rowversion datatype. Otherwise the estimated number of rows is incorrect.</p>
  
  <p>Example:</p>
  
  <p>1.</p>
  
  <pre><code>SELECT * FROM stage.LedgerEntryDimension 
  WHERE StageTimestamp &gt; 2502412 -- int
  </code></pre>
  
  <p>Estimated vs. Actual Number of Rows: 741556 vs. 19923</p>
  
  <p>2.</p>
  
  <pre><code>SELECT * FROM stage.LedgerEntryDimension
  WHERE StageTimestamp &gt; 0x0000000000262F0C -- hex format 
  </code></pre>
  
  <p>Estimated vs. Actual Number of Rows: 1 vs. 19923</p>
  
  <p>3.</p>
  
  <pre><code>SELECT * FROM stage.LedgerEntryDimension
  WHERE StageTimestamp &gt; CAST(2502412 AS binary(8))
  </code></pre>
  
  <p>Estimated vs. Actual Number of Rows: 1 vs. 19923</p>
  
  <p>4.</p>
  
  <pre><code>SELECT * FROM stage.LedgerEntryDimension
  WHERE StageTimestamp &gt; CAST(2502412 AS rowversion)
  </code></pre>
  
  <p>Estimated vs. Actual Number of Rows: 19624,17 vs. 19923 (correct)</p>
  
  <p>How come that SQL Server cardinality estimation is not correct if data type is not explicitly casted? What is the underlying reason for this behaviour?</p>
  
  <p>On the MSDN site for the rowversion data type it states that the rownumber is "semantically equivalent to the binary(8) data type". </p>