Extra Notes Flashcards
What is the difference in creating an Inline table-value function and Multistatement?
CREATE FUNCTION dbo.Inline(@var varchar(5))
RETURNS TABLE
CREATE FUNCTION dbo.MultiStatement(@var varchar(5)) RETURNS TABLE (OutVar varchar(5), OutVar2 int)
What is the suitable FILLFACTOR setting for indexes on an OLTP table?
FILLFACTOR=0 - Means that it will fill in as many rows into a page as possible (85-90)
When should you use PAD_INDEX = OFF?
When you are not using FILLFACTOR
What does PAD_INDEX do for you?
Takes the percentage setting that is applied to the FILLFACTOR option and applies it to the intermediate level of the index.
What are the steps to create a FULL-TEXT Search?
- Install FULL-TEXT Search
- Create a full-text catalog
- Create a unique, single-column, non-nullable index
- Create the full-text index
What does SET XACT_ABORT [ON, OFF] do?
Enables (ON) or disables (OFF) the ability for the insert statements to continue if an UPDATE statement fails.
IE: If XACT_ABORT is OFF and you have multiple insert statements, all of the statements will continue on even if one were to fail.
How can you make sure that multiple insert statements wait until all of the insert statements have been completed before COMMIT?
By using SET IMPLICIT_TRANSACTIONS ON
No commits will be done until expressly told to in the batch.
Can you use data compression on a table that includes a sparse column?
No.
Can you create a unique index on a column that has more than one NULL?
No, the second null would not be unique.
What does SCHEMABINDING do for a view?
Ensures that changes are not made to the underlying tables
What does WITH CHECK OPTION do for a view?
Ensures that any modifications to the underlying table adhere to specified criteria in the SELECT statement associated with the view
How can you modify multiple tables within a view?
Use an INSTEAD OF trigger. This will allow you to modify all of the tables specifically because they can’t be updated within the view.
What does SET STATISTICS TIME show you?
CPU and elapsed time for a query
What does SET STATISTICS IO show you?
Table Scan logical reads physical reads read-ahead reads lob logical reads lob physical reads lob read-ahead reads
What does SET STATISTICS PROFILE show you?
- Rows - Actual number of rows produced by each operator
* Executes - Number of times the operator has been executed
