3 ms·

SQL Server's inline table-valued functions are a good start. They are essentially parametrized views, which are inlined and "disappear" at the point of use, so

SQL Server's inline table-valued functions are a good start. They are essentially parametrized views, which are inlined and "disappear" at the point of use, so all the usual transformations are available to the query optimizer. They are also quite limited in a number of ways: - Can't parametrize on the underlying table or view, even when it is "compatible" with the function's body. Some sort of OOP-style polymorphism or even JavaScript-style "duck-typing" would be nice. - Can't parametrize the SELECT list or ORDER BY clause. - No type generics (i.e. ability to "vary" the parameter's type when that doesn't require changing the function's body). - Can't pass another function as an argument (what would be called delegate or function pointer in other languages). - Only one SQL statement in the function body (SQL Server 2019 has lifted this limitation somewhat, but we are not quite there yet). - Can't (explicitly) throw exceptions. - And probably other things that escape me at the moment... Some of these limitations apply to the stand-alone SQL as well, of course.

throwaway_pdp09 6y ago
Reason for most of these is the optimiser. What if you have eg. 2 tables t1 and t2 which are structurally identical and have select * from parameterised_t where parameterised_t.x = 99 where parameterised_t is either t1 or t2 passed in as a param, the tables can still have very different statistics. The optimiser needs to know statically which it's getting. Of course, if t1 and t2 are structurally identical, you should not usually be splitting them into 2 tables, they should be one table partitioned logically with some predicate (an extra column) rather than physically (having 2 tables). - Can't parametrize the SELECT list or ORDER BY clause. ditto but worse. > - Can't (explicitly) throw exceptions. and that one's a bitch, and quite unnecessary.
- branko_d 6y ago
  > Reason for most of these is the optimiser. Well, optimiser as currently implemented, tying only one plan to each uniqe SQL text. There is nothing, in principle, preventing an implementation that produces multiple plans based on the parameter values. This would be useful not just for "advanced" parametrization I mentioned above, but also for simpler cases such as when a parameter being NULL could be handled via a different access path compared to non-NULL. > if t1 and t2 are structurally identical, you should not usually be splitting them into 2 tables Depends on constraints. For example, you might have two structurally same tables that reference (or are referenced by) different tables.
  - throwaway_pdp09 6y ago
    > ... nothing, ... preventing an implementation that produces multiple plans based on the parameter values I'm afraid there is - time and space. Optimising is a very expensive process that can take well over a second to optimise a complex query on MSSQL (and that's not because the best plan is always found; it has a timout after which the best plan so far found is returned. Complex queries will hit the timeout and a suboptimal plan will most likely be returned), and the compiled query plan can take megabytes. Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. > you might have two structurally same tables that reference (or are referenced by) different tables Umm. I'd say if their semantics were different enough that they be 2 distinct tables then their identicallity is coincidental. I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).
    - branko_d 6y ago
      > I'm afraid there is - time and space. Sure, I'm well aware of that. I did say "in principle", after all. However, we are generating multiple plans already by changing the SQL text (by concatenating-in the table names, SELECT list, ORDER BY and so on). There is currently no good solution in SQL even for parametrizing different "shapes" of WHERE, so we use dynamic SQL for that, generating even more plans. There is some low-hanging fruit here. > Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. You don't have to do it in advance, though. Today, when you change the text of the SELECT list, you generate a new plan anyway. Parametrized SELECT list would be no different. > I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common). Most junction tables have essentially identical structure, but different foreign keys. This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object... But it doesn't really matter whether all fields are identical, only those that are actually used by the given SQL statement. In a hypothetical future SQL, you could implement your algorithm generically and then apply it on any table or view that is "compatible enough", even if it has some extra fields that your SQL statement doesn't use. In today's SQL, unfortunately, you are condemned to repeating the (essentially) same code.

throwaway_pdp09 6y ago

Reason for most of these is the optimiser. What if you have eg. 2 tables t1 and t2 which are structurally identical and have select * from parameterised_t where parameterised_t.x = 99 where parameterised_t is either t1 or t2 passed in as a param, the tables can still have very different statistics. The optimiser needs to know statically which it's getting. Of course, if t1 and t2 are structurally identical, you should not usually be splitting them into 2 tables, they should be one table partitioned logically with some predicate (an extra column) rather than physically (having 2 tables). - Can't parametrize the SELECT list or ORDER BY clause. ditto but worse. > - Can't (explicitly) throw exceptions. and that one's a bitch, and quite unnecessary.

branko_d 6y ago
> Reason for most of these is the optimiser. Well, optimiser as currently implemented, tying only one plan to each uniqe SQL text. There is nothing, in principle, preventing an implementation that produces multiple plans based on the parameter values. This would be useful not just for "advanced" parametrization I mentioned above, but also for simpler cases such as when a parameter being NULL could be handled via a different access path compared to non-NULL. > if t1 and t2 are structurally identical, you should not usually be splitting them into 2 tables Depends on constraints. For example, you might have two structurally same tables that reference (or are referenced by) different tables.
- throwaway_pdp09 6y ago
  > ... nothing, ... preventing an implementation that produces multiple plans based on the parameter values I'm afraid there is - time and space. Optimising is a very expensive process that can take well over a second to optimise a complex query on MSSQL (and that's not because the best plan is always found; it has a timout after which the best plan so far found is returned. Complex queries will hit the timeout and a suboptimal plan will most likely be returned), and the compiled query plan can take megabytes. Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. > you might have two structurally same tables that reference (or are referenced by) different tables Umm. I'd say if their semantics were different enough that they be 2 distinct tables then their identicallity is coincidental. I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).
  - branko_d 6y ago
    > I'm afraid there is - time and space. Sure, I'm well aware of that. I did say "in principle", after all. However, we are generating multiple plans already by changing the SQL text (by concatenating-in the table names, SELECT list, ORDER BY and so on). There is currently no good solution in SQL even for parametrizing different "shapes" of WHERE, so we use dynamic SQL for that, generating even more plans. There is some low-hanging fruit here. > Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. You don't have to do it in advance, though. Today, when you change the text of the SELECT list, you generate a new plan anyway. Parametrized SELECT list would be no different. > I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common). Most junction tables have essentially identical structure, but different foreign keys. This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object... But it doesn't really matter whether all fields are identical, only those that are actually used by the given SQL statement. In a hypothetical future SQL, you could implement your algorithm generically and then apply it on any table or view that is "compatible enough", even if it has some extra fields that your SQL statement doesn't use. In today's SQL, unfortunately, you are condemned to repeating the (essentially) same code.