Unable to index materialized view

  • 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

  • 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/

  • 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'

  • 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?

  • 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/

  • 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

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

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