Extra Notes 2 Flashcards
What is necessary to be able to create a foreign key?
A primary key or a Unique key on the referenced column.
What are the steps (in order) to create a table that is partitioned?
Create database file partitions and files
Create Partition Function
Create Partition Scheme
Create the table by using partition scheme
What datatype is generated with encryption?
varbinary
Can a clustered index include a filter?
No
What is an application role?
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
What are the steps to create certificate and encryption?
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
How can you minimize the number of page splits on a table?
Set FILLFACTOR=85
What are the different LOCK ESCALATIONS you can use?
AUTO & DISABLED
What is LOCK ESCALATION AUTO?
In a partitioned table, you set lock escalation to auto to cause locks to only escalation to the partition level.
what is LOCK ESCALATION DISABLED?
No escalation can occur, which means the system will take many more row or page level locks which consumes more memory.
How can you save the statistics IO of a query?
SET STATISTICS XML ON
What is the difference between WITH RECOMPILE and WITH RECOMPILE AS for a stored procedure?
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.
