Functional Questions
Merging Trigger Batches
UNDO! UNDO!
Pivot/CTE
Windows Groupies
100
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?
  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:
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
The example below is of this type of query:
                            SELECT PatientID
                                   , FirstName
                                   , LastName
                                   , (SELECT MAX(AppointmentDate)
                                      FROM Appointment
                                      WHERE Appointment.PatientID = Patient.PatientID)
                            FROM Patient
                            WHERE PracticeID = 43
What is CORRELATED SUBQUERY
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?
What is CTE, VIEW, TEMPORARY TABLE, TABLE VARIABLE
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
The word that needs to be added to the query below to get it to run without throwing an error:
                            SELECT SalesOrderID
                                   , (SELECT Name
                                      FROM Sales.SalesTerritory
                                      WHERE TerritoryID = TerritoryID)
                                   AS TerritoryName
                            FROM Sales.SalesOrderHeader
What is SalesOrderHeader.
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
What is GROUPING
M
e
n
u