Post reply

Cannot create index on view 'ViewTicket' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.

  • March 11, 2014 at 3:29 pm

    #396404

    Hi Team,

    Please advise....

    Step 1 Created View

    CREATE VIEW [dbo].[ViewTicket]

    WITH SCHEMABINDING

    AS

    SELECT top 10 Sno, TicketNo, 'Ticket' + CONVERT(VARCHAR(10), id) + SPACE(2) + '| Status:' + ISNULL(Status, '') + SPACE(2)

    + '| Priority:' + ISNULL(priority, '') + SPACE(2) + '| Title:' + ISNULL(title, '') + SPACE(2)

    + '| Category:' + ISNULL(category, '') + SPACE(2) + '| SubCategory:' + ISNULL(subcategory, '') + SPACE(2)

    + '| Classification:' + ISNULL(classification, '') + SPACE(2) +

    + '| Queue:' + ISNULL(queue, '') + SPACE(2) + '| LogTime:' + CONVERT(VARCHAR, logtime, 0) + SPACE(2)

    + '| Accept Time:' + CONVERT(VARCHAR, ISNULL(accepttime, ''), 0) FROM dbo.TableTicket

    Step 2

    See below error when i try to implement:

    CREATE unique clustered INDEX td_sno on dbo.ViewTicket (Sno)

    Im using CONVERT(VARCHAR, ISNULL(accepttime, ''), 0) in query

    Step 3 Below Error

    Cannot create index on view 'dbo.ViewTicket' because the view uses an

    implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.

    Thank you

  • March 11, 2014 at 4:02 pm

    #1696169

    ISNULL(accepttime, '')

    Here you implicitly convert the empty string to a datetime field (at least that's what I assume accepttime is. It's just guessing without the table DDL.)

    According to MSDN (emphasis is mine):

    The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 11, 2014 at 4:25 pm

    #1696178

    The next problems that I see you are going to encounter after you fix the CONVERT(VARCHAR, ISNULL(accepttime, ''), 0) toISNULL(CONVERT(VARCHAR, accepttime, 0), '') is that you will not be allowed the TOP 10 and the format style 0 is non deterministic. You could try a style of 101 on the dates rather than 0.

    Getting the TOP 10 while still indexing will be trickier. How are you determining which 10 are returned?

    Here's a quick test

    CREATE TABLE TESTTHIS (

    ID INT IDENTITY PRIMARY KEY,

    ACCEPTTIME DATETIME DEFAULT GETDATE()

    );

    GO

    INSERT INTO TESTTHIS VALUES(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT);

    GO

    CREATE VIEW TESTTHISVIEW1

    WITH SCHEMABINDING AS

    SELECT TOP 10 ID, 'Date to string ' + CONVERT(VARCHAR, ISNULL(accepttime, ''), 0) test

    FROM dbo.TESTTHIS;

    GO

    CREATE VIEW TESTTHISVIEW2

    WITH SCHEMABINDING AS

    SELECT TOP 10 ID, 'Date to string ' + ISNULL(CONVERT(VARCHAR, accepttime, 0), '') test

    FROM dbo.TESTTHIS;

    GO

    CREATE VIEW TESTTHISVIEW3

    WITH SCHEMABINDING AS

    SELECT ID, 'Date to string ' + ISNULL(CONVERT(VARCHAR, accepttime, 0), '') test

    FROM dbo.TESTTHIS

    GO

    CREATE VIEW TESTTHISVIEW4

    WITH SCHEMABINDING AS

    SELECT ID, 'Date to string ' + ISNULL(CONVERT(VARCHAR, accepttime, 101), '') test

    FROM dbo.TESTTHIS

    GO

    CREATE UNIQUE CLUSTERED INDEX tv1_udx ON TESTTHISVIEW1(ID)

    GO

    CREATE UNIQUE CLUSTERED INDEX tv2_udx ON TESTTHISVIEW2(ID)

    GO

    CREATE UNIQUE CLUSTERED INDEX tv3_udx ON TESTTHISVIEW3(ID)

    GO

    CREATE UNIQUE CLUSTERED INDEX tv4_udx ON TESTTHISVIEW4(ID)

    GO

    DROP VIEW TESTTHISVIEW1

    DROP VIEW TESTTHISVIEW2

    DROP VIEW TESTTHISVIEW3

    DROP VIEW TESTTHISVIEW4

    DROP TABLE TESTTHIS

    GO

Viewing 3 posts - 1 through 2 (of 2 total)

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