I am running ArcSDE 10 with SQL Server 2008 R2 Standard Edition. I am new to SDE and SQL Server, but I understand that SQL Server has the ability to create relationships between tables and maintain certain referential integrity rules.
ArcGIS has relationship classes that act similarly, but a relationship class does not have all the features of SQL relationships and does not result in a SQL relationship in the ArcSDE database.
Is it possible to create relationship classes in ArcGIS for an ArcSDE database and create relationships for the same tables in SQL Server? By doing so, I will be able to utilize these relationships whether I am working with the data in ArcGIS or in SQL Server Management Studio. Will the two types of relationships conflict with each other or otherwise hinder performance?
Best Answer
SDE and SQL are not really friends. They do not cooperate very well. SDE uses sql but does not take advantage of all its native capabilities. A relationship set up in sde is not reflected in SQL. Editing feature class tables managed by SDE, modifying table schema's outside of catalog, as well as doing many other things, will pork SDE. Given this track record, I would leave the relationships up to SDE if you're trying to relate feature class information. If you are using regular tables cut sde out and use native sql.
There are no references for this other than my own experiences. If that is unsourced materials then dispute or delete this.