Use of function calls in stored procedure sql server 2005?

Use of function calls in where clause of stored procedure slows down performance in sql server 2005?

SELECT * FROM Member M 
WHERE LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'administrator' 
AND LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'moderator' 

In this query GetLookupDetailTitle is a user defined function and LOWE开发者_如何学JAVAR() is built in function i am asking about both.

Yes.

Both of these are practices to be avoided where possible.

Applying almost any function to a column makes the expression unsargable which means an index cannot be used and even if the column is not indexed it makes cardinality estimates incorrect for the rest of the plan.

Additionally your dbo.GetLookupDetailTitle scalar function looks like it does data access and this should be inlined into the query.

The query optimiser does not inline logic from scalar UDFs and your query will be performing this lookup for each row in your source data, which will effectively enforce a nested loops join irrespective of its suitability.

Additionally this will actually happen twice per row because of the 2 function invocations. You should probably rewrite as something like

SELECT M.* /*But don't use * either, list columns explicitly... */
FROM Member M 
WHERE NOT EXISTS(SELECT * 
                 FROM MemberRoles R 
                 WHERE R.MemberId = M.MemberId 
                 AND R.RoleId IN (1,2)
                 )

Don't be tempted to replace the literal values 1,2 with variables with more descriptive names as this too can mess up cardinality estimates.

Using a function in a WHERE clause forces a table scan.

There's no way to use an index since the engine can't know what the result will be until it runs the function on every row in the table.

You can avoid both the user-defined function and the built-in by

• defining "magic" values for administrator and moderator roles and compare Member.RoleId against these scalars

• defining IsAdministrator and IsModerator flags on a MemberRole table and join with Member to filter on those flags