SQL SERVER – Fix Error 1949, Level 16: Cannot create index on view. The function yields nondeterministic results

Recently, during my training session in Hyderabad, one of the attendees wanted to know the reason of the following error that he encountered every time he tried to create a view. He informed me that he is also creating the index using WITH SCHEMABINDING option. Let us see we can fix error 1949.

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

I could easily find out the reason for this error. He was using non-deterministic function in his view, which was leading to this error. Once the non-deterministic function was removed, the error disappeared.

The definition of the non-deterministic function is that it may return different results each time it is called with a specific set of input values. MSDN lists many functions as non-deterministic:

Now if you are using any of the above functions in your view, it will not allow you to create indexes on the view. You will have to remove the function before creating the view. Following is a quick example for the same:

USE TempDB
GO
-- Create view with non deterministic function GETDATE
CREATE VIEW TempView
WITH SCHEMABINDING
AS
SELECT GETDATE() AS CurrentTime, 1 FirstCol
GO
-- Check the data from the view
SELECT *
FROM TempView
GO
-- Attempt to create the view
CREATE UNIQUE CLUSTERED INDEX [IX_ClusteredIndex_TempView] ON [dbo].[TempView]
(
FirstCol ASC
) ON [PRIMARY]
GO
/* Above query will throw following error
Msg 1949, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.TempView'. 
The function 'getdate' yields nondeterministic results. 
Use a deterministic system function, 
or modify the user-defined function to return deterministic results.
*/
-- Alter View and remove non deterministic function GETDATE
ALTER VIEW TempView
WITH SCHEMABINDING
AS
SELECT 1 FirstCol
GO
-- Creating following view will work successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ClusteredIndex_TempView] ON [dbo].[TempView]
(
FirstCol ASC
) ON [PRIMARY]
GO

This is not the perfect solution as one need to remove the column from view; but if you want to create an index, there is no way to create the index without removing the non-deterministic function.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)