Problem creating an index

Question

Post reply

Problem creating an index

john-822794

SSC Enthusiast

Points: 137
More actions
November 26, 2008 at 4:54 am

#203496

Microsoft SQL Server Developer Edition
Versin: 9.00.3068.00
Database is running in SQL Server 2000 compatability mode.
I have two tables - nothing too fancy - and a view that selects a number of fields from both using an INNER JOIN.
When I try to create a unique clustered index on the view i get the following error:
Cannot create index on view 'MY_DATABASE_NAME.dbo.MY_VIEW_NAME. The function 'user_name' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.
I have reached a dead end in trying to solve this, I understand that there are limitations on creating indexes on views, but as far as I can tell none of these are applicable.
Can anyone offer up any advice on how to get past this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 1

Check these links. You may find something helpful:

http://www.sql-server-performance.com/faq/function_in_view_yields_nondeterministic_results_p1.aspx

http://msdn.microsoft.com/en-us/library/ms178091.aspx

john-822794 SSC Enthusiast Points: 137 More actions · Answer 2

Thanks - but already been looked at these.

Anyway problem is solved.

Have just realized that while the error message relates to the function "user_name", the view uses SESSION_USER in the WHERE clause of the view.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

User_name and session_user are nondeterministic functions, as are most similar security functions. They can return different results when called with the same parameters. As such, they may not be part of an indexed view.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass