A word of caution

I took the tip about SCHEMABINDING and the optimizer's expanding iTVFs and converted a Multi-statement UDF (referred to from here on as the TF) that splits up to 12MB of delimited data using a Numbers table. Perhaps this is SS2005EE-specific and later versions are smarter, but...

BAD MOVE!

I have this (inherited) snippet from an SP that uses CROSS APPLY in the FROM clause and has a WHERE clause that contains DBA.dbo.udf_Split(), which is the TF I converted to an iTVF. The SP happens to be the most important SP in the system - the one that gets a User's permissions to do anything. It runs in under 1 second for over 500 Users using the original TF. However, it takes 15+ minutes to run the same SP using the iTVF for a single User, I consider this a very bad move and will pull the plug on changing this TF to an iTVF:-D

FROM dbo.t_user_ref ur

CROSS APPLY dbo.fn_GetPermissionByUser(user_id) AS p

WHERE GETDATE() BETWEEN eff_date AND term_date

AND ( @CompanyID IS NULL

OR ( @CompanyID IS NOT NULL

AND @CompanyID > 0

AND company_id = @CompanyID

)

)

AND ( @SiteIDs IS NULL

OR ( @SiteIDs IS NOT NULL

AND EXISTS ( SELECT 1

FROM DBA.dbo.udf_Split( @SiteIDs + ', ',',')

WHERE ',' + r_site_ids + ',' <> REPLACE(',' + r_site_ids + ',', ',' + [value] + ',', '')

)

)

)

AND ( @user-id IS NULL

OR ( @user-id IS NOT NULL

AND @user-id > 0

AND user_id = @user-id

)

)

AND ( @UserRole IS NULL

OR ( @UserRole IS NOT NULL

AND p.r_user_role LIKE '%' + @UserRole

)

)

The optimizer did indeed expand the iTVF form of the UDF into the main SELECT, but then goodness knows why it decides that 12789618 rows must now be scanned for each User row.

Be very, very careful which TFs you convert to iTVFs... :ermm: