Ch.2 Case Expressions and Related Functions

What are the differences between COALESCE and ISNULL?

1.) COALESCE can have many inputs. ISNULL can have 2. 2.) The returned data type for COALESCE is determined by the expression returned.The returned data type for ISNULL is determined by the first expressions datatype. 3.) When using the SELECT INTO if the source attribute is NOT NULL than both COALESCE and ISNULL will return an attribute that is NOT NULL. However, if the source attribute allows NULLS. ISNULL will create an attribute that is still NOT NULL. COALESCE will match the source attribute.

Why doesn't the WHEN clause need to check explicitly for whether the value is $20 or more? What does this mean about CASE expressions? What is the purpose of the Else clause in a CASE expression?

Because if the first WHEN evaluates to false it is implied the value did not pass the first check. They evaluate in the order they are written. First expression evaluates to TRUE or FALSE and moves to the next. The Else clause can function as a catch all in case a result is returned that is not in the specified ranges.

Should you use COALESCE or ISNULL? Why? Are their exceptions?

COALESCE is the Standard and more flexible. Use ISNULL whenever performance is an issue with COALESCE and the limitation of inputs is not a problem.

What are two Standard SQL functions that act like the CASE expression? What are their syntax? What do they do they return? Which type of CASE expression are they similar to? Any special considerations for the expressions in the functions?

COALESCE(<Expression1>, <Expression2>, <ExpressionN...>) Coalesce goes through a list of expressions and evaluates them in order. The first to evaluate to NOT NULL is returned. If all evaluate to NULL, NULL is returned. This is similar to a Simple Case expression. Expressions can be of any data type. NULLIF(<expression1>, <expression2>) Nullif compares the first expression to the second. If they are equal a NULL is returned. If they are not expression1 is returned. This is similar to a Searched Case expression. The expressions must be of the same datatype.

What does the Simple CASE expression do?

Compares an expression to multiple scalar when expressions and returns the first matched True expression as a result. The comparison is always an =.

What two functions that exist to assist with migrations from Access? Are they standard?

IIF and CHOOSE. They are T-SQL specific.

What is Non-Standard T-SQL function that acts like COALESCE? What is it's syntax? What does it return?

ISNULL(<expression1>, <expression2>) ISNULL is like Coalesce but only supports two inputs. So similar to a simple CASE expression. It returns the first value that is not NULL, or a NULL if both expressions are NULL.

What is the difference between NEWID and NEWSEQUENTIALID?

NEWID is random order. NEWSEQUENTIALID creates keys in sequential order.

Would you use a FLOAT data type to represent a product unit price?

No. Float is not accurate.

Is CASE a statement? Why or why not?

No. Statements perform an action or control flow of code. Case is an expression as it returns a value.

Looking at the Production.Products table. Return the following productid, productname, unitprice, and using the searched CASE expression evaulate each products priced based on the following criteria. Call the column result 'Price Range' less than 20.00 is 'Low' less than 40.00 is 'Medium' Greater than or equal to 40.00 is 'High'.

SELECT productid, productname, unitprice, CASE WHEN unitprice < 20.00 THEN 'Low' WHEN unitprice < 40.00 THEN 'Medium' WHEN unitprice >= 40.00 THEN 'High' ELSE 'Unknown' END AS 'Price Range' From Production.Products;

Looking at the Production.Products table. Return the following productid, productname, unitprice, and discontinued. Discontinued has two possible values, 0 and 1. Use a Simple case expression to look at the 0 or 1 value in discontinued column. If 0 then return No. If 1 then return Yes. Otherwise, return unknown. Name the column Discontinued Status.

SELECT productid, productname, unitprice, CASE discontinued WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'Unknown' END AS 'Discontinued Status' FROM Production.Products;

Which function returns the current date and time value as DATETIME2 data type?

SYSDATETIME

What does the Searched CASE expression do?

Searched can evaluate a WHEN clause using a predicate expression that look at a range. Compare a WHEN with <, >, <=, >=.

What are the two forms of CASE expressions?

Simple and Searched.

When concatenating character strings, what is the difference between the plus (+) operator and the CONCAT function?

The + operator will return NULL for the entire string if one value is NULL. Concat will return an empty string for any part of the string that returns a NULL.

What happens when two columns are compared in a JOIN and they are null? Often users will use COALESCE and ISNULL to treat NULLS as equals. What is the issue with using COALESCE and ISNULL to change the value of NULLS to another value for the sake of comparing? What should be used instead when comparing T1.col1 to T2.col2 and wanting to return NULL as equals. This is only when both columns being compared are NULLS.

They are filtered out from the result set. Index inefficiency. This is because the attribute is being manipulated and will not use the index. T1.col1=T2.col2 OR (t1.col1 IS NULL AND t2.col2 IS NULL) Using the IS NULL operator makes SQL Server use indexes and understands the comparison of NULLS as equal.

What is a Best Practice when using the COALESCE function that takes into account all of the arguments being NULL?

Use COALESCE to return a value other than NULL. Often used in the last expression by giving an explicit value.

After listing all the WHEN THEN clauses what is Best Practice to wrap up the CASE Expression with? Why? What happens if this is not explicitly stated?

Use an ELSE clause that returns a result if none of the WHEN THEN statements evaluate to true. This functions as a catch all. If no ELSE clause is used then SQL Server defaults to ELSE NULL.