[SQL Server 2005] Sub-query and "WITH" CTE, which statement would be faster for database p

1.
===================================================================== SELECT * FROM ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY [posttime]
DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC ) AS
[temptable] WHERE [rowcounting] > 20000
=====================================================================
and
2.
===================================================================== WITH [temptable] AS ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY
[posttime] DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC )
SELECT * FROM [temptable] WHERE [rowcounting] > 20000
=====================================================================
Which one will be run faster inside sql server?
Which one can get the most performance?Asked and answered in .programming -- please refrain from multiposting.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Parco" <parco@.nowlover.com> wrote in message
news:eqHzFq5dGHA.380@.TK2MSFTNGP04.phx.gbl...
> 1.
> =====================================================================> SELECT * FROM ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY [posttime]
> DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC ) AS
> [temptable] WHERE [rowcounting] > 20000
> =====================================================================> and
> 2.
> =====================================================================> WITH [temptable] AS ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY
> [posttime] DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime]
> DESC )
> SELECT * FROM [temptable] WHERE [rowcounting] > 20000
> =====================================================================> Which one will be run faster inside sql server?
> Which one can get the most performance?
>