Ok. Computed column sounds like the obvious choice then.

With the first option I mean that the new column you are thinking of adding is (prefix+ streetname+ suffix), but if you are not going to be using "streetname" part...

Start by looking at the query plan. If possible, post the queries and plan in the thread.

You can write a simple .net application which will read excel files, manipulate data as you want and then insert into database.

There are couple of options:

1. Do you use actual street name anywhere else. Or else you can insert the street name with prefix and suffix. This would be the most...

To Add to Steve's answer, You may need to see if the existing data is upgradable to new type or not and then write an upgrade script.

Thanks for the quick reply. What I meant to say is I was able to produce deadlocks even with the indexed tvps.

Hi @opc,

I did some testing with the indexed tvps interms of deadlocks. I did not find any difference between non-indexed and indexed versions. I created a simple application which spaws...

Thanks opc.

I missed the point with the deadlock in-case of inserts. This is an important factor in case of high transaction environment.

Thanks again.

Thanks everybody for your responses. I could not post the table structure etc because of policies. Here is what I found out after more testing.

From the execution plans, it looks...

From the plan, it can be seen that the new index is used. It also saves an extra Sort operation on the tvp.

@r.P.Rozema

Thanks for the reply.

The foreign key of A goes into 2 more tables just like table B. But those are not huge tables like table B. So idea...

@r.P.Rozema

I did try left outer with null clause as well. It comes out with the same execution plan and takes same time.

@sqlknowitall

I already tried separation of select, it is the Index scan in the select that is taking most of the time. I think as engine has to check...

@sqlknowitall

Yes. it does have a foreign key in table B. I agree it may not be needed since it is also a clustered PK on the same table.