Skill 1.3 Implement functions and aggregate data Flashcards
What is the output of a ‘TRY_XXX’ conversion function when conversion fails?
NULL
What does the function GETDATE do?
Returns the current date and time in the SQL instance you are querying
What is the SQL standard equivalent of GETDATE?
CURRENT_TIMESTAMP
What is the difference between GETDATE and SYSDATETIME?
SYSDATETIME returns a DATETIME2 value instead of a DATETIME value
How are DATETIME and DATETIME2 values different?
DATETIME2 values have an accuracy of 100ns, DATETIME values are Rounded to increments of .000, .003, or .007 seconds
What is the output of the SYSDATETIMEOFFSET function?
SYSDATETIMEOFFSET returns a DATETIMEOFFSET value, which is a DATETIME2 date value plus the offset in hours from UTC
How do you get the date or time in T-SQL?
Cast the DATETIME, DATETIME2, or DATETIMEOFFSET value as DATE or TIME: CAST(GETDATE() as DATE), CAST(SYSDATETIME() as TIME)
What do the GETUTCDATE and SYSUTCDATETIME functions do?
The GETUTCDATE and SYSUTCDATETIME functions return DATETIME and DATETIME2 values converted to UTC time
What do you use the DATEPART function for?
The DATEPART function is used to extract part of a date (year, month, day, hour, minute, nanosecond) as an integer
What does the DATENAME function do?
DATENAME returns a character string that represents the specified datepart of the specified date
What functions does SQL provide for constructing DATETIME values from parts?
DATEFROMPARTS DATETIME2FROMPARTS DATETIMEFROMPARTS DATETIMEOFFSETFROMPARTS SMALLDATETIMEFROMPARTS TIMEFROMPARTS
What function gets you the end of month date for the given date and time value?
EOMONTH (It supports a second argument indicating how many months to add to the result)
What is the DATEADD function used for?
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.
What does the DATEDIFF function do?
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.
What does SWITCHOFFSET do?
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
How is TODATETIMEOFFSET different from SWITCHOFFSET?
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
How is the function AT TIME ZONE different from SWITCHOFFSET?
AT TIME ZONE expects a time zone name (‘Pacific Standard Time’) instead of an offset value (‘-07:00’)
What two methods of concatenating strings does SQL Server support?
the plus (+) operator and the CONCAT function
What is the output of a string concatenated using the plus operator when any one of the parts is NULL?
NULL
What does CONCAT replace NULLs with?
an empty string
What are the three arguments to the substring function?
string, starting position, length
What are the two arguments to the LEFT and RIGHT functions
string, length
What does the CHARINDEX function do?
CHARINDEX returns the position of the first occurrence of a given string within another string, beginning at a certain position
How is PATINDEX different from CHARINDEX?
PATINDEX looks for a pattern (like the LIKE clause) in a string
What are the two functions you can use to measure the length of a string?
LEN and DATALENGTH
What is the difference between LEN and DATALENGTH?
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)
What does the REPLACE function do?
REPLACE takes an input string, and replaces all occurrences of one string with another string
What does the REPLICATE function do?
REPLICATE takes an input string and replicates it a requested number of times
What does the STUFF function do?
STUFF replaces a given number of characters at a given position in a string with another string
What does the format FUNCTION do?
FORMAT lets you format an input value based on a .NET format string
What does the STRING_SPLIT function do?
STRING_SPLIT takes an input string and a string to split it by and returns a list of strings
What is the general format of the simple form of the CASE function?
case [columname] when some_value then output_value when some_value2 then output_value2 ... else some_default value END as alias_name
What is the general format of the searched form of the CASE function?
case
when [columname] = some_value then output_value
when [columname] = some_value2 then output_value2
…
else some_default value
END as alias_name
What functions are considered abbreviates of the CASE function?
COALESCE, NULLIF (standard)
ISNULL, IIF, CHOOSE (nonstandard)
What does the COALESCE function do?
COALESCE returns the first non-NULL value from a list of values
What does the NULLIF function do?
NULLIF compares two input expressions and returns NULL if they are equal and the first input if they are not
What does the IIF function do?
IIF returns one value if a predicate is true, and another value if a predicate is false or unknown
What does the CHOOSE function do?
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)
What do the @@ROWCOUNT and ROWCOUNT_BIG functions do?
@@ROWCOUNT and ROWCOUNT_BIG return the number of rows affected (inserted, updated, deleted) by the last SQL statement you executed
What compression algorithm does the COMPRESS function use?
GZIP
How do you turn a COMPRESSed string back into a string you can read?
CAST the string as the original type that was compressed (nvarchar(max), etc)
T-SQL triggers are niladic. What does niladic mean?
niladic means that they don’t support parameters
What is CONTEXT_INFO?
CONTEXT_INFO is a session-scoped binary string that can hold up to 128 bytes
How do you set the value of CONTEXT_INFO
you use the command
SET CONTEXT-INFO [varbinary(128) value]
How is SESSION_CONTEXT different from CONTEXT_INFO?
SESSION_CONTEXT stores a list of key-value pairs where the key is nvarchar(128) and the value is a SQL_VARIANT
How do you add a value to SESSION_CONTEXT?
EXEC sys.sp_set_session_context
@key = N’keyname’,
@value = ‘value’,
@read_only = [0 or 1]
How do you read a value from SESSION_CONTEXT?
SELECT SESSION_CONTEXT(N’keyname’) as [alias]
