0

Is it possible to retrieve the application name connection parameter without using APP_NAME() because I've to use in a view sql and I can't create an index due APP_Name() it's a non-deterministic function

Red5goahead
  • 1
  • 4
  • That's like asking how to make `getdate()` deterministic. It's supposed to not be deterministic. – GSerg Jul 11 '17 at 14:29
  • I tried a lot of solutions. maybe the only to avoid non-deterministic issue and use a variable into a view with an index is the SESSION_CONTEXT but it's avaiable on Sql 2016 only. SESSION_CONTEXT should be return a deteministic value – Red5goahead Jul 12 '17 at 10:43
  • With all due respect, I don't think you understand what you're doing. Values returned by `app_name` and `session_context` are going to be different depending on who's making the connection. That is why they are non-deterministic. It does not even make sense to try to disguise them as deterministic. If you want to capture their values at the time the record is inserted, create a regular column with a default of `app_name()` and index that. – GSerg Jul 12 '17 at 12:05
  • I'm trying to improve the performance of a platform that was created long time ago (not by me) based on tables and views. Views works heavily on connection application name used as login /user name. Basically we have hundrends of tables and hundrends of views . Tables can be write by thousands of users and views return subsets of records using the APP_NAME() in the where statements. The question is that indexes on views can't be created due APP_NAME() function – Red5goahead Jul 12 '17 at 13:07

0 Answers0