none
Create new Index

    Question

  • SQL Server 2008 R2

    I'm just not seeing the problem with this syntax.  The error I am getting is:

    Create Failed for Index 'IDX_01'.

    Cannot create index on view 'sscerp.dbo.vJCROHDate-TEST' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value. (Microsoft SQL Server, Error: 10139)

    Here is the view I am trying to create an index on:

    SELECT
    	JobID,
    	CASE Posted WHEN 0
    		THEN CONVERT(DATE, DATEADD(MONTH, - 1, GetDate()), 101)
    	ELSE
    		CASE WHEN DATEDIFF(DAY, CONVERT(DATE,tblJob.PostDate,101), GetDate()) / 30.0 <= 1
    			THEN CONVERT(DATE, DATEADD(MONTH, - 1, GetDate()), 101)
    		ELSE
    			CONVERT(DATE,ISNULL(PostDate, ''),101)
    		END
    	END AS OHDate,
    	ForProfitJob
    FROM
    	dbo.tblJob

    I know what the error message is saying (or at least I thought I knew what it was telling me), but I can't see where in the SQL it's referring to.  I've put Convert() functions all over the place to see if it takes care of it, but none have.  I've re-written this SQL a dozen times trying different things but with no luck.

    Help me SQL developers, you're my only hope. :-)

    Here is the script SQL generates:

    USE [sscerp]
    GO
    SET ARITHABORT ON
    GO
    SET CONCAT_NULL_YIELDS_NULL ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_WARNINGS ON
    GO
    SET NUMERIC_ROUNDABORT OFF
    GO
    CREATE UNIQUE CLUSTERED INDEX [IDX_01] ON [dbo].[vJCROHDate-TEST] 
    (
    	[JobID] ASC
    )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

    Thanks for any help you can offer!

    • Modifié AWSIC mardi 28 février 2012 20:52
    mardi 28 février 2012 20:51

Réponses

  • Create Failed for Index 'IDX_01'.

    Cannot create index on view 'sscerp.dbo.vJCROHDate-TEST' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value. (Microsoft SQL Server, Error: 10139)

    Hi AWSIC,

    Could you please show us the table DDL of tblJob? What’s the data type of PostDate?

    I think the following line causes the error:

    CONVERT(DATE,ISNULL(PostDate, ''),101)

    PostDate should be a datetime data type like datetime or date, right? ‘’ is a string, so there is an implicit conversion from string to datetime.

    However, after changing the line to:

    CONVERT(DATE,ISNULL(PostDate, CONVERT(DATETIME,'19000101')))

    The previous error is gone and another one comes:

    Cannot create index on view 'tempdb.dbo.vJCROHDate-TEST'. The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.

    As you may know, GETDATE() is a nondeterministic function. Views that reference it in a column cannot be indexed. There is no deterministic equvilance of GETDATE(). GETDATE() is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

    For more information, please see: http://msdn.microsoft.com/en-us/library/ms191432.aspx

    So in your case, you cannot change the view into an indexed view to improve performance.

    Also, a datetime value doesn’t have a style. There is no need to add 101 in the CONVERT function. For more information, I would suggest you have a look at: http://www.karaszi.com/SQLServer/info_datetime.asp 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.

    • Modifié KJian_ jeudi 1 mars 2012 04:03
    • Proposé comme réponse Papy NormandModerator jeudi 1 mars 2012 16:30
    • Marqué comme réponse AWSIC jeudi 1 mars 2012 16:45
    jeudi 1 mars 2012 04:01

Toutes les réponses

  • Create Failed for Index 'IDX_01'.

    Cannot create index on view 'sscerp.dbo.vJCROHDate-TEST' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value. (Microsoft SQL Server, Error: 10139)

    Hi AWSIC,

    Could you please show us the table DDL of tblJob? What’s the data type of PostDate?

    I think the following line causes the error:

    CONVERT(DATE,ISNULL(PostDate, ''),101)

    PostDate should be a datetime data type like datetime or date, right? ‘’ is a string, so there is an implicit conversion from string to datetime.

    However, after changing the line to:

    CONVERT(DATE,ISNULL(PostDate, CONVERT(DATETIME,'19000101')))

    The previous error is gone and another one comes:

    Cannot create index on view 'tempdb.dbo.vJCROHDate-TEST'. The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.

    As you may know, GETDATE() is a nondeterministic function. Views that reference it in a column cannot be indexed. There is no deterministic equvilance of GETDATE(). GETDATE() is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

    For more information, please see: http://msdn.microsoft.com/en-us/library/ms191432.aspx

    So in your case, you cannot change the view into an indexed view to improve performance.

    Also, a datetime value doesn’t have a style. There is no need to add 101 in the CONVERT function. For more information, I would suggest you have a look at: http://www.karaszi.com/SQLServer/info_datetime.asp 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.

    • Modifié KJian_ jeudi 1 mars 2012 04:03
    • Proposé comme réponse Papy NormandModerator jeudi 1 mars 2012 16:30
    • Marqué comme réponse AWSIC jeudi 1 mars 2012 16:45
    jeudi 1 mars 2012 04:01
  • Thanks, Jian, for your help.  I've come to the same conclusion after reading a bunch of MSDN articles.  This view can't be indexed as I was hoping.
    jeudi 1 mars 2012 16:46