Here is a script for getting the definition of temp tables:

https://www.sqlservercentral.com/scripts/get-temp-table-definition

https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver16

This might be more efficient:

;WITH CTE AS
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY (SELECT...

SELECT Color,
       SampleType,
       SUM(SampleValue) SampleValue
  FROM #SampleData
 GROUP BY Color, 
   ...

From ChatGPT 4:

The provided deadlock XML contains a lot of information, but let's focus on the core elements to diagnose and address the deadlock issue.

First, let's break down the XML...

There was a big improvement to the performance of scalar valued functions in SQL Server 2019.

But you need to make sure the improvement is switched on to get the benefit,...

• This reply was modified 1 year, 6 months ago by Jonathan AC Roberts.

If you need the count to be NULL when there aren't 5 preceding days then this query will do the trick:

declare @DaysToLookBack int = 5

select a.company, 
...

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select count(*) Count
            ...

There aren't 3 values less than 88 within 5 days of (including the reference date) 2024-02-28

2024-02-28 minus 4 days is 2024-02-24.

So there are only  2 rows less than that (11,...

• This reply was modified 1 year, 6 months ago by Jonathan AC Roberts.

The gaps in dates shouldn't be an issue.

The data you provided isn't the data you put in your amendment of the query.

The results from the test data you provided look...

Has TRADE_DATE got a time part on it? If so is it not always midnight?

Can you supply some test data?

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count
            ...

I would just leave it all in one table.

How many rows has this table got?

• This reply was modified 1 year, 6 months ago by Jonathan AC Roberts.

Could you change the method of deleting rows so that you drop the clustered index on the view, then delete the thousands of rows, then reinstate the clustered index?

Also check...

You could see how the performance is if you drop the clustered index, after dropping the clustered index the view will just behave like a normal view.

Check what indexes are...