Extra Notes Flashcards Preview

70-464 Certification > Extra Notes > Flashcards

Flashcards in Extra Notes Deck (18)
Loading flashcards...
1

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)

2

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)

3

When should you use PAD_INDEX = OFF?

When you are not using FILLFACTOR

4

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.

5

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

6

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.

7

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.

8

Can you use data compression on a table that includes a sparse column?

No.

9

Can you create a unique index on a column that has more than one NULL?

No, the second null would not be unique.

10

What does SCHEMABINDING do for a view?

Ensures that changes are not made to the underlying tables

11

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

12

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.

13

What does SET STATISTICS TIME show you?

CPU and elapsed time for a query

14

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

15

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

16

What does SET STATISTICS XML show you?

Execution information for each statement after executing it

17

When should you use the Image datatype?

Never, it's been depreciated.

18

When creating a CLR function in T-SQL, do you need the parameter in the AS EXTERNAL NAME Assembly.Class.Method (Parameter)?

No, if you put the parameter there you'll get an error.