 <p>Reiterating a few prior posts here (which I'll vote up)...</p> <p>How selective is TypeId? If you only have 5, 10, or even 100 distinct values across your 100M+ rows, the index does nothing for you -- particularly since you're selecting all the rows anyway.</p> <p>I'd suggest creating a column on CHECKSUM(Name) in both tables seems good. Perhaps make this a persisted computed column:</p> <pre><code>CREATE TABLE BioEntity ( BioEntityId int ,Name nvarchar(4000) ,TypeId int ,NameLookup AS checksum(Name) persisted ) </code></pre> <p>and then create an index like so (I'd use clustered, but even nonclustered would help):</p> <pre><code>CREATE clustered INDEX IX_BioEntity__Lookup on BioEntity (NameLookup, TypeId) </code></pre> <p>(Check BOL, there are rules and limitations on building indexes on computed columns that may apply to your environment.)</p> <p>Done on both tables, this should provide a very selective index to support your query if it's revised like this:</p> <pre><code>SELECT EGM.Name, BioEntity.BioEntityId INTO AUX FROM EGM INNER JOIN BioEntity ON EGM.NameLookup = BioEntity.NameLookup and EGM.name = BioEntity.Name and EGM.TypeId = BioEntity.TypeId </code></pre> <p>Depending on many factors it will still run long (not least because you're copying how much data into a new table?) but this should take less than days.</p>