Extra notes 3 Flashcards
How can you use a symmetric key and certificate to protect a column?
OPEN SYMMETRIC KEY KeyName
DECRYPTION BY CERTIFICATE CertificateName
UPDATE TableName
SET Column = EncryptByKey(Key_GUID(‘KeyName’), @VarWithData, 1, HashBytes(‘SHA1’, CONVERT(varbinary, ColumnName)))
Can a SCALAR User Defined Function support using a table variable?
Yes
What monitoring application would you use to view I/O subsystem performance issues (highest stall times)?
Activity Monitor
What can you suspect when locks are taken in a database while using READ_COMMITTED_SNAPSHOT?
TempDB has filled up and when that happens locks are taken on the database level
How do you insert a connection string into a query?
Select Col1, Col2
FROM Table OPENDATASOURCE(ConnectionString)
Which System View shows All Unused Indexes?
sys.dm_db_index_usage_stats
Which System View shows all indexes that must be fragmented?
sys.dm_db_index_physical_stats
Which System View shows all indexes that have page splits?
sys.dm_db_index_operational_stats
At what point do statistics get generated automatically?
- When you populate an empty table;
- When a table with fewer than 500 rows doubles in size;
- When a table with more than 500 rows grows by 500 rows plus 20% of the current row count
What tool do you use to analyze a deadlock graph?
SSMS
What two types of data compression are available for rowstore tables and indexes?
Row and Page
How does SQL Server produce the result set for a non-indexed view?
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.
What correctly defines indexed columns and included columns?
Indexed columns form the key of the index, and included columns are non-key columns used to improve query coverage
Why is NEWID a nondeterministic function?
NEWID returns a unique identifier and a different result.
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?
The application uses delayed durable transactions and the changes have not yet replicated to the secondary replicas.
How might you determine an I/O related memory problem in a SQL Server instance?
Use the Dynamic Management View (DMV) sys.dm_os_wait_stats and locate excessive PAGEIOLATCH wait types.
How can you use a foreign key with a calculated column?
You have to use the PERSISTED keyword when creating the column.
Can you alter a calculated column?
No, in order to change a calculated column you have to drop it and recreate it.
What kind of user can you grant access to but that cannot be used for authentication?
A database user without a login.
Where should you use a database user without a login?
When you need to authenticate applications at the database level without hardcoding passwords.
You have a table trigger that makes sure that data fits within given rules, how can you remove it?
Use constraints, either Foreign Key or Check.
What can you use to replace of a trigger that keeps track of changes?
Auditing by using OUTPUT INTO a table from an insert statement:
INSERT INTO table OUTPUT inserted.val1, inserted.val2 into OutputAuditTable values (val1Value, val2Value)
What is cardinality?
Number of rows in a query result
What are statistics used for?
Used by query optimizer to Create query plans that improve query performance.
What are the required steps for creating an indexed view?
- Verify the SET options are correct for all existing tables that will be referenced in the view.
- Verify that the SET options for the session are set correctly before you create any tables and the view.
- Verify that the view definition is deterministic.
- Create the view by using the WITH SCHEMABINDING option.
- Create the unique clustered index on the view.
