AnsweredAssumed Answered

Best practices for using nonspatial tables (SQL Views) as event layers?

Question asked by szwillinger on Feb 4, 2015
Latest reply on Sep 4, 2019 by bixb0012

To all of the ArcSDE experts,


What are your best practices for using nonspatial tables and/or SQL Server views as input for event layers?  The Help docs for 9.3 note that because ArcGIS cannot add an ObjectID field to a view, you cannot register a view with the geodatabase.  (I'm using 10.2, but I don't think this has changed.) If a view is not registered, it means that the table (even if it has a field called ObjectID) will not work properly as input for an event layer.  Sure, the event layer can be created, but if you try to query the data with a "Select by Attributes", you get very strange results.  In addition, you are not able to select features on the map with the Select tool.  This is a severe limitation when your data is changing frequently and you want users to be able to see those changes dynamically.  With large data files, the process to convert the event layer to a feature class is painfully slow.  So, any suggestions on best practices?  Would it help to create an indexed view in SQL Server? (Indexed views can provide better performance but this has to be weighed against the increased maintenance requirements of the database.  Would ArcGIS "recognize" an indexed view?)


Are we better off duplicating a view as a regular, indexed table and then registering that for the event layer?  This would require some kind of automatic trigger to re-create the table after the underlying tables were changed by users and seems like a very bad idea when you have multiple people editing the data.


I'm also wondering whether tables that are registered have limited functionality for ArcGIS Desktop Basic users versus ArcGIS Desktop Standard users.  Most of the users only have a Basic license and it would be costly to upgrade everyone to a Standard license.  Currently, the underlying SQL tables can be edited through a custom form in ArcGIS regardless of the license level being used.  When the data is refreshed, the event layers show the edits made by the users.