Functional Questions
Straightjackets
UNDO! UNDO!
OOH! Shiny!
Other
100
This function will change a varchar to a datetime2(0)
What is CAST or CONVERT?
100
The type of constraint that provides a value if none is given when a row is inserted
What is a Default Constraint
100
True or False:
The Log file size is always equal to or less than the database file size.
What is FALSE
100
This new technology allows you to transparently keep part of your database on premise and part of it in the Azure cloud
What is Stretch Database
100
This is the best way to know our backups will restore after a disaster.
  1. Use BACKUP DATABASE WITH CHECKSUM, CONTINUE_AFTER_ERROR
  2. Use RESTORE DATABASE WITH CHECKSUM, CONTINUE_AFTER_ERROR
  3. Frequent, actual database restore testing
  4. Create a SQL Agent job to monitor the MSDB.dbo.backupset table
  5. Store the backups on a RAID 10 diskset
  6. Use a combination of FULL, DIFFERENTIAL and LOG backups at regular intervals


April - Robert Every, "Database Restore Testing"
What is:
C. Frequent, actual database restore testing
200
This function can return the number of days between two datetime2(4) variables
What is DATEDIFF
200

The type of constraint that would allow you to limit an int column to the values 1-100

What is a Check Constraint

200

A full backup starts at 8:00 PM and finishes at 8:22 PM. You restore the full backup on another server. What point in time is the new database consistent to?

  1. The new database is the same as the backed up database was at 8:00 PM
  2. The new database is the same as the backed up database was at 8:22 PM
  3. The backup process writes objects sequentially to the .bak file, so the new database contains the objects as they were when the backup process got to that particular object.
  4. The new database is consistent to the point in time between 8:00 PM and 8:22 PM where the backup read finished and the backup write began. To determine the synchronization point, add a continually updating timestamp in the database so you can query and determine the last modified time on the restored database.
  5. The trains will never meet since they are on different tracks.

What is:

2. The new database is the same as the backed up database was at 8:22 PM

200
These tables now allow foreign keys and check constraints
What are In-Memory tables
200

Objects in this data interchange language are comprised of name-value pairs

What is JSON

300
The output of this statement: SELECT Friday = NULLIF(IIF(1=0,'Tuesday', 'Wednesday'), 'Thursday')
What is Wednesday
300
The type of constraint you would use if you wanted to ensure that no two patients had the same chart number
What is a Unique Constraint
300
You encounter a major system failure which indicates unrecoverable disk errors in your primary .mdf database file. What is the first thing you should do?
  1. Unplug the server to prevent further corruption
  2. Execute a tail log backup
  3. Execute a full database backup
  4. Restore the most recent full backup and any differential/log backups that are available
  5. Execute DBCC CHECKDB(0, REPAIR_ALLOW_DATA_LOSS)
  6. Check to see if your resume on Monster is current
  7. Run in small circles, flapping your arms and whimpering softly
What Is:
B. Execute a tail log backup
300
This new feature will allow DBAs to easily force an old query plan to be used if a new plan has performance issues
What is Query Store
300

In SQL Server, these may access memory nodes to allocate memory. Each component that uses significant memory must create one - frequently at server startup.

Memory Clerks

400
The function that when placed in the blank below returns the number of bytes used by @PracticeName
__________________(@PracticeName)
What is DATALENGTH
400
This type of object can restrict updates and deletes based on both the old values and the new values in the row (e.g. don't allow a row to be deleted if the status is "done").
What is a Trigger
400
If you plan to backup your database across the network, which account(s) need rights to the network location?
  1. The user executing the backup
  2. The account the SQL Agent Service is running under
  3. The account the SQL Server Service is running under
  4. The user account specified as the Proxy Account in the SQL Agent setup
  5. Both the user executing the backup (which may be the SQL Agent Service) and the account the SQL Server Service is running under
  6. Account from Sesame Street (1 backup, ha ha ha!)
What is:
C. The account the SQL Server Service is running under
400
SQL Server now imports and exports data in this popular data interchange format, with functions similar to existing support for XML
What is JSON
400

This feature in SQL Server allows admins to limit resource usage - on all queries, or based on specified criteria.

Resource Governor

500
The function that formats a datetime using .NET format strings and cultures
What is FORMAT
500
Daily Double!
Use this keyword when creating a view to prevent schema changes to the underlying table that would invalidate the view.
500
Which of these are types of backups that SQL Server can perform on a database?
  1. Full Backup
  2. Differential Backup
  3. Log Backup
  4. Partial Backup
  5. Filegroup Backup
  6. File Backup
  7. Sink Backed up
What Is:
A. Full Backup
B. Differential Backup
C. Log Backup

E. Filegroup Backup
F. File Backup
500
This backup methodology now supports databases that are in simple recovery mode
What is Managed backups to Windows Azure
500

What is the last clause (when present) to be processed in a SQL Statement?

What is TOP

M
e
n
u