Unable to index materialized view

Question

Post reply

Unable to index materialized view

TryingToLearn

SSCrazy

Points: 2646
More actions
December 6, 2017 at 12:11 pm

#326740

Tying to create an index on this view: and receive the following error:
Column cannot be used in an index or statistics or as a partition key because it is non-deterministic. (Microsoft SQL Server, Error: 2729)
But its not.
Select COLUMNPROPERTY (OBJECT_ID( 'test' ), 'dt', 'IsDeterministic');
(No column name)
0
CREATE VIEW dbo.test
WITH SCHEMABINDING
AS
SELECT
  e.EMPID,
     calldate + CAST(cast(calltime as datetime) as float) dt,
  c.DURATION,
  c.PHONENUM,
  c.IO -- 'I = inbound, O = outbound
FROM [dbo].[CALLS] c
  JOIN [dbo].[EMP] e on c.empnum = e.empnum

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27060 More actions · Answer 1

From Books Online:

CAST - Deterministic unless used with datetime, smalldatetime, or sql_variant

Try using CONVERT with the style specified.

And, why are you converting to a float first?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

TryingToLearn SSCrazy Points: 2646 More actions · Answer 2

So what i trying to do is create the materialized view on this table: Combining the calldate + calltime field

CREATE TABLE [dbo].[CALLS](
    [calldate] [datetime] NOT NULL,
    [calltime] [char](5) NOT NULL,
    [duration] [char](8) NOT NULL,
    [phonenum] [char](11) NULL,
    [empnum] [numeric](18, 0) NULL
) ON [PRIMARY]

This fails with the same error msg
CREATE VIEW [dbo].[test]
WITH SCHEMABINDING
AS
SELECT
  e.EMPID,
     CONVERT(DATETIME, CONVERT(CHAR(8), c.Calldate, 112)
     + ' ' + CONVERT(CHAR(8), c.calltime, 108)) as CALLDATETIME,
     c.DURATION,
  c.PHONENUM,
  c.IO -- 'I = inbound, O = outbound
FROM [dbo].[CALLS] c
  JOIN [dbo].[EMP] e ON c.empnum = e.empnum
WHERE c.CALLDATE > '2016-01-01'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

TryingToLearn - Wednesday, December 6, 2017 12:48 PM
So what i trying to do is create the materialized view on this table: Combining the calldate + calltime field
CREATE TABLE [dbo].[CALLS](
    [calldate] [datetime] NOT NULL,
    [calltime] [char](5) NOT NULL,
    [duration] [char](8) NOT NULL,
    [phonenum] [char](11) NULL,
    [empnum] [numeric](18, 0) NULL
) ON [PRIMARY]
This fails with the same error msg
CREATE VIEW [dbo].[test]
WITH SCHEMABINDING
AS
SELECT
  e.EMPID,
     CONVERT(DATETIME, CONVERT(CHAR(8), c.Calldate, 112)
     + ' ' + CONVERT(CHAR(8), c.calltime, 108)) as CALLDATETIME,
     c.DURATION,
  c.PHONENUM,
  c.IO -- 'I = inbound, O = outbound
FROM [dbo].[CALLS] c
  JOIN [dbo].[EMP] e ON c.empnum = e.empnum
WHERE c.CALLDATE > '2016-01-01'

The column calltime is defined as VARCHAR(5), how is the data stored in the table?

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

Did you actually read Books Online?

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions

This may work.
DATETIMEFROMPARTS (DATEPART(year, c.Calldate), DATEPART(month, c.Calldate), DATEPART(day, c.Calldate), DATEPART(hour, c.calltime), DATEPART(minute, c.calltime), DATEPART(second, c.calltime), 0)

What is the data look like for calltime??

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

TryingToLearn SSCrazy Points: 2646 More actions · Answer 5

This is what the data looks like:

calldate calltime    duration    phonenum    empnum
2013-12-31 00:00:00.000    10:59    00:01:19    2679682968     23
2013-12-31 00:00:00.000    8:42     00:01:06    7314354142     83
2013-12-31 00:00:00.000    10:33    00:00:54    6109339749     83
2013-12-31 00:00:00.000    11:50    00:00:58    2673129022     83

Yes i read the article, but here appears to something strange, as I've tried many options and none work. I also realize this data is bad(char), i'm trying to fix it, but there are downstream reports that must be changed as well.
Thanks