Ddi you try this one

;with mycte

as

(

select *,ROW_NUMBER() over(partition by Account,Organization,Amount order by id asc) as rn

from @SAMPLE

)

select ID,Account,Organization,SUBGROUP,Amount

from mycte

where rn =1

order by ID asc

Thanks for this. I can not open the plan on my machine.I will try from my work machine tomorrow.

Either I have not understood what the original OP wanted but why do we need a like statement when the sql server provides us with the method which can convert...

Your approch is just fine But I will not use a new varaible but rather will use the select statement in try

drop table T1

go

create table T1

(

u_id UNIQUEIDENTIFIER NOT NULL...

Which subgroup you want to keep. Based on your output I have decided that you want to keep the subgroup which has minimum ID value.Below sql will give you the...

I added two indexes to the CollectorTransitionHistory table, based on the predicates. The first statement is against the Endpoints table, and the EndpointID is the PK (clustered), so it's probably...

There are no record count and there are no forward row count data in this snap shot. This is indeed a heap and fragmentation is very heavy. Can you post...

Looks like the indexes are issues here.

The delete statement is using clustered index but all index keys are not part of it.Thus all the rows which has dailystartdate =@startdate...

Cubes solution is just like your unique ,doubles,tripples but it can have more than tripple like say 30 column combinations and thus in the end will need less tables for...

i just tried like this

;WITH MemberAndParent

AS (

SELECT m.MemberID, m.Name AS MemberName, r.ParentId

FROM tblMembers m

LEFT JOIN

tblMembersRelation r

ON r.MemberID = m.MemberID

),

Hierarchy

AS (

SELECT ParentId, MemberID, 0 AS LevelNum, MemberName, CAST(MemberName AS VARCHAR(1000))...

There are couple of things.

Can you post the output of the following?

select * from sys.dm_db_index_physical_stats(DB_ID(),object_id('<tablename>'),null,null,null)

To me it looks like that you do not have any clustered index and you have...

You can take advantage of cube subclause . This generates all the permutation and combinations...

See the below example...

If the dynamic code is becoming bigger.. You can have two part..first till...

Which columns has clustered and non clustered indexes on the CollectorTransitionHistory

table? What about the unique/PK?

The second update seems causing the issue also at the same time it looks like it...

First of all you need to increase the batchsize to aorund 5000.Second Can you post the procedure code which is deleting the data i.e. trimTable?

Also, it is possible that...

Apart from the function you have so many conditions ,are there proper indexes on some(or all) of these columns?

Try to comment the function call (AND (AOC_RPT.UDFSTATGETCASESTATUS(CS.CASE_ID,'05/01/2006') IN (1000001,1000002,1000006))

) condition and...