Collections:
SCHEMABINDING - Binding Views to Underlying Tables in SQL Server
How To Bind a View to the Schema of the Underlying Tables in SQL Server?
✍: FYIcenter.com
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:
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.
⇒ Creating an Index on a View in SQL Server
⇐ Determining Data Types of View Columns in SQL Server
2016-11-03, 1829🔥, 0💬
Popular Posts:
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...
How To Get a List of All Tables with "sys.tables" View in SQL Server? If you want to see the table y...
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...