Try TOP(1) in a CROSS APPLY.

e.g.:

SELECT DISTINCT 
       m.ID, 
       m.Type, 
   ...

Hi I Did not mentioned earlier, This is my Live OLTP Database/Tables. So On top of the adhoc Queries  there are some another maintenance jobs as well as some...

I hadn't actually looked...

You haven't provided information on what indexes are on the tables.

Ideally FinalTAble will have a clustered index on Id and no other indexes.

I would then try creating clustered indexes on...

I have a Use case like.

We tried disabling all the index but still it's taking too much time. Even we have 5-8 hours maintain-ace window. We don't have partitioned...

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 6 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...