Measure Up Create Database Objects Flashcards
What are the two types of DML triggers?
After
Instead of
What should you use to ensure that the underlying table structure cannot be modified?
With Schemabinding
What should you use to ensure that no modification can be made that would prevent a row currently included in the view from being excluded from the view?
Note: when a query is issued against this view that attempts to change the value of the column data, an error occurs.
With Check Option
What should you use to hide the definition of how a view was coded?
With Encryption
Which constraint is used to uniquely identify each record and not allow any blank values?
Primary Key constraint
What should you use to ensure that only metadata for the view can be read, not that of the underlying base tables?
With View_Metadata
Which clause enforces the restriction that only rows that match the condition defined by the view can be modified through the view?
With Check Option
List the statement Syntax order used to turn off constraint checking
Alter [Table]
NoCheck Constraint
List the statement syntax order used to fix a table accidentally saved as part of the incorrect schema.
Alter [Schema]
Transfer [Table]
Which function can be used within a trigger to determine whether an Update or Insert statement has attempted to modify the value in a column?
Update()
**Note: you pass the name of the column to the function within the parentheses
List the statement Syntax order used to modify an existing column in a table.
Alter [Table]
Alter [Column]
Which command should be used to remove a table?
Drop [Table]
What commands are used to remove rows from a table, but not to delete the entire table structure?
Note: 2 commands can be used.
Truncate [Table]
Delete [Table]
Can an index be created on a view that is based on a query that performs a Right Join?
No
Note: A right join is an an outer join. You cannot create an index on a view that is based on a query that performs an outer join.
What statement should be used to combine the result sets and return all the records from both Select statements?
Union All
What command should be used to remove a view?
Drop View
What command should be used to remove rows from a view?
Delete View
Can you use Alter [Column] to rename a column name?
No
Note: To rename a column, you must execute the sp-rename stored procedure.
List the statement Syntax order used to add new columns/fields to a pre-existing table?
Alter [Table]
Add [Column(s)]
What commands should you use if you want to create a Table in a Schema that doesn’t exist?
Create [Schema]
Create [Table]
Note: Because the Schema doesn’t exist, both should be created.
A foreign key constraint can be defined in the column definition by using either what keyword or clause?
References
Foreign Key References
Which clause causes the data to be formatted with each table represented by an element and each column represented as an attribute?
For XML Auto clause
Which clause is used to identify the name of the outer element?
For XML Path
What view hint should be used to force the query optimizer to use indexes built on the view?
NOEXPAND view hint
What view hint should be used to force the execution plan to ignore the index?
Force_Scan view hint
Which command is used to modify existing columns in a table but not create new ones?
Alter [Column]
Note: Use Add [Column] to add a new column
To create a column that does not allow NULL values, which keyword is used?
Not Null
What keyword is used to create a computed column and specifying the formula used to calculate the column in parentheses?
As
Note: looks like this
Column As (Column + Column)
Which trigger should be used to update view when more than one base table needs to be updated?
Instead Of
If you want to ensure that when a record is deleted in the parent table of the relationship, the value of the Foreign Key is updated. What Referential Integrity option is used for this task?
On Delete Set Default
Note: This will ensure that the value of the Foreign Key is updated.
What keyword can be used in a Correlated Subquery to filter the results set so that it contains only the rows that match at least one row in the other table?
Exists
What type of integrity is used to validate entries for specific columns?
Domain Integrity
What type of integrity is used to define a row as a unique entity?
Entity Integrity
What type of integrity is used to define relationships between tables?
Referential Integrity
Which function would be used to return a varbinary bit pattern indicating which columns in a table or view had been modified?
Columns_Updated
Which function would be used to return event information used with a DDL Trigger?
EVENTDATA
What type of value will the Update function return if the column referenced with the function has been modified?
Boolean
What constraint is used to provide a relationship between tables?
Foreign Key constraint
Does the Unique constraint allow for a single Null value?
Yes
What clause needs to be used within a View to be able to use the Order By statement?
Top
What clause is used to return unique records?
Distinct
What can you use with Order By to sort the order?
Asc or Desc
What type of view is a special type of view that is defined by a Union All of tables structured in the same way but, stored separately as multiple tables?
Partitioned View
What clause allows you to create a subquery that returns True as soon as it encounters a match?
Note: It’s the optimal way to create a query based on whether there is a row corresponding that matches a condition in a different table.
Exists
Are Alter Column and Add Column valid statements within a view?
For example If you need to add a column to a view?
No
Use the Select statement in a view to include a column
You can create a column that contains sequential integers by creating a column as what type of column?
Identity
Note: you need to specify the data type before using the identity property
The identity property accepts two values, what are they?
The starting value and the increment value
You can apply what type of constraint to a calculated column on if you include the Persisted keyword.
Check
What function is used to generate a Globally Unique Identifier (GUID) that is sequential to the last value generated?
NEWSEQENTIALID()
Does the data type need to be identified before or after the Not Null statement?
Before
The ISNULL function accepts two parameters, what are they?
A value to check and a replacement value.
Note: If the value to check is not Null, the value is returned. If the value to check is Null, the replacement value is returned.
Can you define a data type for a computed column?
No
The Persisted keyword must occur before or after the computation?
After
What keyword needs to be included if you are defining a Check constraint on a computed column?
Persisted
You can change the nullability of a column from Not Null to Null by using what statement?
Alter Table [Table]
Alter Column [Column]
What keyword allows you to define a common table expression (CTE), which allows you to create a temporary result set?
With
What does CTE stand for?
Common Table Expression
What operator is used to join two tables with the same set of columns?
Union
The Union operator keeps only what kind of rows?
distinct rows
The Union All operator keeps what kind of rows?
duplicate rows
What join returns all the records in the table specified in the From clause, even if there is not a match with any record in the table specified in the Join clause?
Left
When you use an aggregate function, you need to either use 1 of 2 clauses, what are they?
Group By or Over
What function returns the name of the month?
DATENAME
What join is used to include all rows in the table specified in the Join clause, even if there is not a match with the rows in the table specified in the From clause?
Right
What keyword allows you to define different ways to group data, for example grouping total sales by month.
Grouping Sets
Example: you can group total sales by month.
Oct | Nov | Dec
$632.00 | $257.18 | $1679.30
What clause can be used to combine the results from one Select statement with the results from another Select statement.
Note: Clause not Join
Union
Both result sets need to have a compatible column list to use what clause?
Union
To create an auto generated column with a uniqueidentifier (GUID), it needs to default with either 1 of 2 functions. What are the functions?
NEWID() or NEWSEQENTAILID()
To turn off constraint checking when inserting data into a table, what should be used?
With No Check
When creating an indexed view on a table that contains computed columns, you need to ensure that all columns in the Select statement are what?
Deterministic
A column must be marked Persisted if it has what constraint or if it participates in what type of relationship?
Check constraint
Foreign Key relationship
In order to index a view, you need to create it with what attribute?
Schemabinding
Which option ensures that only the rows included in the view can be modified through the view?
Check Option
Is the Check Option clause placed after or before the Select statement that defines the view?
After
What attribute is used to prevent certain type of client libraries from viewing information about the base tables?
VIEW_METADATA
Can you use a wildcard in the selection list when defining a schema bound view?
No
When the view is schema bound, you need to refer to the table how?
Using its two part name.
What field is used to automatically increment the value of the field when a new record is added?
Identity(1,1)
Note: Identity (1,1) would start counting at the number 1 and increment the value by 1 for each row inserted into the table.
The Identity field has two arguments, what are they and what do they do?
Seed - Starts the counting
Increment - what value to add to the current count
What constraint is used to force each record to have a unique value?
Primary Key Constraint
Does the Primary Key field allow for Null values?
No
What should be added to the view to improve performance of a view?
Index
Does Aliasing have any effect on query performance?
No
This clause with the Select statement is used to prevent duplicate records in the result set and return unique records
Distinct
This clause is used to restrict the total number of records to return from the result set
Top
Which View can be used to modify the data of an underlying base table?
- Select with Top
- Select with Top and Check Option
- Select with Count
- Select with Count and Check Option
Select with Top
Note: the rest are considered non-updatable and cannot be used to modify the data in an underlying base table.
Hint: No check or count, only top
