Have you ever encountered an error while trying to create a view in SQL Server? One of the common errors that you might come across is error 1949, which states “Cannot create index on view. The function yields nondeterministic results.”

In simple terms, a non-deterministic function is one that may return different results each time it is called with the same set of input values. This can cause issues when trying to create an index on a view that uses such a function.

SQL Server provides a list of functions that are considered non-deterministic, including functions like GETDATE, NEWID, and CHARINDEX, among others. If you use any of these functions in your view, you will not be able to create an index on that view.

Let’s take a look at an example to understand this better:

USE TempDB
GO

-- Create view with non-deterministic function GETDATE
CREATE VIEW TempView
WITH SCHEMABINDING
AS
SELECT GETDATE() AS CurrentTime, 1 AS FirstCol
GO

-- Check the data from the view
SELECT *
FROM TempView
GO

-- Attempt to create the index
CREATE UNIQUE CLUSTERED INDEX [IX_ClusteredIndex_TempView] ON [dbo].[TempView]
(
FirstCol ASC
) ON [PRIMARY]
GO

/* Above query will throw the 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 the view and remove the non-deterministic function GETDATE
ALTER VIEW TempView
WITH SCHEMABINDING
AS
SELECT 1 AS FirstCol
GO

-- Creating the index will now work successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ClusteredIndex_TempView] ON [dbo].[TempView]
(
FirstCol ASC
) ON [PRIMARY]
GO

As you can see in the example above, when we tried to create an index on the view that used the non-deterministic function GETDATE, we encountered error 1949. However, after removing the non-deterministic function from the view, we were able to create the index successfully.

It’s important to note that removing the non-deterministic function from the view might not always be the ideal solution, especially if the function is providing valuable information. In such cases, you might need to find alternative ways to achieve your desired outcome.

Understanding non-deterministic functions and their impact on creating indexes in SQL Server can help you avoid errors and ensure the smooth functioning of your database. It’s always recommended to review the functions you are using in your views and consider their determinism before attempting to create indexes.

Thank you for reading! If you have any questions or comments, feel free to leave them below.