I have created an indexed view that I imported as an entity in the designer. Why doesn't LGP recognize that there is a PK on this entity (since there is one on the indexed view)?

If I create the PK manually, and then refresh the catalog, will my definitions of PKs on indexed views imported as entities survive?

Also, how do I create UKs in LGP that I have created on the indexed views?

And a more philosophical question: Why isn't there a separate type for indexed views (beside typed views and entities)?

Will any of this change in v3.0?

magic wrote:

I have created an indexed view that I imported as an entity in the designer. Why doesn't LGP recognize that there is a PK on this entity (since there is one on the indexed view)?

Indexed views are, after all, views right? AFAIK this is a limitation of the connector.

magic wrote:

If I create the PK manually, and then refresh the catalog, will my definitions of PKs on indexed views imported as entities survive?

No. But you can control this kind of stuff in Project properties

magic wrote:

Also, how do I create UKs in LGP that I have created on the indexed views?

Please elaborate more on what does "create UKs in LGP" means and what you want it for.

magic wrote:

And a more philosophical question: Why isn't there a separate type for indexed views (beside typed views and entities)?

this isn't supported yet

magic wrote:

I have created an indexed view that I imported as an entity in the designer. Why doesn't LGP recognize that there is a PK on this entity (since there is one on the indexed view)?

Technically, there's no PK on the indexed view, or if you mean with PK a clustered index, of course there's this index, but it's not a PK: it's not a referential identifying element, although in indexed views, the index has to be unique.

So let's say you can assume the unique clustered index is something we can use as a PK, a surrogate key. The problem now is: how to determine if the view is an indexed view? If you look in sys.Views, you'll see there's no difference.

The query to obtain the pk fields results in 0 rows for the indexed view (when joined with sys.views instead of sys.tables), the same for unique constraints. In fact, sys.key_constraints has no row for the indexed view. This leaves only one thing: they're not recognizable as an indexed view, at least we don't know where to pull that info from. I find it strange that the info isn't in the sys. schema views btw. No view has the info, also INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE doesn't.

If I create the PK manually, and then refresh the catalog, will my definitions of PKs on indexed views imported as entities survive?

If you define a PK on the entity manually and the target is a view, the pk definition will persist in the project, as the view has no pk information, and the refresher knows that.

Also, how do I create UKs in LGP that I have created on the indexed views?

In v2.x you can't. This is added in v3 where you can define unique constraints on entities which are then reflected in generated code and also in the meta-data (if the target is a table of course)

And a more philosophical question: Why isn't there a separate type for indexed views (beside typed views and entities)? Will any of this change in v3.0?

They're not separated because you can't see they're different. (at least we can't find a way to pull that info from the meta-data. )

In v3 you can define unique constraints in the designer. It will lead to DDL SQL for tables, which you can toss away if you don't want to apply that to the db. You can define in the designer if UCs in the model should follow pk UCs. However with a target on a view, the target (view) is ignored for model element syncing so all model elements are kept as-is as the refresher knows that these elements can't be determined on views.

As always, I appreciate your detailed answers. Thank you.

I'll try to summarize in my own words: If I want to create an entity based on an indexed view based on the unique clustered index (let's call it a PK for the sake of simplicity), I have to "teach LGP manually" that such an index exists by identifying the PK columns manually. This information can't be retrieved manually, but it also won't get lost if I refresh the indexed view(s).

As for now (v2.x) there is no possibility to also reflect other unique indexes created on the indexed view, but there will be one soon (v3.0 is supposed to come in less than a month, right?).

Besides: MsSQL sucks (in terms of indexed views)! Not only can't they differentiate properly as you outlined, but you can't do so many things you can do with with them in Oracle ...

You got it right.

Another thought on the topic: What if LGP offered a possibility to manually identify an index as the base for the PK or UK?

This way the "manual identification" (choosing the columns that belong to it) of such keys would a) be simplified and b) if the index would be changed or dropped, the associated key could be updated automatically by LGP?

magic wrote:

Another thought on the topic: What if LGP offered a possibility to manually identify an index as the base for the PK or UK?

This way the "manual identification" (choosing the columns that belong to it) of such keys would a) be simplified and b) if the index would be changed or dropped, the associated key could be updated automatically by LGP?

but there's no index / constraint pulled from the meta-data for the view. So it can never detect if it's dropped. Which IMHO comes down to simply specifying what the PK fields are manually, which is what you are doing now as well (no db constraint needed).

hmmm ... did I already say that MsSQL sucks?

magic wrote:

hmmm ... did I already say that MsSQL sucks?

haha you did, but if you feel the need, you can re-state it