SQL Jeopardy

Datatypes

100

Use this datatype when you want to be able to store any kind of data, regardless of type.

sql_variant

200

This datatype will hold geometric shapes based on a round-earth coordinate system.

Geography

300

As of December 5, 2018, the national debt was approximately $21,829,887,333,499 (21 trillion dollars).  Name two datatypes that can accurately and precisely store this value.

Money, Bigint, or Numeric/Decimal

400

In the query below, what datatype is @Node?

SELECT @Node.GetAncestor(2)

HierarchyID

500

Daily Double!

In the query below, what datatype is @OutputVariable?  

SELECT @OutputVariable = SWITCHOFFSET(@Variable1, @Variable2)

Recovery

100

The two recovery models that allow (and require) regular log backups.

Full and Bulk Logged

200

You’ve created a new database using the full recovery model.  What must you do before you take your first log backup?

Create a full backup

300

This type of backup captures any changes since the last full backup.

Differential backup

400

The database log file is divided into multiple smaller chucks or sub-components called:

Virtual Log Files (VLFs)

500

The process that occurs when the SQL Server Service starts.  During this time, committed but unwritten transactions are rolled forward and uncommitted written transactions are rolled back.

Recovery

Errors

100

The query below never returns any rows.  How can you fix it?

SELECT *

FROM Sales.SalesOrderHeader

WHERE CurrencyRateID = NULL

Replace the = with IS

WHERE CurrencyRateID IS NULL

200

Which of the following is the proper way to send a custom error back to the client?

1. RAISERROR ('I am afraid I can''t do that Dave.', 16, 1)
2. RETURN ('I am afraid I can''t do that Dave.', 16, 1)
3. THROW ('I am afraid I can''t do that Dave.', 16, 1)
4. RAISE ('I am afraid I can''t do that Dave.', 16, 1)

A.  RAISERROR ('I am afraid I can''t do that Dave.', 16, 1)

300

Given the Patient Table outlined in figure 1, which of the following insert will succeed (choose all that apply)?

1. INSERT INTO #Patient
   VALUES (1, 'Chad', 'Crawford', 37)
2. INSERT INTO #Patient (PatientID, Fname, Lname, Age, BD)
   VALUES (1, 'Chad', REPLICATE('Crawford', 8), 37, '8/23/1974')
3. INSERT INTO #Patient (PatientID, Fname, Lname, Age, BD)
   VALUES (1, NULL, 'Crawford', 37, '8/23/1974')
4. INSERT INTO #Patient (PatientID, Fname, Lname, Age, BD)
   VALUES (1, 'Chad', 'Crawford', 37, NULL)

C:
INSERT INTO #Patient (PatientID, Fname, Lname, Age, BD)
VALUES (1, NULL, 'Crawford', 37, '8/23/1974')

400

Errors at this severity or above will not only terminate the batch, it will also disconnect the client’s session to SQL Server.

20

500

Assuming the table and column names exist, which of the following queries will throw an error?

1. SELECT *
   FROM Employee
   MYSQL JOIN PurchaseOrderHeader ON BusinessEntityID = EmployeeID
2. SELECT *
   FROM Employee, PurchaseOrderHeader
   WHERE Employee.BusinessEntityID = PurchaseOrderHeader.EmployeeID
3. SELECT *
   FROM Employee
   LEFT JOIN PurchaseOrderHeader ON Employee.BusinessEntityID = PurchaseOrderHeader.EmployeeID
   RIGHT JOIN PurchaseOrderDetail ON PurchaseOrderHeader.PurchaseOrderID = PurchaseOrderDetail.PurchaseOrderID
4. SELECT *
   FROM Employee
   CROSS JOIN PurchaseOrderHeader ON Employee.BusinessEntityID = PurchaseOrderHeader.EmployeeID

D:

SELECT *

FROM Employee

CROSS JOIN PurchaseOrderHeader ON Employee.BusinessEntityID = PurchaseOrderHeader.EmployeeID

Database Objects

100

This object is defined as “a column or combination of columns that contain values that uniquely identify each row in the table"

Primary Key

200

The object highlighted in the script below:

  WITH SalesData AS (

       SELECT SalesOrderHeader.SalesOrderID

              , COUNT(*) AS OrderItemCount

       FROM Sales.SalesOrderHeader

       INNER JOIN Sales.SalesOrderDetail

       ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID

       GROUP BY SalesOrderHeader.SalesOrderID

  )

  SELECT *

  FROM SalesData

CTE

300

Although this object is defined by Books Online as “A virtual table whose contents are defined by a query…. [this object] does not exist as a stored set of data values in a database.”, it can be indexed.

View

400

This object “is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the [object] was created…. Unlike identity columns, [these objects] are not associated with tables.

Sequence

500

This object is the only one with access to the EVENTDATA() function

DDL Trigger

Performance Monitoring

100

A high signal wait percentage indicates a bottle neck on this resource.

CPU

200

This counter measures the estimated number of seconds a data page will stay in memory before being flushed back to disk.

Page Life Expectancy (PLE)

300

This wait type indicates parallelism is being used.

CXPacket

400

The Dynamic Management Function that returns information about index fragmentation.

sys.dm_db_index_physical_stats

500

The RESOURCE_SEMAPHORE_QUERY_COMPILE wait type indicates a bottleneck on this resource.

Memory