Extra notes 3

Question 1

How can you use a symmetric key and certificate to protect a column?

Answer 1

OPEN SYMMETRIC KEY KeyName
DECRYPTION BY CERTIFICATE CertificateName
UPDATE TableName
SET Column = EncryptByKey(Key_GUID(‘KeyName’), @VarWithData, 1, HashBytes(‘SHA1’, CONVERT(varbinary, ColumnName)))

Question 2

Can a SCALAR User Defined Function support using a table variable?

Answer 2

Yes

Question 3

What monitoring application would you use to view I/O subsystem performance issues (highest stall times)?

Answer 3

Activity Monitor

Question 4

What can you suspect when locks are taken in a database while using READ_COMMITTED_SNAPSHOT?

Answer 4

TempDB has filled up and when that happens locks are taken on the database level

Question 5

How do you insert a connection string into a query?

Answer 5

Select Col1, Col2

FROM Table OPENDATASOURCE(ConnectionString)

Question 6

Which System View shows All Unused Indexes?

Answer 6

sys.dm_db_index_usage_stats

Question 7

Which System View shows all indexes that must be fragmented?

Answer 7

sys.dm_db_index_physical_stats

Question 8

Which System View shows all indexes that have page splits?

Answer 8

sys.dm_db_index_operational_stats

Question 9

At what point do statistics get generated automatically?

Answer 9

1. When you populate an empty table;
2. When a table with fewer than 500 rows doubles in size;
3. When a table with more than 500 rows grows by 500 rows plus 20% of the current row count

Question 10

What tool do you use to analyze a deadlock graph?

Answer 10

SSMS

Question 11

What two types of data compression are available for rowstore tables and indexes?

Answer 11

Row and Page

Question 12

How does SQL Server produce the result set for a non-indexed view?

Answer 12

The rows and columns of data originate from tables referenced in the query defining the view and they are produced dynamically when the view is referenced.

Question 13

What correctly defines indexed columns and included columns?

Answer 13

Indexed columns form the key of the index, and included columns are non-key columns used to improve query coverage

Question 14

Why is NEWID a nondeterministic function?

Answer 14

NEWID returns a unique identifier and a different result.

Question 15

You possess a database in an availability group with in-memory tables. Users express that sometimes they see the results of updates and sometimes the results of updates are unavailable. What is the most likely explanation?

Answer 15

The application uses delayed durable transactions and the changes have not yet replicated to the secondary replicas.

Question 16

How might you determine an I/O related memory problem in a SQL Server instance?

Answer 16

Use the Dynamic Management View (DMV) sys.dm_os_wait_stats and locate excessive PAGEIOLATCH wait types.

Question 17

How can you use a foreign key with a calculated column?

Answer 17

You have to use the PERSISTED keyword when creating the column.

Question 18

Can you alter a calculated column?

Answer 18

No, in order to change a calculated column you have to drop it and recreate it.

Question 19

What kind of user can you grant access to but that cannot be used for authentication?

Answer 19

A database user without a login.

Question 20

Where should you use a database user without a login?

Answer 20

When you need to authenticate applications at the database level without hardcoding passwords.

Question 21

You have a table trigger that makes sure that data fits within given rules, how can you remove it?

Answer 21

Use constraints, either Foreign Key or Check.

Question 22

What can you use to replace of a trigger that keeps track of changes?

Answer 22

Auditing by using OUTPUT INTO a table from an insert statement:

INSERT INTO table OUTPUT inserted.val1, inserted.val2 into OutputAuditTable values (val1Value, val2Value)

Question 23

What is cardinality?

Answer 23

Number of rows in a query result

Question 24

What are statistics used for?

Answer 24

Used by query optimizer to Create query plans that improve query performance.

Question 25

What are the required steps for creating an indexed view?

Answer 25

1. Verify the SET options are correct for all existing tables that will be referenced in the view.
2. Verify that the SET options for the session are set correctly before you create any tables and the view.
3. Verify that the view definition is deterministic.
4. Create the view by using the WITH SCHEMABINDING option.
5. Create the unique clustered index on the view.