Question 1

OUTER APPLY

Accepted Answer

Operator returns all the rows from the left table expression irrespective of its match with the right table expression. Like a LEFT OUTER JOIN.

Question 2

CROSS APPLY

Accepted Answer

Operator returns only those rows from the left table expression (in its final output) if it matches with the right table expression. In other words, the right table expression returns rows for the left table expression match only.

Question 3

TRY CATCH blocks

Accepted Answer

Are not allowed within functions. Error handling needs to be placed in the code that calls the function instead.

Question 4

datetime to datetime2 conversion

Accepted Answer

Datetime must be in increments of 0.000, 0.003 or 0.007 in order to be converted to datetime2 without rounding.

Question 5

PIVOT

Accepted Answer

Converts rows to columns

Question 6

UNPIVOT

Accepted Answer

Converts columns to rows

Question 7

datetimeoffset()

Accepted Answer

Stores the date and time in UTC and a timezone offset. This allows both UTC and a local time to be stored in the same field.

Question 8

What kind of join can you use to invoke a table-valued function?

Accepted Answer

CROSS APPLY, OUTER APPLY

Question 9

serializable

Accepted Answer

Prevents other users from modifying the data until the transaction has completed.

Question 10

Windows identity of the user

Accepted Answer

ORIGINAL_LOGIN()

Question 11

T-SQL conversion functions

Accepted Answer

CAST and CONVERT

Question 12

CAST

Accepted Answer

FUNCTION (source_expression AS target_type)

Question 13

CONVERT

Accepted Answer

FUNCTION(target_type, source_expression [,style_number])

Question 14

TRY_CAST, TRY_CONVERT, TRY_PARSE

Accepted Answer

Return a NULL if the conversion is not valid instead of failing.

Question 15

DATEPART

Accepted Answer

FUNCTION(month,'20170212') = returns 2

Question 16

DATENAME

Accepted Answer

FUNCTION(month,'20170212') = returns 'February'

Question 17

DATEFROMPARTS

Accepted Answer

FUNCTION(2017,02,12) = returns '2017-02-12'

Question 18

EOMONTH

Accepted Answer

FUNCTION(SYSDATETIME()) = returns '2017-02-29'

Question 19

DATEADD

Accepted Answer

FUNCTION(year,1,'20170212') = returns '2018-02-12'

Question 20

DATEDIFF using day part

Accepted Answer

FUNCTION(day,'20160212','20170212') = returns 336

Question 21

DATEDIFF using year part

Accepted Answer

FUNCTION(year,'20161231','20170101') = returns 1

Question 22

SWITCHOFFSET

Accepted Answer

FUNCTION(SYSDATETIMEOFFSET(),'-08:00') = adjusts the input datetimeoffset value adjusted to a requested target offset (from the UTC time zone).

Question 23

TODATETIMEOFFSET

Accepted Answer

FUNCTION(mydatetime, theoffsetvalue)

Question 24

AT TIME ZONE

Accepted Answer

Function compensating for daylight savings time.  FUNCTION 'UTC'

Question 25

LEFT

Accepted Answer

FUNCTION('abcde',3) = returns 'abc'

Question 26

RIGHT

Accepted Answer

