I just updated all the stats for this able and still the same behavior.

code inside the function

CREATE FUNCTION [dbo].[fnDate2int] ( @Date DATE )

RETURNS INT

AS

BEGIN

DECLARE @IntDate INT = CAST(CONVERT(VARCHAR(8),@Date,112) AS INT)

RETURN @IntDate

IDIDAT is declared as INT in the table

DECLARE @StartDate DATE = '9/5/2023'

DECLARE @EndDate DATE = '9/5/2023'

DECLARE

@IntStartDate INT = dbo.fnDate2int(@StartDate)

,@IntEndDate INT = dbo.fnDate2int(@EndDate)

query 1

SELECT

FROM  table il

WHERE

il.IDIDAT between @IntStartDate AND @IntStartDate

query 2

SELECT

FROM ...

Scattered throughout.

Thanks. Question is do you really have room for improvement, when you your table is large as 176M rows and your view on top of that table returns everything without...

so code is just a view on top of this table, that does select * from this table without any filter.

any findings/guidance?

Memory shouldn't be an issue. 476 GB for SQL instance.

try this one as this one is from prod

is this script safe to run on production?

yes

Here you go.

Thank you. That works great. Now, I have another Challange. I’m calling API to get the json file (string) and loading json string into SQL table in a column nvarchar(max)....

Will delete on colunstore index takes longer than Update? Or both will take longer?

Soultuion DesNorton gave worked like a champ. Thank you so much!!