Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - SCHEMABINDING - Binding Views to Underlying Tables

By: FYIcenter.com

(Continued from previous topic...)

How To Bind a View to the Schema of the Underlying Tables?

By default, views are not bound to the schema of the underlying tables. This means that SQL Server will allow you to change underlying table's schema any time. For example, you can drop the underlying table while keep the view. Of course, this will make the view not valid any more.

If you don't want anyone to change underlying table's schema once a view has been defined, you can create a binding view with the "WITH SCHEMABINDING" clause to bind the view to the schema of underlying tables. A binding view has the following features:

  • Changing of underlying table's schema is not allowed as long as there exists one binding view.
  • Indexes can be created only on binding views.

The tutorial exercise below shows you how to create a binding with "WITH SCHEMABINDING":

DROP VIEW fyi_links_view;
GO

CREATE VIEW fyi_links_view (ID, DateString, CountUrl)
   WITH SCHEMABINDING
   AS SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM fyi_links_copy WHERE counts > 1000;
GO
Msg 4512, Level 16, State 3, Procedure fyi_links_view, 
   Line 3
Cannot schema bind view 'fyi_links_view' because name 
   'fyi_links_copy' is invalid for schema binding. 
   Names must be in two-part format and an object 
   cannot reference itself.

CREATE VIEW fyi_links_view (ID, DateString, CountUrl)
   WITH SCHEMABINDING
   AS SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM dbo.fyi_links_copy WHERE counts > 1000;
GO
Command(s) completed successfully.

DROP TABLE fyi_links_copy;
GO
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'fyi_links_copy' because it is being 
   referenced by object 'fyi_links_view'.

The error received in the first CREATE VIEW statement says that you must prefix the table name with the schema name.

The error received in the DROP TABLE statement proves that you can not change the underlying tables if there is binding view.

(Continued on next topic...)

  1. What Are Views?
  2. How To Create a View on an Existing Table?
  3. How To See Existing Views?
  4. How To Drop Existing Views from a Database?
  5. How To Get a List of Columns in a View using "sys.columns"?
  6. How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
  7. How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
  8. How To Generate CREATE VIEW Script on an Existing View?
  9. How To Get the Definition of a View Out of the SQL Server?
  10. Can You Create a View with Data from Multiple Tables?
  11. Can You Create a View using Data from Another View?
  12. What Happens If You Delete a Table That Is Used by a View?
  13. Can You Use ORDER BY When Defining a View?
  14. How To Modify the Underlying Query of an Existing View?
  15. Can You Insert Data into a View?
  16. Can You Update Data in a View?
  17. Can You Delete Data from a View?
  18. How To Assign New Column Names in a View?
  19. How Column Data Types Are Determined in a View?
  20. How To Bind a View to the Schema of the Underlying Tables?
  21. How To Create an Index on a View?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...