This indicates a parallel execution plan.

>>Each index is on one column only, and none of them are unique or clustered.

Without a clustered index, you likely have a large fragmented heap. More disk I/O to fetch...

>>to pass several similar values

Do you mean an "array" that may have a variable number of "rows" ?

You can pass it as XML and use OpenXML inside the SP...

That's why I suggested DTS.

Load the text file to a staging/temp table. Give the table 1 extra column for an error code and default it to zero.

Run validation SQL task,...

>>How can I accomplish this through a single SP.

Why are you restricted to a single SP ?

This is the sort of task that a DTS package would typically be used...

A Select statement using a function to generate a column. So far so good - looks harmless enough:

Select dbo.SomeFunction(t.Column1) As FunctionResult

From YourTable As t

Where ...

The...

Your EXISTS is invalid syntax, and even if correct, would not give an equivalent resultset.

Also note the original problem:

>>3 total linked servers the select runs but it does not run with...

>>Correct me if I'm wrong but table variables are better to use than temp tables cause you can run into tempdb locks

Not necessarily. It depends on whether the #temp...

They both serve a different purpose, so performance comparison is not really applicable.

>>however I don't have a choice about how to form the sql since it is being generated by a middle-ware process.

In that case you have no other choice than to...

The alias doesn't apply at that point in the SQL parsing/execution. Whatever expression you're aliasing in the SELECT needs to be replicated in the FROM/JOIN section.

If it's a large, complex...

I doubt there's any performance difference. I expect someone who didn't know SQL tried to use " <> NULL", found it didn't work, and rather than learning about NULLs, found...

The problem is not the UNION.

The problem is all the sub-SELECTs within each SELECT, eg:

(Select distinct 'Y' FROM DataWarehouse.dbo.WhseOpenOrders WHERE PartNumber = D.PartNumber) as CrossDock

When you place a SELECT within...

Join to a derived table that gives the MIN() ID (or MAX(), whatever you want) per dupe:

SELECT t.iTmPunchTimeSummaryId, t.sCalldate, t.sEmployeeId, t.dTotalHrs

FROM tmPunchtimeSummary As t

INNER JOIN

(

  SELECT MIN(iTmPunchTimeSummaryId) As RetainedID

 ...

>>1) Select distinct * into #t1 from <table_name>

That won't work - there's an Identity column, so Select Distinct * changes nothing since every record is distinct due to ascending identity...