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 (http://blog.SQLAuthority.com)