2 added 11 characters in body
source | link

Generally speaking ... performance should be the same.

Technically speaking ... it depends on the query; for example, if the TOP is used within the confines of a derived table then you may see different performance depending on how/if the optimizer flattens the query.

To answer the question for a given query(s), run some tests with the following options enabled:

set showplan on
set statistics io,time on
go
-- set rowcount XXX
select [top] ...
-- set rowcount 0
go

You'll want to run the query a couple times to make sure data has been pulled from disk so that you can compare in-memory data queries to each other.

Obviously (?) you'll then want to compare the 2x query plans as well as the IOs and timings (keeping in mind that timestimes can vary widelygreatly based on the load on the dataserverduration of blocking and how long your query spends/or time spent on athe wait queue waiting for a spinto run on a cpudataserver engine). Net result is thatIOs should be comparable on repeat runs, but for performance comparisonstimings you'll likely want to run each query a few times and take the average.

Generally speaking ... performance should be the same.

Technically speaking ... it depends on the query; for example, if the TOP is used within the confines of a derived table then you may see different performance depending on how/if the optimizer flattens the query.

To answer the question for a given query(s), run some tests with the following options enabled:

set showplan on
set statistics io,time on
go
-- set rowcount XXX
select [top] ...
-- set rowcount 0
go

You'll want to run the query a couple times to make sure data has been pulled from disk so that you can compare in-memory data queries to each other.

Obviously (?) you'll then want to compare the 2x query plans as well as the IOs and timings (keeping in mind that times can vary widely based on the load on the dataserver and how long your query spends on a wait queue waiting for a spin on a cpu). Net result is that for performance comparisons you'll want to run each query a few times and take the average.

Generally speaking ... performance should be the same.

Technically speaking ... it depends on the query; for example, if the TOP is used within the confines of a derived table then you may see different performance depending on how/if the optimizer flattens the query.

To answer the question for a given query(s), run some tests with the following options enabled:

set showplan on
set statistics io,time on
go
-- set rowcount XXX
select [top] ...
-- set rowcount 0
go

You'll want to run the query a couple times to make sure data has been pulled from disk so that you can compare in-memory data queries to each other.

Obviously you'll then want to compare the 2x query plans as well as the IOs and timings (keeping in mind that times can vary greatly based on duration of blocking and/or time spent on the wait queue waiting to run on a dataserver engine). IOs should be comparable on repeat runs, but for timings you'll likely want to run each query a few times and take the average.

1
source | link

Generally speaking ... performance should be the same.

Technically speaking ... it depends on the query; for example, if the TOP is used within the confines of a derived table then you may see different performance depending on how/if the optimizer flattens the query.

To answer the question for a given query(s), run some tests with the following options enabled:

set showplan on
set statistics io,time on
go
-- set rowcount XXX
select [top] ...
-- set rowcount 0
go

You'll want to run the query a couple times to make sure data has been pulled from disk so that you can compare in-memory data queries to each other.

Obviously (?) you'll then want to compare the 2x query plans as well as the IOs and timings (keeping in mind that times can vary widely based on the load on the dataserver and how long your query spends on a wait queue waiting for a spin on a cpu). Net result is that for performance comparisons you'll want to run each query a few times and take the average.