SQL SERVER – FIX : Error : Msg 1949, Level 16: 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.

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.

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 function in your view, it will not allow you to create index 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 index, there is no way to create the index without removing the non-deterministic function.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – FIX : Error : Msg 1949, Level 16: 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.

  1. 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.

  2. @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:

    http://technet.microsoft.com/en-us/library/cc917715.aspx

    Moreover, I have written bit same article at

    http://www.sqlhub.com/2009/07/cannot-create-index-on-view-msg-1940.html

    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

  3. 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

    • 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 :-)

  4. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s