Schemabinding Flashcards
What can you use SchemaBinding with?
Creating a view or function
What does Schemabinding do?
Binds the structure of any underlying table or view
You can not change ______ of a database with schemabound objects.
collation
True or False?
You can use Select * in a schemabound view?
False
True or False? You can make any change to the table that do not affect the structure of the schemabound columns.
True
Where can you look to see if an object (Column or Function) is schemabound?
Column: is_schema_bound in sys.sql_modules
Function: OBJECTPROPERTY
Objects that are bound (tables/views) can not be dropped while a schemabound object references them.
True or False
True
if you reference a view or function in a schemabound view or function then that view or function must also be what?
Schemabound
Schemabinding isn’t a commonly used tool unless you are setting up what?
An indexed view
When you use schmabinding, the select_statement, there are three objects that can be referenced, what are they?
Tables
Views
User-defined functions
True or False: when using schemabinding, all referenced objects must be in the same database?
True
Views or tables that participate in a view created with the schemabinding clause can or cannot be dropped unless that view is dropped or changed so that it no longer has schemabinding?
Cannot
List basic syntax order for creating a view with Schemabinding
5 Steps
CREATE VIEW WITH Schemabinding AS SELECT FROM
List basic syntax order for creating or altering a function with Schemabinding
4 Steps
CREATE or ALTER FUNCTION
RETURNS
WITH Schemabinding
AS
True or False: Stored procedure can be schemabound?
False
Schemabinding prevents ____ or _____ of any object on which the function depends?
Altering or dropping
Note: if a schema-bound function references a table, then columns may be added to the table, but no existing columns can be altered or dropped, and neither can the table itself.
Indexed views must be created with which option?
With Schemabinding
The Schemabinding option ensures that structural changes against referenced objects and columns will be what?
rejected
What clause should you specify when creating a function that will ensure that the objects referenced in the function definition cannot be modified unless the function is also modified?
With Schemabinding
True or False: When you use schmabinding, the select_statement must include two-part names that are referenced.
True
