Chapter 3: Filtering and Sorting Data

Question 1

What is a two-valued logic?

Answer 1

When a predicate return as response “TRUE” or “FALSE”.

Question 2

What is a three-valued logic?

Answer 2

When a predicate returns as response “TRUE”, “FALSE” or “UNKNOWN”. It returns “UNKNOWN” when one of operands, or may both, is NULL.

Question 3

NULL = NULL ?

Answer 3

Returns “UNKNOWN”.

Question 4

What is a Search Argument (SARG)?

Answer 4

When the predicate is in the form “column operator value” or “value operator column”. Using the predicate in this way, SQL Server can use indexes.

Question 5

Why we can’t apply a function to the filtered column?

Answer 5

Because it doesn’t allow SQL Server to use indexes. We also cannot manipulate the filtered column.

Question 6

What is the precedence of logical operators, like NOT, AND, OR, and so on?

Answer 6

NOT precedes the AND and OR operators. AND precedes the OR operator. Remember the operations that are in parentheses has more precedences than NOT, AND and OR.

Question 7

Note for this chapter

Answer 7

Review the table for how to use LIKE wildcards.

Question 8

What is the standard way to specify a date?

Answer 8

Like this: ‘YYYYMMDD’

Question 9

Is guaranteed the order of a query?

Answer 9

No, it isn’t. You guarantee the order of a query when you specifiy a ORDER BY clause.

Question 10

What is the default direction of sorting?

Answer 10

Ascendent.

Question 11

When the ordering is deterministic?

Answer 11

When you guarantee the order of the results. For sample, it the values of City columns aren’t unique, that means the order is not deterministic and is not guaranteed. But if you order by city and employeerId, you guarantee the order because employeerId is unique.

Question 12

When you order a result by a column which allows NULL, how SQL Server treats this?

Answer 12

If you specify the order to be ascedent, then NULLs value will be in the beginning of the result. Otherwise, if you specifiy the order to be descendent, the NULLs value will be on the bottom.

Question 13

What is the difference between the result of a query with and one without an ORDER BY clause?

Answer 13

Without an ORDER BY clause, the result is relational (from an ordering perspective);
with an ORDER BY clause, the result is conceptually what the standard
calls a cursor.

Question 14

What’s the TOP clause?

Answer 14

With the TOP option, you can filter a requested number or percent of rows from the query
result based on indicated ordering. You specify the TOP option in the SELECT clause followed
by the requested number of rows in parentheses (BIGINT data type). The ordering specification
of the TOP filter is based on the same ORDER BY clause that is normally used for presentation
ordering.

Question 15

What is the syntax to specify a percent of rows to be filtered?

Answer 15

n = input
SELECT TOP (@n) PERCENT ...

Question 16

What’s the difference between TOP clause and OFFSET clause?

Answer 16

Both filters rows based in how many rows we want in our result. But TOP isn’t a standard, is just only for T-SQL, whereas OFFSET clause is a standard.

Question 17

How OFFSET clause works?

Answer 17

You specify how many rows you want to skip in the result, and then specify how many rows you want to fetch (get) in the result.

Question 18

What is the difference between NEXT and FIRST?

Answer 18

It’s more intuitive to use NEXT when you’re skipping rows (when you specifiy a number greater than 0 in the OFFSET clause), and it’s more intuitive to use FIRST when you’re not skipping rows (when you specify 0 in the OFFSET clause).

Question 19

How I can issue a OFFSET-FETCH clause without specifying a order presentation in the result?

Answer 19

Just adding in the ORDER BY clause the expression: “SELECT NULL”. For example: ORDER BY (SELECT NULL).

Question 20

How do you guarantee deterministic results with TOP?

Answer 20

By defining unique ordering to break ties.

Question 21

What’s is Sargable?

Answer 21

In RDBMS, sargable means when a query can improve his perfomance with an index. Sargable stands for Search Argument Able.

Question 22

When a query isn’t sargable?

Answer 22

When a function is applied to an indexed column example in the WHERE or ON clause. For example: SELECT * FROM TABLE WHERE Function(IndexedColumn) = Value;

Question 23

How I can implement paging in a database?

Answer 23

Using the clause: OFFSET … ROWS FETCH NEXT … ROWS ONLY. The crearly way to achieve this is: OFFSET (page - 1 ) * m ROWS FETCH FIRST n ROWS ONLY, where page = 1 (first page) and m = n. The first clause skip (page - 1) * m rows, and the second clause indicates it’ll get the first n rows (next can be used there instead of first).