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.
Assuming that no ORDER BY is specified, does a given query have a guaranteed order?
No. A table in T-SQL is supposed to represent a relation; a relation is a set, and a set has no order to it’s elements. Unless, you explicitly instruct the query otherwise, the results have no guaranteed order. The ordering is said to be arbitrary - it’s optimization dependent..
What is the only way to truly guarantee that the rows are returned from a query in a certain order?
Add an ORDER BY clause.
If you don’t indicate a direction (ASC, DESC) with the ORDER BY clause, which is the default?
ASC
Assuming that an ORDER BY is specified, but that the ordered column is not unique, do the duplicate rows have a guaranteed order?
No. The ordering of the rows with the same value is not guaranteed. You can specify additional expressions in the ORDER BY list to serve as a tie-breaker.
Can you sort by the ordinal position of columns, e.g. ORDER BY 4, 1? Why is this a bad practice?
Yes, but it is considered a bad practice because (1) It’s non-relational (2) If the SELECT list ever changes, you must remember to change the ordinal indices (otherwise, they may not sync up)
Can you sort by columns that you’re not returning?
Yes, but it depends. The rules is, you can order the results by elements that are not part of the SELECT list, as long as the result rows would have normally been allowed there. However, if SELECT DISTINCT is used, the source and result rows don’t map up one to one and you can only sort by elements in the SELECT list.
Are column aliases assigned in the SELECT clause visible to the ORDER BY clause?
Yes. ORDER BY gets evaluated conceptually after SELECT - unlike most other clauses. As a result, the column aliases assigned in SELECT are visible to ORDER BY.
