select t.col1, t.col2, sum(t.col3) col3, sum(t.col4) col4, col5
from (
select 7213 col1, 992 col2, 30 col3, 0 col4, cast(getdate() as date) col5
union all
select 7213 col1, 992 col2, 0...

I can't see the point in the clustered index :

CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL 
    ON dbo.PERSON_HISTORY ( OccurredDate, My ) 
...

I can't see the point in the clustered index :

CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL 
    ON dbo.PERSON_HISTORY ( OccurredDate, My ) 
  ...

Hmm, I remember a WHERE condition on the OccurredDate; maybe I'm mixing this query up with another one.

At any rate, one last time, as I stated earlier:

the history table...

First, the history table should be clustered leading on OccurredDate.  That's generally true for log tables.  [Forget the stupid myth that "by default, every table should be...

First, the history table should be clustered leading on OccurredDate.  That's generally true for log tables.  [Forget the stupid myth that "by default, every table should be clustered on...

any suggestions for dropping and re-creating on that large of a Table?

Not sure how much of a time window I have to do the drop\create...  The Box it's on...

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

I think Scott means to drop the clustered primary key on table Quality (CONSTRAINT [PK_Quality] PRIMARY KEY CLUSTERED ([Quality_ID]...

possible way - but without a real execution plan of this with one of the "bad performance" queries we don't know if it is the best option

            
June 16, 2021 at 5:27 pm
            
#3897077

            
            
                                                
        

Slightly more concise way to test if a column is present:

IF COL_LENGTH(N'[dbo].[tbl_Reference]', N'ReferenceName') IS NOT NULL BEGIN

    PRINT 'UPDATING [dbo].[tbl_Reference] SET ClientReferenceNumber '
 ...

thanks for the suggestions, I will try these and update the results.

Can we do updates in batches? If I have total record count of 10 million than what should...

It has a clustered index but clustered index column is not included in where clause nor in the list of columns getting updated.

Does dropping a clustered index before update...

What columns are on the clustered index?

Do you have any other indexes on the table?

You know you could write the two update statements to be just one update statement?

I tried that and it ran (unlike when I put the option in other parts of the code) but I still got the same error. Any ideas as to...

It would help if you could show us the queries with information on the number of rows in each table.