would it be worth trying to add a non-clustered index to the temp table to try and get ride of the key lookup?

drop it into a text file and attach the text file.

where did you hear that from?

It is true that you can hinder the perform of an update by having to many indexes on a table as you will be updating...

yes you can

Well they mena completely different things

what values are stored in that column? Do you store nulls or do you store the text 'null'?

are you sure this is what you want?

select count(*) from AuditData where TATCallType = 'null'

And not

select count(*) from AuditData where TATCallType is null

From your last spreadsheet we can see there is a clustered index being used.

I'm going to say that creating a non-clustered index covering you where clause and your selet output...

you could also post all the indexes on your table.

I have a feeling you have a clustered index on that table that you haven't mentioned yet.

I also have a feeling...

Always happy to help 🙂

OK so now we getting somewhere.

what indexes do you have on the table you inserting into?

Also do you have any triggers on that table.

I would also take note of Dave's...

Here is another solution.

This is based on two thing.

1 - Columns are not if not being used.

2 - The number of columns is known.

SELECT

Col0,

COALESCE(Col1,Col2,Col3,Col4,Col5)

FROM @MyTable

I'm unclear, on why it needs to be dynamic?

are you saying that you never know how many columns there are in the table?

Secondly why is your table constructed this way.

Could...

how long does the actuall select part of you query take without the insert?

I can't really read that plan so I'm not sure what the stats are on the...

Is this the kind of thing you looking for?

DECLARE @MyTable TABLE

([col0] CHAR(1),

[col1] CHAR(2),

[col2] CHAR(2),

[col3] CHAR(2),

[col4] CHAR(2),

[col5] CHAR(2))

INSERT INTO @MyTable

SELECT 'a','xa',null,null,null,null UNION ALL

SELECT 'b',null,'xb',null,null,null UNION ALL

SELECT 'c',null,null,'xc',null,null UNION ALL

SELECT 'd',null,null,null,'xd',null...

could you post the actual execution plan, as a *.sqlplan zipped up and attached to the job.

Could you also give me a little break down what you trying to return...