Post reply

SchemaBinding

  • December 10, 2007 at 1:22 am

    #70611

    "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"

    from : http://msdn2.microsoft.com/en-us/library/ms187956.aspx

    It also states "If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried."

    My Questions being

    1) How does Schemabinding help performance?

    I read when schemabinding applied to UDF's it enhances performance

    2) With Schemabinidng clause the view does not need to be refreshed? Will the refresh take place automatically ? or at what interval?

    3) what does it affect the UDF's?

    Thanks

  • December 10, 2007 at 4:11 am

    #758523

    Schemabinding does not enhance UDF's performance.

    It will make sure that the columns refered in the UDF/view are not dropped.

  • December 12, 2007 at 12:47 am

    #759280

    Hi Megha,

    Check the following links :

    1- Use of SCHEMABINDING option for TSQL UDFs can improve performance in SQL Server 2005

    http://blogs.msdn.com/sqltips/archive/2006/06/12/628722.aspx

    http://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx

    SQL Server 2005 has new optimization logic to use the SCHEMABINDING option to derive certain properties about the TSQL UDF.

    This can greatly improve performance of queries that use scalar UDFs in a SELECT statement.

    2- See below

    http://msdn2.microsoft.com/en-us/library/ms187956.aspx

    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.

    If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying

    the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

    Note:

    Improving Performance with SQL Server 2005 Indexed Views

    http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

    Regards,

    Ahmed

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply