Hugo Kornelis (11/4/2010)
Hi David,... But in the rest of the article, you only describe routines generated upon primary keys and unique constraints. So technicallly, the developer can still get away with not defining the foreign key, as long as the PK and UQ constraints are defined.
...
You should convert to inline table-valued functions. These, like views, are treated as macros - the reference to the function is first replaced by its definition, and only then will the optimizer be invoked to work out an execution plan. You'll see a major performance gain!
Hugo: Thanks so much for your posts, especially this one. First, RAP does generate functions and procedures for every foreign key; the particular table I chose for illustration purposes just doesn't happen to contain any foreign business keys - I chose it for the sake of brevity. If you look at the ExampleCRM tables that have foreign keys that are defined for business purposes (not just the status-field foreign key) you will see the function and procedure definitions.
Secondly, I like your code here. It never occurred to me to use a union. Are you sure that the union would execute faster than the "if" statement and the two simpler separate queries in RAP's generated code? I guess you are, so I am definitely going to check this out.