none
with schemabinding in function.

    Question

  • Hi

    With respect to performace.

    1) "with schemabinding"  when used with function, performs better ?

    i got information that with schemabinding in scalar function does not use "table spool" , so we are planning to use  inline functions.

    so we would like to know above question for inline function also and others.

    2) Can it also perform  better in compution specific functions (if we are not using CLR functions)?

    so if both points are correct , then should we use "with schemabinding" all the time?

         i have tried practically but show things are changing in diffrent senario.

    yours sincerely.

    Monday, November 19, 2012 7:21 AM

Answers

  • As often when it comes to SQL Server, there are a whole lot of "it depends" and non-intuitive behaviour, but yes there are situations where adding WITH SCHEMABINDING to a function can improve performance. It does not make the function run faster, but it helps the optimizer when the function is used in a larger query. More precisely, it is a condition for SQL Server consider the function to be deterministic. If the function is non-deterministic by its nature, for instance it uses getdate(), I don't think SCHEMABINDING has any effect.

    I should add that this mainly applies to scalar and multi-statement functions which are black boxes for the optimizer. Inline functions are a different matter.

    SCHEMABINDING also has the effect that if the function refers to a table, you cannot drop that table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Hasham NiazEditor Monday, November 19, 2012 8:47 AM
    • Marked as answer by rajemessage Wednesday, November 21, 2012 4:02 PM
    Monday, November 19, 2012 8:09 AM
  • The below is a good article worth reading:

    http://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/20/t-sql-user-defined-functions-the-good-the-bad-and-the-ugly-part-1.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by rajemessage Wednesday, November 21, 2012 4:01 PM
    Monday, November 19, 2012 8:44 AM

All replies

  • Schemabinding binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

    Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

    SCHEMABINDING cannot be specified if the view contains alias data type columns.

    Well, to put it simply, once you create a view with schemabinding, you cannot change the underlying tables in a way that would break the view.  Examples of this would be removing columns or dropping tables that are specified in the view.

    Many Thanks & Best Regards, Hua Min

    Monday, November 19, 2012 7:36 AM
  • As often when it comes to SQL Server, there are a whole lot of "it depends" and non-intuitive behaviour, but yes there are situations where adding WITH SCHEMABINDING to a function can improve performance. It does not make the function run faster, but it helps the optimizer when the function is used in a larger query. More precisely, it is a condition for SQL Server consider the function to be deterministic. If the function is non-deterministic by its nature, for instance it uses getdate(), I don't think SCHEMABINDING has any effect.

    I should add that this mainly applies to scalar and multi-statement functions which are black boxes for the optimizer. Inline functions are a different matter.

    SCHEMABINDING also has the effect that if the function refers to a table, you cannot drop that table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Hasham NiazEditor Monday, November 19, 2012 8:47 AM
    • Marked as answer by rajemessage Wednesday, November 21, 2012 4:02 PM
    Monday, November 19, 2012 8:09 AM
  • The below is a good article worth reading:

    http://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/20/t-sql-user-defined-functions-the-good-the-bad-and-the-ugly-part-1.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by rajemessage Wednesday, November 21, 2012 4:01 PM
    Monday, November 19, 2012 8:44 AM