TOP and OFFSET-FETCH Flashcards
What data type is the number that indicates how many rows to return?
BIGINT
SQL Server 70-461 03-03
What happens if the percent top rows you indicated doesn’t result in a whole number of rows? Maybe 8.3, for example.
The ceiling is returned. So 8.3 records would become 9.
SQL Server 70-461 03-03
What clause do you typically also use with TOP?
- ORDER BY
- It is not required, but without it you’re going to get an arbitrary result that probably won’t be very meaningful.
SQL Server 70-461 03-03
How does WITH TIES work?
- SELECT TOP(3) WITH TIES col1, col2 (Note that 3 can be any BIGINT)
- This will ensure that if the last row, row number three, has the same value for the ORDER BY column selected as rows after it, the rows beyond 3 that have the same value will be included.
SQL Server 70-461 03-03
What is the ordering of multiple rows that are included as a result of using WITH TIES?
Say you ask for TOP(3) WITH TIES. The last record and 2 after it have a matching value so they are included. The order of the last, third, row and the two other will always be arbitrary regardless of whether an ORDER BY clause is also used.
SQL Server 70-461 03-03
What clause must also be used if you include WITH TIES in your TOP statement?
ORDER BY
SQL Server 70-461 03-03
What clause must be used with offset-fetch, or offset by itself?
- ORDER BY
- Microsoft did this because offset-fetch is standard and they wanted to follow the standard of requiring order by.
- Since TOP is specific to SQL Server, they chose to let ORDER BY be optional, except when using WITH TIES.
SQL Server 70-461 03-03
What clause does offset-fetch go after?
ORDER BY
SQL Server 70-461 03-03
Of offset-fetch, which one can be used alone? Which is required to use the other?
- Offset can be used by itself
- In order to use fetch, offset must also be used.
SQL Server 70-461 03-03
How does offset used alone work?
- It indicates how many rows to skip and returns all other rows.
- Example: Offset 50 Rows
- Skips 50 row and returns all others
SQL Server 70-461 03-03
What if you want to use offset or offset-fetch but want an arbitrary order?
- Since the order by clause is required, do the following
- ORDER BY(SELECT NULL)
SQL Server 70-461 03-03
What does OFFSET represent?
How many rows you want to skip
SQL Server 70-461 03-03
What does FETCH represent?
How many rows you want to return.
SQL Server 70-461 03-03
What two words are interchangeable in the offset-fetch statement?
Choose one out of each to use
1. Next or First
2. Row or Rows
SQL Server 70-461 03-03
What if you don’t want to skip any rows?
OFFSET 0 ROWS
SQL Server 70-461 03-03