Chapter 3 Filtering and Sorting Data Flashcards
What are the 3 query clauses that enable you to filter data based on predicates?
ON, WHERE, and HAVING.
What is two-valued logic?
When NULLs are not possible in the data, the predicate can evaluate to true or false.
What is three-valued logic?
When NULLs are possible in the data, the predicate can evaluate to true, false, or unknown. You get an unknown when at least one operand is NULL.
Are two NULLs considered equal, e.g. NULL = NULL?
No. NULL = NULL is unknown - not true.
What is the purpose of IS NULL and IS NOT NULL?
IS NULL returns true when the tested operand is NULL. IS NOT NULL returns true when the tested operand is not NULL.
What is SARG (Search Argument)?
Search Argument describes a form that a predicate needs to be in for SQL Server to use indexes efficiently without requiring a full scan of the table. Predicates such as col1 = 10 and col1 > 10 are search argument form. Basically, applying manipulation to the filtered column prevents the predicate from being search argument, e.g. F(col1) = 10, WHERE COALESCE(shippeddate, ‘19000101’) = COALESCE(@dt, ‘19000101’), col1 -1 <= @n + 1.
What is the correct way to handle NULL inputs in a predicate?
Don’t use ISNULL or COALESCE because this breaks the search argument form; instead, use something like WHERE region <> ‘WA’ OR region IS NULL;
What is the order of precedence for logical operators?
Parenthesis then NOT then AND then OR, e.g. WHERE col1 = ‘w’ AND col2 = ‘x’ OR col3 = ‘y’ AND col4 = ‘z’ evaluates to (col1=’w’ AND col2=’x’) OR (col3=’y’ AND col4=’y’)
In what order are the expressions in a WHERE clause evaluated?
Due to the all at once concept in the language, the expression will not be evaluated in left to right order. SQL Server could decide for cost related reasons, to start with the right expression, e.g. WHERE propertyType = ‘INT’ AND CAST(propertyValue AS INT) > 10. To make this query work, you can use TRY_CAST instead.
Can implicit conversion in the WHERE clause hurt performance?
Yes. For example, when comparing against literals, be sure to distinguish between unicode and non-unicode correctly, e.g. WHERE lastName = N’Davis’
What is the LIKE function?
Used to filter character string data (regular and unicode) based on pattern matching, e.g. LIKE . Supports: % = any string including empty string, _ single character, [ABC] single character from a set, [A-C] single character from a range, [^ABC] single character not in the list or range.
What is the ESCAPE keyword used for in the context of LIKE?
Used to help search for a character that is considered a wildcard. You must designate your escape character with the ESCAPE keyword, e.g. col1 LIKE ‘!_%’ ESCAPE ‘!’ searches for strings that start with an underscore using the ! as the escape character.
What are some of the performance concerns with LIKE?
When LIKE is used with a known prefix (col LIKE ‘ABC%’, SQL Server can efficiently use an index on the filtered column. When the pattern starts with a wildcard (col LIKE ‘%ABC%’), SQL cannot rely on index ordering any more.
How does SQL Server interpret a date literal when used in a where clause such as WHERE orderdate=’02/12/07’?
Each logon has a default language associated with it and the default language sets various session options on the logon’s behalf, including one called DATEFORMAT. A logon with us_english will have a DATEFORMAT set to mdy, British to dmy, and Japenese to ymd.
What is the language neutral date format?
ymd, e.g. 20070212. is language neutral for all date and time data types.
What are the performance benefits of the WHERE clause?
(1) You reduce network traffic by filtering in the database server instead of the client, (2) potentially use indexes to avoid full scans of the tables involved thus minimizing I/O.
What is the form of a filter predicate that can rely on index ordering?
A search argument.