This function will change a varchar to a datetime2(0)
What is CAST or CONVERT?
100
This special table only exists within a trigger and contains all the new data created by the statement
What is INSERTED
100
True or False:
The Log file size is always equal to or less than the database file size.
What is FALSE
100
A CTE that references itself an arbitrary number of times
What is a Recursive CTE
100
The window function that returns the rank of a row within a group, without any numeric gaps
What is DENSE_RANK
200
This function can return the number of days between two datetime2(4) variables
What is DATEDIFF
200
This type of trigger executes before the INSERT, UPDATE or DELETE happens
What is INSTEAD OF
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
The word that starts a CTE
What is WITH
200
The operator that returns a row for all permutations of the columns listed in the GROUP BY
What is CUBE
300
The output of this statement:
SELECT Friday = NULLIF(IIF(1=0,'Tuesday', 'Wednesday'), 'Thursday')
What is Wednesday
300
This type of trigger is capable of capturing schema changes
What is a DDL Trigger
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
The clause (among SELECT, FROM, WHERE, GROUP BY, etc.) where you use the PIVOT operator.
What is FROM
300
The window function that returns a value from the next row in sequence
What is LEAD
400
The function that when placed in the blank below returns the number of bytes used by @PracticeName
__________________(@PracticeName)
What is DATALENGTH
400
The two commands that can follow a WHEN MATCHED clause of the MERGE statement
What is UPDATE and DELETE
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
The operator that creates a new column for values held in a set of rows.
What is PIVOT
400
The window function that splits a query result into a specified number of equally sized sub groups and returns a numeric value indicating which group each row is in
What is NTILE
500
This function, new in SQL 2012, allows datetimes to be formatted using .NET format strings and cultures
What is FORMAT
500
Daily Double
The condition, exhibited by Bobby Tables, can occur when user input is used to dynamically create a SQL Query
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
The Performance of a non-recursive CTE is most likely to be similar to the performance of this database object
What is VIEW
500
The function that indicates if a row is aggregated on a specified column