FUNCTION('abcde',3 = returns 'cde'

Question 27

CHARINDEX

Accepted Answer

FUNCTION(' ', 'Inigo Montoya') = returns 6

Question 28

PATINDEX

Accepted Answer

FUNCTION('%[0-9]%', 'abcd123efgh') = returns 5

Question 29

LEN

Accepted Answer

FUNCTION(N'xyz') = returns 3, removes trailing spaces.

Question 30

DATALENGTH

Accepted Answer

FUNCTION(N'xyz') = returns 6. Counts 2 bytes per character, and does not removing trailing spaces.

Question 31

REPLACE

Accepted Answer

FUNCTION('.1.2.3.', ',' , '/') = returns '/1/2/3/'

Question 32

REPLICATE

Accepted Answer

FUNCTION('0', '10') = returns '0000000000'

Question 33

COALESCE

Accepted Answer

Function that accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. The datatype of the returned input determines the datatype of the result.

Question 34

ISNULL

Accepted Answer

Like COALESCE, but with only 2 inputs. The datatype of the first input determines the datatype of the result.

Question 35

NULLIF

Accepted Answer

This function accepts 2 input expressions, returns NULL if they are equal, and returns the first input if they are not.

Question 36

IIF

Accepted Answer

FUNCTION(,,) = returns one value if an input predicate is true, and another value if it is false.

Question 37

@@ROWCOUNT

Accepted Answer

System function that returns the number of rows affected by the last statement that you executed.

Question 38

COMPRESS

Accepted Answer

System function that enables you to compress an input character or binary string using the GZIP algorithm into a result binary string.

Question 39

CONTEXT_INFO

Accepted Answer

System function that enables you to set a binary string of up to 128 bytes that is associated with your session. It can be read from anywhere in your session.

Question 40

NEWID()

Accepted Answer

Generates a UNIQUEIDENTIFIER typed value (GUID).

Question 41

Function Determinism

Accepted Answer

Characteristic that indicates whether the function is guaranteed to return the same result given the same set of input values (including an empty set) in different invocations.

Question 42

SCOPE_IDENTITY

Accepted Answer

Function that returns the last identity column that was inserted into an identity column.

Question 43

THROW

Accepted Answer

Statement takes 3 parameters: error number, message, and state. Error number must be between 50000 and 2147483647 for user defined errors. State must be between 0 and 255.

Question 44

FOR SYSTEM_TIME AS OF

Accepted Answer

Displays information that was valid at a specific point in time (temporal tables).

Question 45

CUBE

Accepted Answer

Cannot be used in a SELECT statement. Part of GROUP BY clause.

Question 46

RAISERROR

Accepted Answer

Cannot be used to report system errors. System errors have error numbers less than 50000.

Question 47

RAISERROR

Accepted Answer

Error reporting that always requires parameters.

Question 48

derived table

Accepted Answer

Can only be used within the current statement.

Question 49

table-valued user-defined function

Accepted Answer

Can be used anywhere a table or view can be used.

Question 50

WITH SCHEMABINDING

Accepted Answer

Required statement for indexed views.

Question 51

Used in the GROUP BY clause.

Accepted Answer

GROUPING SETS, CUBED, ROLLUP.

Question 52

GROUPING

Accepted Answer

FUNCTION(columnname) AS alias; accepts a single element as input and returns 0 when the element is part of the grouping set and 1 when it is not.

Question 53

GROUPING_ID

Accepted Answer

FUNCTION(column1, column2, column3) AS alias; returns an id based on which elements are part of the grouping set.

Question 54

ROW_NUMBER

Accepted Answer

FUNCTION() OVER (ORDER BY val) AS alias; function that computes unique incrementing integers starting with 1 within the window partition based on the window ordering (for entire result set).

Question 55

RANK

Accepted Answer

FUNCTION() OVER (ORDER BY val) AS alias; function that computes incrementing integers starting with 1 for the result set. Assigns the same ranking value to rows that share the same ordering value but continues ranking by skipping the unused rank numbers.

Question 56

DENSE_RANK

Accepted Answer

FUNCTION() OVER (ORDER BY val) AS alias; function that computes incrementing integers starting with 1 for the result set. Assigns the same ranking value to rows that share the same ordering value then continues with the next number in sequence.

Question 57

NTILE

Accepted Answer

FUNCTION(10) OVER (ORDER BY val) AS alias; function that separates result set into groups of result set/10 and assigns each group an incrementing integer as ID. If there is a remainder, an extra row is added to each group from the top of the set.

Question 58

Window Offset Functions

Accepted Answer

LAG, LEAD, FIRST_VALUE, LAST_VALUE

Question 59

LAG

Accepted Answer

FUNCTION(val) OVER(PARTITION BY custid ORDER BY date) AS prev_val; function that returns the value of the previous row (default offset is 1 if not specified).

Question 60

LAG

Accepted Answer

FUNCTION(val, 3, 0) OVER(PARTITION BY custid ORDER BY date) AS three_prev_val; function that returns the value of the row 3 previous, and if none exists for that offset, returns 0 instead of NULL.

Question 61

LEAD

Accepted Answer

FUNCTION(val) OVER(PARTITION BY custid ORDER BY date) AS next_val; function that returns the value of the next row (default offset is 1 if not specified).

Question 62

LEAD

Accepted Answer

FUNCTION(val, 2, 0) OVER(PARTITION BY custid ORDER BY date) AS two_next_val; function that returns the value of the next next row, or if none exists for that offset, returns 0 instead of NULL.

Question 63

FIRST_VALUE

Accepted Answer

FUNCTION(val) OVER(PARTITION BY custid ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as val; returns the first value in the specified partition.

Question 64

LAST_VALUE

Accepted Answer

FUNCTION(val) OVER(PARTITION BY custid ORDER BY orderdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as val; returns the last value in the specified partition.

"Know" box contains:
Time elapsed:
Retries:

SQL

Querying T-SQL