Thanks Lynn. But it turns out you CAN insert into a temp table (the single # kind) using dynamic sql, and I was just being an idiot.

Here's the working...

There's no proof like pudding Mark.

Pretty sure you can only have one table at a time with IDENTITY_INSERT ON. I'm not in front of QA right now... can anyone confirm?

Why is it taking just as long when I drop all the non-clustered indexes first?

What else is it doing?

thanks fellas.  I'm actually getting the quickest times using the concatenate and NOT IN clause.  Could be a fluke, but I made sure to run freeproccache and dropcleanbuffers first.

I'll revisit...

OK, here's a wrap up.  I think I was expecting a lot more out of the partitioned views, so I kept on trying to find reasons why they weren't working. ...

The partitioned view is used as the source of a temp table population.

I ran the code as-is... FROM the partitioned view, WHERE we filter on the partitioning column using a...

I'll check the times now SQLZ and post them presently.

Thanks for the input Philip, and I considered the fact that the low number of rows could be causing the 33, 33, 33, but I'm seeing a congruous behavior in...

dbcc freeproccache did nothing but cause SQL to take longer to come up with the same crappy plan.

was that how I was supposed to clean the plan cache?

Actual execution plans showing 1-2% on all 60 underlyers as well. 

I just realized that if I used a hard coded value in my "WHERE part_col" = XXX clause, the query plan comes out PERFECT, but when I use a variable...

I'm going to start a new thread for this, but wanted to post something here as well...

Our problems are a little different as I'm not having trouble ONLY when I...

Not sure which change did the trick...

I dropped all check constraints on the underlying tables.

I dropped the view.

Recreated all the check constraints.

Recreated the view.

Updated statistics on underlying tables.

I'm now getting...

Thanks Karl.

My times are written in min:sec format, so the difference is 10 seconds, not .1 sec.

All Check constraints on the table are ENabled.

Yeah, if it was scanning all tables,...