Why does a property show up in > 1 facet? Why do some facets support CoC:Prevent, but others not?

  • Article

Last week, Shaun Stuart posted some questions on his blog about PBM "strangeness".   He was wondering why some properties like "AutoClose" show up in more than one facet, and then why some of those facets support "check on change : prevent" but others don't.

I've reproduced my answer to Shawn, below ...

A property can appear in > 1 facet by design. Facets are based around usage patterns, and so a property like AutoShink may show up in > 1 facet. To a rough approximation, you can think of facets as like views.

The facet evaluation mode is the least restrictive (MIN) over all the facet properties. Suppose a facet F has three properties, P1, P2, and P3. Suppose that it is possible to rollback a transaction when you try to set P1 and P2, but the setter on P3 is non-transactional. Then, F cannot support the CheckOnChange:Prevent mode.

There are a few underlying reasons that facets have different evaluation mode capabilities. In the discussion below, “CoC” means “check on change” and “CoS” means “check on schedule.”

Some property setters do not map to DDL statements that can be rolled back in a trigger. Any facet containing such a property will not support CoC:Prevent.

Some property setters do not map to events supports by EVENT NOTIFICATION objects. Any facet containing such a property will not support CoC:Log (side note: use select * from sys.event_notification_event_types to see supported events).

To see a list of Facets along with their supported evaluation modes, see https://blogs.msdn.com/b/sqlpbm/archive/2008/05/24/facets.aspx

All this said, I can see why a customer like you would be confused. I have added these design change requests (DCRs) for the next release.

a) Show the supported facet evaluation modes in the “Object Explorer Details” page.
b) Show the supported facet evaluation modes in the “General” tab page of the “Facet Dialog”
c) Make the facets and their evaluation modes programmatically discoverable via SQLPS.
d) Add the table from the PBM blog page above and add it to the books on line.

Best regards,
Clifford Dibble
SQL PM