GilaMonster (9/13/2012)
scogeb (9/13/2012)
Jeff Moden (9/12/2012)
Have you tried the NOT IN without the DISTINCT?Also, what's with all the LTRIM/RTRIM stuff? Data should be properly stored without leading spaces and trailing spaces hardly ever matter except when determining the actual number of bytes a strinng may contain.
Yep, same deal with NOT IN without the DISTINCT, 100% CPU for way too long.
Yeah, it will be. SQL ignores DISTINCT when specified in an IN/NOT IN subquery.
Thanks, Gail. I learned something new today.
Just to verify, I tried it a couple of different ways. On larger tables, it'll sometimes add a "Sort (Distinct Sort)" to the execution plan if the optimizer thinks it will help but I couldn't get it to add such a sort using DISTINCT on smaller tables.
--Jeff Moden
Change is inevitable... Change for the better is not.