Chapter 3: Filtering and Sorting Data Flashcards
What is a two-valued logic?
When a predicate return as response “TRUE” or “FALSE”.
What is a three-valued logic?
When a predicate returns as response “TRUE”, “FALSE” or “UNKNOWN”. It returns “UNKNOWN” when one of operands, or may both, is NULL.
NULL = NULL ?
Returns “UNKNOWN”.
What is a Search Argument (SARG)?
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.
Why we can’t apply a function to the filtered column?
Because it doesn’t allow SQL Server to use indexes. We also cannot manipulate the filtered column.
What is the precedence of logical operators, like NOT, AND, OR, and so on?
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.
Note for this chapter
Review the table for how to use LIKE wildcards.
What is the standard way to specify a date?
Like this: ‘YYYYMMDD’
Is guaranteed the order of a query?
No, it isn’t. You guarantee the order of a query when you specifiy a ORDER BY clause.
What is the default direction of sorting?
Ascendent.
When the ordering is deterministic?
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.
When you order a result by a column which allows NULL, how SQL Server treats this?
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.
What is the difference between the result of a query with and one without an ORDER BY clause?
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.
What’s the TOP clause?
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.
What is the syntax to specify a percent of rows to be filtered?
n = input SELECT TOP (@n) PERCENT ...
What’s the difference between TOP clause and OFFSET clause?
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.
How OFFSET clause works?
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.
What is the difference between NEXT and FIRST?
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).
How I can issue a OFFSET-FETCH clause without specifying a order presentation in the result?
Just adding in the ORDER BY clause the expression: “SELECT NULL”. For example: ORDER BY (SELECT NULL).
How do you guarantee deterministic results with TOP?
By defining unique ordering to break ties.
What’s is Sargable?
In RDBMS, sargable means when a query can improve his perfomance with an index. Sargable stands for Search Argument Able.
When a query isn’t sargable?
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;
How I can implement paging in a database?
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).
