SQL Rowcount vs Top

Question

What is the different between

Set Rowcount X

And

Select Top X *
From Z

In TSQL?

Answer 1

The main difference is that top will only effect the query you are running while set rowcount will persist with the connection and apply to all queries executed within that connection.

Answer 2

Top can do a few more things for you. For one, you can specify a percentage, instead of an integer. You can also handle situations where ties in column values occur.

http://technet.microsoft.com/en-us/library/ms189463.aspx

Answer 3

In older versions of SQL Server (2005 and earlier I am not sure about 2008) you could not use a variable in a top statement so:

declare @rc int

set @rc=10

select top @rc * from myTable --Wont work

set rowcount @rc
select * from myTable --Will work