locked
Cannot use SchemaBinding in SQL Server 2012 instance RRS feed

  • Question

  • In a new database created by code CREATE DATABASE AUS_WEEKLY, when I try to create a view with schemabinding, I get the error Msg 4513, Level 16, State 2, cannot schema bind view. In another instance, I can use schemabinding on an AdventureWorks2012 database. How do use schemabinding?

    

    Sandy Shin

    Monday, January 5, 2015 3:32 AM

Answers

  • My guess is view definition is using a UDF (User Defined Function) which is not schemabound by itself. That being the case first make it schemabinding then view will also work with schemabinding.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, January 5, 2015 3:54 AM
  • BOL says

    All base tables referenced by the view must be in the same database as the view and have the same owner as the view.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 5, 2015 6:16 AM
  • Hello ,

    I'm suggesting you to see msdn ref :

    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.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, January 5, 2015 7:43 AM

All replies

  • Can you post the exact message text? May be your view doesn't conform criteria for schema binding (there are several rules the view has to conform to).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Ahsan KabirMVP Monday, January 5, 2015 6:53 AM
    Monday, January 5, 2015 3:34 AM
    Answerer
  • My guess is view definition is using a UDF (User Defined Function) which is not schemabound by itself. That being the case first make it schemabinding then view will also work with schemabinding.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, January 5, 2015 3:54 AM
  • BOL says

    All base tables referenced by the view must be in the same database as the view and have the same owner as the view.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 5, 2015 6:16 AM
  • Hello ,

    I'm suggesting you to see msdn ref :

    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.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, January 5, 2015 7:43 AM
  • WITH SCHEMABINDING can be used in Views and T-SQL Functions, but not in Stored Procedures.

    Objects that are schema bound can have their definition changed, but objects that are referenced by schema bound objects cannot have their definition changed.

    Schema binding effectively states that the meta-data which is created at object creation time can then be relied upon to be accurate at all times, and use of sp_refreshsqlmodule is not necessary.

    Schema binding can also significantly increase the performance of user defined functions in SQL Server 2005 and above. However, caution should be applied, as this is definitely not always the case.

    1 A beautiful answer, but I think there are a couple of things worth adding.

    First, while "but objects that are referenced by schema bound objects cannot have their definition changed." is technically true, you cannot generally rely on schemabinding as a way to prevent changes by people with appropriate permissions. SSMS will give you a warning, but then remove schemabinding in order to make changes ordered through the gui.

    Also, it is worth noting that schemabinding is required for a view if the view is going
    Monday, January 5, 2015 10:49 AM