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:
| @@ERROR | FORMATMESSAGE | NEWID |
| @@IDENTITY | GETANSINULL | PATINDEX |
| @@ROWCOUNT | GETDATE | PERMISSIONS |
| @@TRANCOUNT | GetUTCDate | SESSION_USER |
| APP_NAME | HOST_ID | STATS_DATE |
| CHARINDEX | HOST_NAME | SYSTEM_USER |
| CURRENT_TIMESTAMP | IDENT_INCR | TEXTPTR |
| CURRENT_USER | IDENT_SEED | TEXTVALID |
| DATENAME | IDENTITY | USER_NAME |
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://blog.sqlauthority.com)
8 Comments. Leave new
Good one, never came across this one but would not have known had I not read this.
Pinal, Question though –
What is the performance gain and the impact of creating a index on a view? how does it work? For example.
If my view is really gathering a huge (10 millions+) chunk of data, would creating a index on the fields that are most commonly queried/selected against benefit the performance? what is the impact on the size growth? Thanks.
@Vikram,
Indexed views can increase query performance in the following ways:
* Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
* Tables can be prejoined and the resulting data set stored.
* Combinations of joins or aggregations can be stored.
you can find more details from below URL:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc917715(v=technet.10)
Moreover, I have written bit same article at
which might give you an idea which kind of error comes between while dealing with Views.
Hello Vikram,
Ritesh has pointed out all pros of indexing on view. But there are some cons also such as extra storage space requirement and slow performance of insert-update-delete on base tables.
When an index is created on a view then data of all columns (not only of indexed columns) of view is stored saperately on disk. That could be an issue if view returnd millions of rows.
All updates and changes in all base tables are also reflected in indexed view simultaneously. So DML transactions take longer time to complete.
So decide about indexed view only after considering all pros and cons.
Regards,
Pinal Dave
Hi Pinal,
Nice article.
I want to eagar for If my base table has millions+ records.So, is it fine to create cluster index or noncluster inex on that view?
Becuse if i create a cluster index then it will take more space and hardly for maintenance.
What actully need to do?
Thnaks,
Paresh
Hi Paresh,
The first index on view is always clustered index. So there is no way to create a non-clustered index on view without creating a clustered index.
Regards,
Pinal Dave
Hi Pinal,
Why using non-deterministic functions is not allowed in Indexed View?
The Reason is that ALL Data (not even the indexed columns) is stored on disk.
Which value should SQL Server have persisted if you have a non-deterministic function in the view.
Assume you use GetDate() in your view-definition.
Assume SQL Server stores the time from the point of index creation.
Or should he store the time from the point of inserting the new row. But what about the existing rows?
Or should SQL Server store the time of the point when a user queries the view? But what about 2.000 concurrent users??? questions over questions with non-determistic functions :-)
Hi Pinal,
If I have an indexed view and the data got change in the tables included does it affect the view. I think it affects. what is the exact use of with noexpand