New answers tagged optimizer
The reason for the behavior is that SQL Server can't determine how many rows will match to ForeignKey, since there is no index with RowKey as the leading column (it can deduce this from statistics on the #temp table, but those don't exist for table variables/UDTTs), so it makes an estimate of 100,000 rows, which is better handled with a scan than a ...
Table variables and temp tables are handled differently in a number of ways. There is a great answer here with lots of specifics as to where they are different. Specifically in your case I would guess that the fact that temp tables can have additional statistics generated and parallel plans while table variables have more limited statistics (no column ...
Try: WHERE FileName >= N'ATT000' AND filename < N'ATT001' And make sure you have included columns to avoid lookups.
Top 50 recent answers are included