Why does the same query produce dramatically different plans with identical table data with different name used?

DBNewbie_2007 2016-01-22 21:11:44

I was try to test the impact of adding a "covering query" and a "filtered query" on a query to help eliminate a large table scan.

  1. Take Object16 (2nd query), make a copy (Object9).
  2. Create Clustered index, foreign keys to new Object9.
  3. Copy data from Object16 into into Object9 (1st query).
  4. Add non-clustered covering index, filtered index to Object9.
    • Compare query plans (query with Object9 still using the clustered index).
  5. Switch order of queries (original becomes 2nd query).
    • Compare query plans (no change).
  6. Add same non-clustered covering index, filtered index to Object16.
    • Compare query plans (no change).
  7. Update statistics, check index fragmentation.
    • Compare query plans (no change).

The thing I am having problems with is why Object12 and Object14 in the first query look so much different (different = actual rows) than the same Object12 and Object14 in the second query, as well as why the query plans look so different, if the only change in the query was one table?