TOP and OFFSET-FETCH

Question 1

What data type is the number that indicates how many rows to return?

Answer 1

BIGINT

SQL Server 70-461 03-03

Question 2

What happens if the percent top rows you indicated doesn’t result in a whole number of rows? Maybe 8.3, for example.

Answer 2

The ceiling is returned. So 8.3 records would become 9.

SQL Server 70-461 03-03

Question 3

What clause do you typically also use with TOP?

Answer 3

• 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

Question 4

How does WITH TIES work?

Answer 4

• 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

Question 5

What is the ordering of multiple rows that are included as a result of using WITH TIES?

Answer 5

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

Question 6

What clause must also be used if you include WITH TIES in your TOP statement?

Answer 6

ORDER BY

SQL Server 70-461 03-03

Question 7

What clause must be used with offset-fetch, or offset by itself?

Answer 7

• 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

Question 8

What clause does offset-fetch go after?

Answer 8

ORDER BY

SQL Server 70-461 03-03

Question 9

Of offset-fetch, which one can be used alone? Which is required to use the other?

Answer 9

• Offset can be used by itself
• In order to use fetch, offset must also be used.

SQL Server 70-461 03-03

Question 10

How does offset used alone work?

Answer 10

• 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

Question 11

What if you want to use offset or offset-fetch but want an arbitrary order?

Answer 11

• Since the order by clause is required, do the following
• ORDER BY(SELECT NULL)

SQL Server 70-461 03-03

Question 12

What does OFFSET represent?

Answer 12

How many rows you want to skip

SQL Server 70-461 03-03

Question 13

What does FETCH represent?

Answer 13

How many rows you want to return.

SQL Server 70-461 03-03

Question 14

What two words are interchangeable in the offset-fetch statement?

Answer 14

Choose one out of each to use
1. Next or First
2. Row or Rows

SQL Server 70-461 03-03

Question 15

What if you don’t want to skip any rows?

Answer 15

OFFSET 0 ROWS

SQL Server 70-461 03-03

Question 16

Where can an expression be used to substitute a number?

Answer 16

• TOP(expression)
• OFFSET(expression) ROWS
• FETCH(expression) ROWS
• TOP(expression) PERCENT? *

*Not sure about this one

SQL Server 70-461 03-03

Question 17

Which is standard TOP or OFFSET-FETCH?

Answer 17

• OFFSET-FETCH
• Use this when either TOP or OFFSET-FETCH will get the results you’re looking for.
• TOP is specific to t-SQL

SQL Server 70-461 03-03

Question 18

What can offset-fetch do that top can’t?

Answer 18

Skip rows

SQL Server 70-461 03-03

Question 19

Does TOP always return the exact number of rows you specify?

Answer 19

• No. If there are only 2 rows period and you asked for 3 you will only get two rows.
• If you use WITH TIES you may get less than what you asked for, as explained above, more than what you asked for if there are records that tie or exactly what you asked for if there are no tie records.

SQL Server 70-461 03-03

Question 20

What is the correct syntax for filtering with TOP?

Answer 20

If you want a certain number of records
- SELECT TOP(n) col1, col2, col3 (where n is any BIGINT)

If you want a percentage of records
- SELECT TOP(n) PERCENT col1, col2, col3 (where n is any FLOAT between 0 and 100)
- The word PERCENT is included after the standard TOP statement

SQL Server 70-461 03-03

Question 21

What is the data type that indicates the percentage of rows?

Answer 21

FLOAT with a range from 0 to 100

SQL Server 70-461 03-03

Question 22

What should you use if you truly want an arbitrary result for the TOP records?

Answer 22

• ORDER BY (SELECT NULL)
• This way you have very clearly indicated that you want an arbitrary result and didn’t just forget to specify something by which to order.

SQL Server 70-461 03-03

Question 23

What is the proper form of the offset-fetch statement?

Answer 23

• ORDER BY col1 DESC, col2 DESC OFFSET 50 ROWS FETCH NEXT AS ROWS ONLY

SQL Server 70-461 03-03

Question 24

What can TOP do that offset-fetch can’t?

Answer 24

• Return a percent of records
• Use WITH TIES capability

SQL Server 70-461 03-03

Question 25

How can you ensure deterministic results?

Answer 25

Deterministic in that the same rows will be returned, but with option 1 the order isn’t guaranteed.

1. Use WITH TIES
   or
2. Make the ordering truly unique through what you list in the ORDER BY section. That way, say two rows have the same orderdate, the other ORDER BY columns you have indicated will determine what record gets included in the TOP results.

SQL Server 70-461 03-03