SchemaBinding

• Megha Yadav

  SSCommitted

  Points: 1735

  More actions

  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

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  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.

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  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