Cannot create index on view "MyVideo..RawLogView". The view contains a convert that is imprecise or non-deterministic.

So the customer was asking, how to make this deterministic? Would love to learn if you have any other option.

Possible Solution

Here are couple of possible solutions to this problem. The root reason is that the cast string to datetimeoffset(7) is not deterministic, we need use convert with style 126. Let me show how we can rewrite this query to achieve the same. Note, in below example Option 2, we use SWITCHOFFSET built-in to convert a datetime UTC values to a datetimeoffset, and then SWITCH the timezone to +08:00. Let us see the code now:

USEtempdb

GO

— If it exists Drop it.

DROPTABLErawlog

GO

CREATETABLErawlog(idINTIDENTITY(1,1)PRIMARYKEY,

createdDATETIME2(7))

GO

INSERTINTOrawlogVALUES(SYSDATETIME())

GO

We have created our dummy table for use with a value. Let us check if the string conversion returns the same result.

These are just two of the methods to achieve the goal. Do let me know if you use Indexed views in your code today? What are the scenarios you use them? Have you encountered any of these deterministic restrictions in your coding anytime? Do let me know, would love to learn from you too.

This entry was posted
on Tuesday, May 28th, 2013 at 09:57 and is filed under Technology.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.