Is the table clustered on ID?

Have you got any other indexes on the table?

Have you looked at the execution plan?

As we don't know what the processing is no one will be able to say whether or not a DYNAMIC READ_ONLY CURSOR is the best way to process your data. Can...

Yes, the ROW_NUMBER() function is unused so does nothing.

What error are you are getting?

SELECT BusinessEntityID, 
       FirstName, 
       MiddleName, 
       LastName, 
...

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 5 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 '
 ...