Chapter 3 Filtering and Sorting Data

Question 1

What are the 3 query clauses that enable you to filter data based on predicates?

Answer 1

ON, WHERE, and HAVING.

Question 2

What is two-valued logic?

Answer 2

When NULLs are not possible in the data, the predicate can evaluate to true or false.

Question 3

What is three-valued logic?

Answer 3

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.

Question 4

Are two NULLs considered equal, e.g. NULL = NULL?

Answer 4

No. NULL = NULL is unknown - not true.

Question 5

What is the purpose of IS NULL and IS NOT NULL?

Answer 5

IS NULL returns true when the tested operand is NULL. IS NOT NULL returns true when the tested operand is not NULL.

Question 6

What is SARG (Search Argument)?

Answer 6

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.

Question 7

What is the correct way to handle NULL inputs in a predicate?

Answer 7

Don’t use ISNULL or COALESCE because this breaks the search argument form; instead, use something like WHERE region <> ‘WA’ OR region IS NULL;

Question 8

What is the order of precedence for logical operators?

Answer 8

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’)

Question 9

In what order are the expressions in a WHERE clause evaluated?

Answer 9

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.

Question 10

Can implicit conversion in the WHERE clause hurt performance?

Answer 10

Yes. For example, when comparing against literals, be sure to distinguish between unicode and non-unicode correctly, e.g. WHERE lastName = N’Davis’

Question 11

What is the LIKE function?

Answer 11

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.

Question 12

What is the ESCAPE keyword used for in the context of LIKE?

Answer 12

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.

Question 13

What are some of the performance concerns with LIKE?

Answer 13

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.

Question 14

How does SQL Server interpret a date literal when used in a where clause such as WHERE orderdate=’02/12/07’?

Answer 14

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.

Question 15

What is the language neutral date format?

Answer 15

ymd, e.g. 20070212. is language neutral for all date and time data types.

Question 16

What are the performance benefits of the WHERE clause?

Answer 16

(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.

Question 17

What is the form of a filter predicate that can rely on index ordering?

Answer 17

A search argument.

Question 18

Assuming that no ORDER BY is specified, does a given query have a guaranteed order?

Answer 18

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..

Question 19

What is the only way to truly guarantee that the rows are returned from a query in a certain order?

Answer 19

Add an ORDER BY clause.

Question 20

If you don’t indicate a direction (ASC, DESC) with the ORDER BY clause, which is the default?

Answer 20

ASC

Question 21

Assuming that an ORDER BY is specified, but that the ordered column is not unique, do the duplicate rows have a guaranteed order?

Answer 21

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.

Question 22

Can you sort by the ordinal position of columns, e.g. ORDER BY 4, 1? Why is this a bad practice?

Answer 22

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)

Question 23

Can you sort by columns that you’re not returning?

Answer 23

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.

Question 24

Are column aliases assigned in the SELECT clause visible to the ORDER BY clause?

Answer 24

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.

Question 25

How does SQL handle NULL’s during sorting?

Answer 25

Standard SQL says that NULLs should sort together, but leaves it to the implementation to decide if they should be before or after non-NULL values. In SQL Server, they are sorted BEFORE non-NULLs when ASC is used.

Question 26

What is a result with an ORDER BY called? What is a result without an ORDER BY called?

Answer 26

A query with an ORDER BY returns a cursor. A query without an ORDER BY returns a relation (relational).

Question 27

How can you achieve completely deterministic ordering?

Answer 27

The ORDER BY list must be unique.

Question 28

What is the TOP option and how do you use it?

Answer 28

You specify the TOP option in the SELECT clause followed by the requested number of rows in parenthesis (BIGINT); you can also specify a percent of rows to filter instead of a number (FLOAT value 0 - 100). The PERCENT option puts a ceiling on the resulting number of rows. TOP (1) PERCENT of 830 rows => 8.3 => ceiling => 9 rows, e.g. SELECT TOP(1) col1, col2, col3 or SELECT TOP(1) PERCENT col1, col2, col3.

Question 29

Does T-SQL support specifying the number of rows to filter using the TOP option without parenthesis?

Answer 29

Yes, but only for backwards compatibility reasons. The correct syntax is with parenthesis.

Question 30

Is the TOP option limited to a constant?

Answer 30

The TOP option is not limited to a constant input. You can also specify a self contained expression such as a parameter/variable, e.g. DECLARE @n AS BIGINT = 5;
SELECT TOP (@n) col1, col2, etc.

Question 31

When you specify the TOP option is ORDER BY required?

Answer 31

No, an ORDER BY clause isn’t mandatory, but of course, the result is not deterministic. If you are looking for three arbitrary rows, it would be a good idea to add an ORDER BY (SELECT NULL) to let people know the choice was intentional and not an oversight.

Question 32

What is the WITH TIES option?

Answer 32

If there are other rows in the result outside of TOP that have the same value in the ordered by column list as the last row, you can ask to include all ties with the last row by adding WITH TIES, e.g. SELECT TOP(3) WITH TIES col1, col2, etc. This could return more rows than you asked for.

Question 33

Can the TOP option be used in modification statements?

Answer 33

Yes, TOP can be used to limit how many rows get modified.

Question 34

Is the TOP option part of standard SQL?

Answer 34

No. It is proprietary to T-SQL.

Question 35

What is OFFSET-FETCH?

Answer 35

OFFSET-FETCH is a filtering option that can be used to filter data based on a specified number of rows and ordering. It also has skipping capability, making it useful for ad-hoc paging.

Question 36

Is OFFSET-FETCH standard?

Answer 36

Yes.

Question 37

How do you use OFFSET-FETCH?

Answer 37

The OFFSET and FETCH clauses appear right after the ORDER BY clause (required). You first specify the OFFSET clause indicating how many rows you want to skip (0 if you don’t want to skip any); you then optionally specify the FETCH clause indicating how many rows you want to filter., e.g. SELECT orderid, orderdate, custid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

Question 38

Can FETCH NEXT and FETCH FIRST be used interchangeably?

Answer 38

Yes. In order to make the syntax more intuitive, you can use FETCH NEXT or FETCH FIRST interchangeably. When skipping rows, it might be more intuitive to use to use FETCH NEXT, but when not skipping any rows, FETCH FIRST might be more intuitive.

Question 39

Can ROWS and ROW be used interchangeably?

Answer 39

Yes. In order to make the syntax more intuitive, you can use ROWS and ROW interchangeably both for the OFFSET and FETCH. It’s up to the user to use a proper form. It’s not like you will get an error if you say FETCH NEXT 1 ROWS.

Question 40

Can OFFSET be used without FETCH?

Answer 40

An OFFSET clause doesn’t require a FETCH clause. By indicating an OFFSET clause, you’re requesting to skip some rows, but by not indicating a FETCH clause, you’re requesting to return all remaining rows.

Question 41

Can FETCH be used without OFFSET?

Answer 41

A FETCH clause requires an OFFSET clause.

Question 42

How can you use OFFSET FETCH to filter a certain number of rows based on arbitrary ordering?

Answer 42

Use ORDER BY (SELECT NULL) clause, e.g. SELECT orderId, orderdate,custid,empid FROM Sales.Orders ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;

Question 43

Can OFFSET and FETCH accept expressions as inputs?

Answer 43

Yes. This is handy for paging, e.g. OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

Question 44

Is it recommended to use OFFSET FETCH vs TOP?

Answer 44

Because OFFSET FETCH option is standard and TOP isn’t, in cases where they are equivalent, it’s recommended to use the former. OFFSET FETCH does not have an equivalent for TOP’s WITH TIES option.