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?
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.
How does SQL handle NULL’s during sorting?
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.
What is a result with an ORDER BY called? What is a result without an ORDER BY called?
A query with an ORDER BY returns a cursor. A query without an ORDER BY returns a relation (relational).
How can you achieve completely deterministic ordering?
The ORDER BY list must be unique.
What is the TOP option and how do you use it?
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.
Does T-SQL support specifying the number of rows to filter using the TOP option without parenthesis?
Yes, but only for backwards compatibility reasons. The correct syntax is with parenthesis.
Is the TOP option limited to a constant?
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.
When you specify the TOP option is ORDER BY required?
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.
What is the WITH TIES option?
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.
Can the TOP option be used in modification statements?
Yes, TOP can be used to limit how many rows get modified.
Is the TOP option part of standard SQL?
No. It is proprietary to T-SQL.
What is OFFSET-FETCH?
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.
Is OFFSET-FETCH standard?
How do you use OFFSET-FETCH?
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;
Can FETCH NEXT and FETCH FIRST be used interchangeably?
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.
Can ROWS and ROW be used interchangeably?
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.
Can OFFSET be used without FETCH?
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.
Can FETCH be used without OFFSET?
A FETCH clause requires an OFFSET clause.
How can you use OFFSET FETCH to filter a certain number of rows based on arbitrary ordering?
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;
Can OFFSET and FETCH accept expressions as inputs?
Yes. This is handy for paging, e.g. OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;
Is it recommended to use OFFSET FETCH vs TOP?
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.