SQL should generate the same query plan for either query.
You'll want some type of index to contain "FilterField" assuming that it is generally selective. That is, that only a limited number of rows will generally match the value you search on. If, for example, 10% or more of rows commonly match the filter, then SQL will just likely scan the full table anyway.
A temp table without an index does not automatically slow down a query, but it can. It depends.
Hi Scott, can I add an index to a temp table because I tend to use them a lot since I use the data in a couple of places. When the session drops, it will remove the temp table but will it automatically remove the index?
Also, if I limit the number of fields, would that help on the performance? This table has over 150 fields and a million records.
Also, should I use "no lock"?
Last, this query actually ran faster than the above two:
SELECT * FROM T1
INNER JOIN T2 ON T2.ID = T1.ID AND T1.FilterField = @pi_Filter
If you put an index on a table, esp. a temp table, that doesn't already have an index, be sure to use a clustered index. There's almost never any point in putting a non-clus index on a heap (a table w/o a clus index).