This function will change a varchar to a datetime2(0)
What is CAST or CONVERT?
100
Add one of these to see the rows that are flowing through an SSIS pipeline.
What is a Data Viewer or Data Tap
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.
Use BACKUP DATABASE WITH CHECKSUM, CONTINUE_AFTER_ERROR
Use RESTORE DATABASE WITH CHECKSUM, CONTINUE_AFTER_ERROR
Frequent, actual database restore testing
Create a SQL Agent job to monitor the MSDB.dbo.backupset table
Store the backups on a RAID 10 diskset
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
This transform will help validate your data by comparing what you have with a set of accurate, cleansed values and indicate how close of a match it was able to find.
What is the Fuzzy Lookup Transformation
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?
The new database is the same as the backed up database was at 8:00 PM
The new database is the same as the backed up database was at 8:22 PM
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.
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.
The trains will never meet since they are on different tracks.
What is:
B. 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
November - Greg McMurray
What is JSON
November - Greg McMurray, "JavaScript Object Notation (JSON)"
300
The output of this statement:
SELECT Friday = NULLIF(IIF(1=0,'Tuesday', 'Wednesday'), 'Thursday')
What is Wednesday
300
If your package fails, you may be able to restart it from the point of failure if you configured this.
What are Checkpoints (or FailPackageOnFailure)
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?
Unplug the server to prevent further corruption
Execute a tail log backup
Execute a full database backup
Restore the most recent full backup and any differential/log backups that are available
Execute DBCC CHECKDB(0, REPAIR_ALLOW_DATA_LOSS)
Check to see if your resume on Monster is current
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
What is the name of the programming language used in Microsoft Power Query?
May - Steve Seeley, "BI Power Hour"
What is M
400
The function that when placed in the blank below returns the number of bytes used by @PracticeName
__________________(@PracticeName)
What is DATALENGTH
400
This source adapter, used to gather only rows that have changed in the OLTP database, does not have a corresponding destination adapter (how lonely!)
What is the CDC Source
400
If you plan to backup your database across the network, which account(s) need rights to the network location?
The user executing the backup
The account the SQL Agent Service is running under
The account the SQL Server Service is running under
The user account specified as the Proxy Account in the SQL Agent setup
Both the user executing the backup (which may be the SQL Agent Service) and the account the SQL Server Service is running under
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 XML document specifies the structure, format, and valid datatypes that corresponding XML documents should follow.
October - Aaron Cutshall, "XML Extreme"
What is an XML Schema Definition (XSD)
500
What function will allow you to format a datetime using .NET format strings and cultures?
What is FORMAT
500
Daily Double!
The Merge and Merge Join transformations require their input data to be in a particular format. What transform should you place immediately prior to these transformations to ensure they execute properly?
500
Which of these are types of backups that SQL Server can perform on a database?
Full Backup
Differential Backup
Log Backup
Partial Backup
Filegroup Backup
File Backup
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?