topic Re: Views, SQL Server Express, and SDE 10.1 in Data Management Questions

Views, SQL Server Express, and SDE 10.1

TimHayes — Fri, 14 Sep 2012 19:38:42 GMT

Can SDE 10.1 used with SQL Server Express generate Views? I have multiple feature classes in an SDE/SQL Server Express Geodatabase and would like certain records to be "collated" into a View for the user to access inside one of my ArcGIS Server Flex Map Viewers.

Re: Views, SQL Server Express, and SDE 10.1

VinceAngelo — Fri, 14 Sep 2012 20:47:19 GMT

What kind of view? It sounds like you want a UNION ALL join, which is tricky, at best.

Best practice for views is often to use native geometry, edit the view natively, then
register it when you've got it working correctly. For SDEBINARY storage, this wouldn't
work, and you're tightly constrained on how you can create the view (I'll generally
make a fake table with the columns defined as I'll need them, join that in a view,
then edit the view after, but you have to be *really* careful not to break the view).

- V

Re: Views, SQL Server Express, and SDE 10.1

TimHayes — Fri, 14 Sep 2012 22:04:36 GMT

What kind of view? It sounds like you want a UNION ALL join, which is tricky, at best.

Best practice for views is often to use native geometry, edit the view natively, then
register it when you've got it working correctly. For SDEBINARY storage, this wouldn't
work, and you're tightly constrained on how you can create the view (I'll generally
make a fake table with the columns defined as I'll need them, join that in a view,
then edit the view after, but you have to be *really* careful not to break the view).

- V

I should explain more clearly, here is what I would like to do:

I have 5 Feature Classes (all Lines) in the Geodatabase:
- Water Pipes
- Gas Pipes
- Sanitary Sewer Pipes
- Air Pipes
- Chemical Pipes

I would like my users to be able to access these layers as a read only (Select only), no editing, for use in InforEAM 8.5 Asset Management GIS Module which accesses ArcGIS Server 10.1 via a Map Service (which ties directly to the MXD, as you know). But, the trick is I want the user to see these Feature Classes inside the InforEAM Map Window as "one Feature Class", remember these are separate Feature Classes in the Geodatabase. I assume creating a View from these 5 Feature Classes is the correct approach?

Re: Views, SQL Server Express, and SDE 10.1

VinceAngelo — Sat, 15 Sep 2012 00:37:54 GMT

So all these tables have identical attributes, and have been implemented using GEOMETRY
storage with identical parameters? And none of them are versioned? And they've all been
configured with different, non-overlapping rowid sequences? Then if Microsoft supports
UNION ALL views with GEOMETRY, and the performance is adequate, it might be possible
to register the resulting view.

But you'd still get better performance if you appended the various pieces into a new single
table, and set up a replication mechanism for this "materialied view" from the component
tables.

- V

Re: Views, SQL Server Express, and SDE 10.1

TimHayes — Sat, 15 Sep 2012 01:26:43 GMT

So all these tables have identical attributes, and have been implemented using GEOMETRY
storage with identical parameters? And none of them are versioned? And they've all been
configured with different, non-overlapping rowid sequences? Then if Microsoft supports
UNION ALL views with GEOMETRY, and the performance is adequate, it might be possible
to register the resulting view.

But you'd still get better performance if you appended the various pieces into a new single
table, and set up a replication mechanism for this "materialied view" from the component
tables.

- V

The answer! thanks! your idea about the single table is brilliant. I will look into this in more detail. I think it just might work.