Flashcards in Extra Notes 2 Deck (12)
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?
Can a clustered index include a filter?
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?
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