SQL Server 2019. Cannot create index in view because column is "Imprecise,computed and not pers-CodePudding

I want to create an index on a SQL Server view. The column I want to index is defined like this:

CASE 
    WHEN (CAST([LOCAL_DATE] AS float) - FLOOR(CAST([LOCAL_DATE] AS float))) 
         BETWEEN CAST([START_DATE] AS float) - floor(CAST([START_DATE] AS float)))  
             AND (CAST([END_DATE] AS float) - FLOOR(CAST([END_DATE] AS float)))  
       THEN 1 
       ELSE 0 
END AS InTime

LOCAL_DATE is a datetime column in my source table. The InTime column in my view is just a flag, so normally it would be a bit, but SQL Server creates it as an Int column.

The thing is when I try to create a index in my view, it throws an error stating that the column is is "imprecise, computed and not persisted".

CREATE INDEX Index_Name ON [dbo].[MyView](InTime)

Is there any workaround for this? I use float conversion to compare datetime, as to my understanding it's the fastest way.

For example DateTime is 1 Jan 2022 12:00

I need to know if this event occurred between 8:00 and 16:00 or not, and show my InTime column. So I use

(CAST([LOCAL_DATE] AS float) - FLOOR(CAST([LOCAL_DATE] AS float)))

to get just the time.

The goal is to speed up any query that request events that occurred InTime

---- EDIT/UPDATE -----

After your suggestions, I'm trying with CAST function. But now I get a different error, saying that the column 'InTime' is "non-deterministic"

This is my actual view definition:

ALTER VIEW [dbo].[MyView]
WITH SCHEMABINDING 
AS
SELECT        TOP (100) A.LOCAL_DATE,
                             CASE WHEN CAST(LOCAL_DATE AS TIME) BETWEEN (CASE DATEPART(WEEKDAY, A.LOCAL_DATE) WHEN 1 THEN
                             (SELECT        (CAST(StartDate AS TIME))
                               FROM            [dbo].[SCHEDULLE_TABLE] E
                               WHERE        E.IdCen = A.IdCen) ELSE
                             (SELECT        (CAST(StartDateB AS TIME))
                               FROM            [dbo].[SCHEDULLE_TABLE] E
                               WHERE        E.IdCen = A.IdCen) END) AND (CASE DATEPART(WEEKDAY, A.LOCAL_DATE) WHEN 1 THEN
                             (SELECT        (CAST(EndDate AS TIME))
                               FROM            [dbo].[SCHEDULLE_TABLE] E
                               WHERE        E.IdCen = A.IdCen)  ELSE
                             (SELECT        (CAST(EndDateB AS TIME))
                               FROM            [dbo].[SCHEDULLE_TABLE] E
                               WHERE        E.IdCen = A.IdCen) END) THEN 1 ELSE 0 END AS InTime
FROM            dbo.EventTable AS A 
GO

CodePudding user response：

Just cast to TIME instead of FLOAT, eg

drop table if exists t
create table t
(
    id int identity primary key,
    local_date datetime, 
    start_date datetime, 
    end_date datetime
)
go
create or alter view vt 
with schemabinding
as
    select t.id, t.local_date, t.start_date, t.end_date,
            CASE WHEN CAST([LOCAL_DATE] AS time)
                BETWEEN CAST([START_DATE] AS time) 
                AND CAST([END_DATE] AS time) 
                THEN 1  ELSE 0 END as InTime
    from dbo.t

go
create unique clustered index ix_vt on vt(InTime,id)