Extra Notes 2 Flashcards Preview

70-464 Certification > Extra Notes 2 > Flashcards

Flashcards in Extra Notes 2 Deck (12)
Loading flashcards...
1
Q

What is necessary to be able to create a foreign key?

A

A primary key or a Unique key on the referenced column.

2
Q

What are the steps (in order) to create a table that is partitioned?

A

Create database file partitions and files
Create Partition Function
Create Partition Scheme
Create the table by using partition scheme

3
Q

What datatype is generated with encryption?

A

varbinary

4
Q

Can a clustered index include a filter?

A

No

5
Q

What is an application role?

A

A database principal that enables an application to run with its own, user-like permissions. Can be used with a certificate to ensure that the certificate can only be used in specific parts of the system

6
Q

What are the steps to create certificate and encryption?

A

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘PASSWORD’

CREATE APPLICATION ROLE roleName WITH PASSWORD = ‘PASSWORD’

CREATE CERTIFICATE certificateName AUTHORIZATION roleName WITH SUBJECT = ‘Certificate Subject’

CREATE SYMMETRIC KEY encrypterName WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE certificateName

7
Q

How can you minimize the number of page splits on a table?

A

Set FILLFACTOR=85

8
Q

What are the different LOCK ESCALATIONS you can use?

A

AUTO & DISABLED

9
Q

What is LOCK ESCALATION AUTO?

A

In a partitioned table, you set lock escalation to auto to cause locks to only escalation to the partition level.

10
Q

what is LOCK ESCALATION DISABLED?

A

No escalation can occur, which means the system will take many more row or page level locks which consumes more memory.

11
Q

How can you save the statistics IO of a query?

A

SET STATISTICS XML ON

12
Q

What is the difference between WITH RECOMPILE and WITH RECOMPILE AS for a stored procedure?

A

It forces the stored procedure to be recompiled for each execution, regardless if another developer uses WITH RECOMPILE or not. WITH RECOMPILE (no AS) is a one-off.