9

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?

Improve this question
3
  • This is just a guess (which is why it isn't an answer) but I would bet that adding relationships could cause some conflicts unless you're very careful. On an important side note, if you're versioning your tables, you don't want to read them from the SQL side, only from the GIS side. Reading from the SQL side only shows the oldest version of the data (and not the changes that have been made that constitute the versions).
    – Michael Todd
    Nov 17, 2011 at 16:04
  • @MichaelTodd - Thanks for your response. I've heard about the issues with accessing versioned data through SQL Server. However, I have also heard that this is possible using multi-versioned views. I'm still a newbie with this stuff, so I'm not exactly sure what that means, but my takeaway is that it is possible. I'm just finding that when it comes to data management in ArcSDE, ArcGIS is the weakest link.
    – Brian
    Nov 17, 2011 at 16:34
  • 1
    Yes, a multi-versioned view works, but it's vastly slower. We went from sub 1-second queries to 4-second queries when we switched to MVVs (which doesn't sound like much but the lag was very noticeable internally as well as to external customers).
    – Michael Todd
    Nov 17, 2011 at 18:02

1 Answer 1

Reset to default
7

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.