Is this an inefficient way to write a SQL query?

Let's suppose I had a view, like this:

CREATE VIEW EmployeeView 
AS
 SELECT ID, Name, Salary(PaymentPlanID) AS Payment
 FROM Employees

The user-defined functi开发者_如何学运维on, Salary, is somewhat expensive.

If I wanted to do something like this,

SELECT *
FROM TempWorkers t
  INNER JOIN EmployeeView e ON t.ID = e.ID

will Salary be executed on every row of Employees, or will it do the join first and then only be called on the rows filtered by the join? Could I expect the same behavior if EmployeeView was a subquery or a table valued function instead of a view?

---

The function will only be called where relevant. If your final select statement does not include that field, it's not called at all. If your final select refers to 1% of your table, it will only be called for that 1% of the table.

This is effectively the same for sub-queries/inline views. You could specify the function for a field in a sub-query, then never use that field, in which case the function never gets called.

As an aside: scalar functions are indeed notoriously expensive in many regards. You may be able to reduce it's cost by forming it as an inline table valued function.

SELECT
  myTable.*,
  myFunction.Value
FROM
  myTable
CROSS APPLY
  myFunction(myTable.field1, myTable.field2) as myFunction

As long as MyFunction is Inline (not multistatement) and returns only one row for each set of inputs, this often scales much better than Scalar Functions.

This is slightly different from making the whole view a table valued function, that returns many rows.

If such a TVF is multistatment, it WILL call the Salary function for every record. But inline functions can expanded inline, as if a SQL macro, and so only call Salary as required; like the view.

As a general rule for TVFs though, don't return records that will then be discarded.

---

It should only execute the Salary function for the joined rows. But you are not filtering the tables any further. If ID is a foreign key column and not null then it will execute that function for all the rows. The actual execution plan is a good place to see for sure.

---

As said above, the function will only be called for relevant rows. For your further questions, and to get a really good idea of what's happening, you need to gather performance data either through SQL Profiler, or by viewing the actual execution plan and elapsed times. Then test out a few theories and find which is best performance.