Nicely done.  My only concern would be that since the DBA won't allow the OP to create the fnTally function, will they also forbid the OP from calling a...

Here's my attempt:

;with TestData as 
(
    SELECT *
      FROM (VALUES ('A[kfg]'),
        ...

I don't think...

Hi

Thanks a lot.

I've run your edited query. but its cost was 51% against last improved query that I sent in my last post. And Logical Read...

Hi

Thanks a lot.

I've run your edited query. but its cost was 51% against last improved query that I sent in my last post. And Logical Read grows up for...

I've rewritten your query to use EXISTS instead of IN. Removed the UNION ALL and change the comma separate table names to INNER JOIN. I think it's a better starting...

Although the SQL online documentation shows an example of using WHERE with a comparison operator, I don't understand how it  is being used below.  I understand the notation "columnName"...

Why the not?:

if not exists ( select 0 from sys.sql_logins where name = @szlogin)

Also I think you might need:

EXECUTE AS LOGIN='sa';

before the line: if...

.

• This reply was modified 5 years, 2 months ago by Jonathan AC Roberts.

You really need to look at the code. It could be, for example, that large temporary tables are being created for the processing. You could get around this by using...

Okay I will try creating these indexes and see if it benefits. Since, the query isn't completing in its entirety I have no clue of the optimizer suggesting an...

Thank you so much for all the help...

• This reply was modified 5 years, 2 months ago by Jonathan AC Roberts. Reason: added PS:

You have an OUTER APPLY (aliased c2) in the query but in the WHERE clause of the main query you have

AND D.LiveTable = c2.TableName

So why is it an OUTER APPLY...

Yep, I agree with you Jonathan AC Roberts, but what if the number of columns is dynamic?  With version 1 it has 3, version 2 has 2, version 3...

Not sure that an index will help anything since there is no WHERE clause or ORDER BY.  No matter what index you put on there, it is going to...