Answers

Question and Answer:

  Home  MS SQL Server

⟩ 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 ggl_links_view;

GO

CREATE VIEW ggl_links_view (ID, DateString, CountUrl)

WITH SCHEMABINDING

AS SELECT id, CONVERT(VARCHAR(16), created, 107),

CONVERT(VARCHAR(20),counts)+' - '+url

FROM ggl_links_copy WHERE counts > 1000;

GO

Msg 4512, Level 16, State 3, Procedure ggl_links_view,

Line 3

Cannot schema bind view 'ggl_links_view' because name

'ggl_links_copy' is invalid for schema binding.

Names must be in two-part format and an object

canno

 134 views

More Questions for you: