Skill 1.3 Implement functions and aggregate data

Question 1

What is the output of a ‘TRY_XXX’ conversion function when conversion fails?

Answer 1

NULL

Question 2

What does the function GETDATE do?

Answer 2

Returns the current date and time in the SQL instance you are querying

Question 3

What is the SQL standard equivalent of GETDATE?

Answer 3

CURRENT_TIMESTAMP

Question 4

What is the difference between GETDATE and SYSDATETIME?

Answer 4

SYSDATETIME returns a DATETIME2 value instead of a DATETIME value

Question 5

How are DATETIME and DATETIME2 values different?

Answer 5

DATETIME2 values have an accuracy of 100ns, DATETIME values are Rounded to increments of .000, .003, or .007 seconds

Question 6

What is the output of the SYSDATETIMEOFFSET function?

Answer 6

SYSDATETIMEOFFSET returns a DATETIMEOFFSET value, which is a DATETIME2 date value plus the offset in hours from UTC

Question 7

How do you get the date or time in T-SQL?

Answer 7

Cast the DATETIME, DATETIME2, or DATETIMEOFFSET value as DATE or TIME: CAST(GETDATE() as DATE), CAST(SYSDATETIME() as TIME)

Question 8

What do the GETUTCDATE and SYSUTCDATETIME functions do?

Answer 8

The GETUTCDATE and SYSUTCDATETIME functions return DATETIME and DATETIME2 values converted to UTC time

Question 9

What do you use the DATEPART function for?

Answer 9

The DATEPART function is used to extract part of a date (year, month, day, hour, minute, nanosecond) as an integer

Question 10

What does the DATENAME function do?

Answer 10

DATENAME returns a character string that represents the specified datepart of the specified date

Question 11

What functions does SQL provide for constructing DATETIME values from parts?

Answer 11

DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS

Question 12

What function gets you the end of month date for the given date and time value?

Answer 12

EOMONTH (It supports a second argument indicating how many months to add to the result)

Question 13

What is the DATEADD function used for?

Answer 13

DATEADD takes a date, number of units, and a date part, and gives you a second date with that number of units of that part added. Negative units allows for subtraction.

Question 14

What does the DATEDIFF function do?

Answer 14

DATEDIFF gives you the difference in units between two dates, expressed as an integer. Use DATEDIFF_BIG, if the difference doesn’t fit in a 4-byte integer.

Question 15

What does SWITCHOFFSET do?

Answer 15

SWITCHOFFSET returns an input DATETIMEOFFSET value adjusted to a requested target offset. You can use it to convert a datetime in the one timezone to a datetime in another timezone

Question 16

How is TODATETIMEOFFSET different from SWITCHOFFSET?

Answer 16

TODATETIMEOFFSET accepts a DATETIME value (not a DATETIMEOFFSET value) as input. You can use it to convert a datetime in the server’s current timezone into a datetime in another timezone

Question 17

How is the function AT TIME ZONE different from SWITCHOFFSET?

Answer 17

AT TIME ZONE expects a time zone name (‘Pacific Standard Time’) instead of an offset value (‘-07:00’)

Question 18

What two methods of concatenating strings does SQL Server support?

Answer 18

the plus (+) operator and the CONCAT function

Question 19

What is the output of a string concatenated using the plus operator when any one of the parts is NULL?

Answer 19

NULL

Question 20

What does CONCAT replace NULLs with?

Answer 20

an empty string

Question 21

What are the three arguments to the substring function?

Answer 21

string, starting position, length

Question 22

What are the two arguments to the LEFT and RIGHT functions

Answer 22

string, length

Question 23

What does the CHARINDEX function do?

Answer 23

CHARINDEX returns the position of the first occurrence of a given string within another string, beginning at a certain position

Question 24

How is PATINDEX different from CHARINDEX?

Answer 24

PATINDEX looks for a pattern (like the LIKE clause) in a string

Question 25

What are the two functions you can use to measure the length of a string?

Answer 25

LEN and DATALENGTH

Question 26

What is the difference between LEN and DATALENGTH?

Answer 26

LEN returns the number of characters in a string, minus any trailing spaces (NOT leading spaces), DATALENGTH returns the number of bytes in the input string (2 bytes per character for unicode strings)

Question 27

What does the REPLACE function do?

Answer 27

REPLACE takes an input string, and replaces all occurrences of one string with another string

Question 28

What does the REPLICATE function do?

Answer 28

REPLICATE takes an input string and replicates it a requested number of times

Question 29

What does the STUFF function do?

Answer 29

STUFF replaces a given number of characters at a given position in a string with another string

Question 30

What does the format FUNCTION do?

Answer 30

FORMAT lets you format an input value based on a .NET format string

Question 31

What does the STRING_SPLIT function do?

Answer 31

STRING_SPLIT takes an input string and a string to split it by and returns a list of strings

Question 32

What is the general format of the simple form of the CASE function?

Answer 32

case [columname]
when some_value then output_value
when some_value2 then output_value2
...
else some_default value
END as alias_name

Question 33

What is the general format of the searched form of the CASE function?

Answer 33

case
when [columname] = some_value then output_value
when [columname] = some_value2 then output_value2
…
else some_default value
END as alias_name

Question 34

What functions are considered abbreviates of the CASE function?

Answer 34

COALESCE, NULLIF (standard)

ISNULL, IIF, CHOOSE (nonstandard)

Question 35

What does the COALESCE function do?

Answer 35

COALESCE returns the first non-NULL value from a list of values

Question 36

What does the NULLIF function do?

Answer 36

NULLIF compares two input expressions and returns NULL if they are equal and the first input if they are not

Question 37

What does the IIF function do?

Answer 37

IIF returns one value if a predicate is true, and another value if a predicate is false or unknown

Question 38

What does the CHOOSE function do?

Answer 38

CHOOSE lets you give a position and a list of expressions, and returns the expression in the given position (NULL if there is no expression in that position)

Question 39

What do the @@ROWCOUNT and ROWCOUNT_BIG functions do?

Answer 39

@@ROWCOUNT and ROWCOUNT_BIG return the number of rows affected (inserted, updated, deleted) by the last SQL statement you executed

Question 40

What compression algorithm does the COMPRESS function use?

Answer 40

GZIP

Question 41

How do you turn a COMPRESSed string back into a string you can read?

Answer 41

CAST the string as the original type that was compressed (nvarchar(max), etc)

Question 42

T-SQL triggers are niladic. What does niladic mean?

Answer 42

niladic means that they don’t support parameters

Question 43

What is CONTEXT_INFO?

Answer 43

CONTEXT_INFO is a session-scoped binary string that can hold up to 128 bytes

Question 44

How do you set the value of CONTEXT_INFO

Answer 44

you use the command

SET CONTEXT-INFO [varbinary(128) value]

Question 45

How is SESSION_CONTEXT different from CONTEXT_INFO?

Answer 45

SESSION_CONTEXT stores a list of key-value pairs where the key is nvarchar(128) and the value is a SQL_VARIANT

Question 46

How do you add a value to SESSION_CONTEXT?

Answer 46

EXEC sys.sp_set_session_context
@key = N’keyname’,
@value = ‘value’,
@read_only = [0 or 1]

Question 47

How do you read a value from SESSION_CONTEXT?

Answer 47

SELECT SESSION_CONTEXT(N’keyname’) as [alias]