Since you say the updates are not "extremely frequent", it's mostly an "Insert" index by TenantID.  It's a Type 98 Index and since there aren't a large number of...

Hi Jeff

Thank you for the detailed explanation and advice on Fill Factor settings. It is incredibly helpful.

In our case, the clustered index (TENANTID, ID) will likely see updates to rows...

post to get new page

Jeff,

Have you got any recommendations for the fill factor when I create the new clustered primary keys on (TENANTID, ID)?

Where ID is an identity column on the table.

Does partitioning by TENANTID improve query performance when most queries are filtered by tenant? For instance, will partitioning reduce the amount of data SQL Server scans, or is...

Nice.

Also, you don't have to change the FKs in other tables if you need one for just ID.  Instead, just add a unqiue nonclus index on ( ID );...

I've created a stored procedure that takes a table name as a parameter and checks the clustered index column order. If the order is ID, TENANTID, the procedure drops the...

Using YEAR and MONTH Functions:

INSERT INTO #ToDelete (Id)
SELECT Id
 ...

Depending on the width of the rows and the number of indexes on the table, deleting a day's worth of rows should typically take no more than...

How many rows are added to the table each day? And how many rows are currently in the table?

I’ve revised my original response to correct the delete statement - perhaps it could serve as an example.

I’ve also added WITH (NOLOCK) on the...

This is throwing an error:

-- Populate the batch table with the next set of IDs to delete DELETE TOP (@BatchSize) OUTPUT DELETED.Id INTO #BatchToDelete FROM #ToDelete;

Msg 102, Level 15,...

Certainly! Simply include the criteria in the WHERE clause of...

When performing deletions on large tables, it is essential to delete rows using the clustered index (in this case, PK(Id)) and process the deletions in small batches. This approach ensures:

Efficiency:...

• This reply was modified 1 week ago by  Jonathan AC Roberts. Reason: Correct delete from batch, and added NOLOCK to ID select

Not quite the same result but has all the info:

SELECT *
  FROM #STOCKS1 AS T1 
  FULL JOIN #STOCKS2 AS T2 
   ...