TOP and OFFSET-FETCH Flashcards

1
Q

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

A

BIGINT

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What clause do you typically also use with TOP?

A
  • 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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How does WITH TIES work?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

ORDER BY

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What clause does offset-fetch go after?

A

ORDER BY

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

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

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How does offset used alone work?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

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

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What does OFFSET represent?

A

How many rows you want to skip

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What does FETCH represent?

A

How many rows you want to return.

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

OFFSET 0 ROWS

SQL Server 70-461 03-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Where can an expression be used to substitute a number?

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

*Not sure about this one

SQL Server 70-461 03-03

17
Q

Which is standard TOP or OFFSET-FETCH?

A
  • 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

18
Q

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

A

Skip rows

SQL Server 70-461 03-03

19
Q

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

A
  • 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

20
Q

What is the correct syntax for filtering with TOP?

A

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

21
Q

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

A

FLOAT with a range from 0 to 100

SQL Server 70-461 03-03

22
Q

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

A
  • 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

23
Q

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

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

SQL Server 70-461 03-03

24
Q

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

A
  • Return a percent of records
  • Use WITH TIES capability

SQL Server 70-461 03-03

25
Q

How can you ensure deterministic results?

A

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