If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. The function ‘sysdatetime’ yields nondeterministic results…” and so on, in SQL Server, it looks like you’re trying to create an index on a view that returns nondeterministic results.

Indexes can only be created on views that return deterministic results.

To fix this issue, remove the nondeterministic part from the view or change it so that it’s deterministic. Alternatively, review whether the view should even be indexed.

Example of Error

Suppose we create the following table and schema-bound view:

-- Create a table
CREATE TABLE dbo.t1
  (
    c1 int,
    c2 AS SYSDATETIME()
  );

-- Create a view that references that table
CREATE VIEW v1 WITH SCHEMABINDING AS 
    SELECT c1, c2 FROM dbo.t1;

And now we try to create an index on the view:

CREATE UNIQUE CLUSTERED INDEX IDX_v1 ON v1 (c1);

Output:

Msg 1949, Level 16, State 1, Line 1
Cannot create index on view 'demo.dbo.v1'. The function 'sysdatetime' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.

We got this error because the table uses SYSDATETIME(), which is a nondeterministic function. This function returns the date and time of the computer on which the instance of SQL Server is running, which changes every time the function is run (which makes it nondeterministic).

Microsoft also mentions this on their website:

SYSDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

And here’s what Microsoft says about the deterministic view requirement:

The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, and the WHERE and GROUP BY clauses, are deterministic. Deterministic expressions always return the same result whenever they’re evaluated with a specific set of input values.

Solution

Given we can only index views that yield deterministic results, we first need to review whether or not we should index the view. If it must be nondeterministic, then we can’t index it.

But if we decide that we can remove the nondeterministic part from the view (or base table), then we can go ahead with that.

Example:

DROP VIEW dbo.v1;

ALTER TABLE dbo.t1
DROP COLUMN c2;
 
ALTER TABLE dbo.t1
ADD c2 datetime2(7);

Here I changed SYSDATETIME() to a regular datetime2 column. I had to drop the column and create it again, because that’s what we have to do with computed columns. I could have dropped it and not replaced it with anything, but in that case I would need to modify the definition of the view.

Anyway, we can now go ahead and create the view and index again:

-- Create a view
CREATE VIEW v1 WITH SCHEMABINDING AS 
    SELECT c1, c2 FROM dbo.t1;

-- Create the index
CREATE UNIQUE CLUSTERED INDEX IDX_v1 ON v1 (c1);

Output:

Commands completed successfully.

This time the table (and view) were dropped as expected.