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.

SQL SERVER - Fix Error 1949, Level 16: Cannot create index on view. The function  yields nondeterministic results error-500x500 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:

@@ERRORFORMATMESSAGENEWID
@@IDENTITYGETANSINULLPATINDEX
@@ROWCOUNTGETDATEPERMISSIONS
@@TRANCOUNTGetUTCDateSESSION_USER
APP_NAMEHOST_IDSTATS_DATE
CHARINDEXHOST_NAMESYSTEM_USER
CURRENT_TIMESTAMPIDENT_INCRTEXTPTR
CURRENT_USERIDENT_SEEDTEXTVALID
DATENAMEIDENTITYUSER_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)

, , ,
Previous Post
SQL SERVER – Get Date of All Weekdays or Weekends of the Year
Next Post
SQLAuthority News – 1200th Post – An Important Milestone

Related Posts

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.

    Reply
  • @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.

    Reply
    • 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

      Reply
  • 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

    Reply
  • 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

    Reply
  • Santosh Gadila
    June 10, 2010 11:50 pm

    Hi Pinal,
    Why using non-deterministic functions is not allowed in Indexed View?

    Reply
    • Ralf Dietrich
      July 21, 2010 11:52 pm

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

      Reply
  • 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

    Reply

Leave a Reply

Menu