October 20, 2017 at 10:37 am
Hi
What is the use of select top(100) percent in a query in Sql 2008
Thanks
October 20, 2017 at 10:39 am
jagjitsingh - Friday, October 20, 2017 10:37 AMHi
What is the use of select top(100) percent in a query in Sql 2008Thanks
Sounds like auto-generated code. It doesn't do anything and can be removed.
October 20, 2017 at 11:24 am
SQL Server has a deprecated behavior in views and subqueries in which an order by clause is illegal unless with a top () or an offset clause. In the past if you wanted a sort in one of those illegal situations you could get around it using top (100) percent. Since a table has no guaranty of order in a select without an order by and a view is a virtual table the logic is that an order by in a view violates that.
October 20, 2017 at 2:02 pm
Lynn Pettis - Friday, October 20, 2017 10:39 AMjagjitsingh - Friday, October 20, 2017 10:37 AMHi
What is the use of select top(100) percent in a query in Sql 2008Thanks
Sounds like auto-generated code. It doesn't do anything and can be removed.
If removed from a VIEW or CTE where there's also an ORDER BY clause, such removal will break the code unless the ORDER BY is also removed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 20, 2017 at 3:03 pm
sgmunson - Friday, October 20, 2017 2:02 PMLynn Pettis - Friday, October 20, 2017 10:39 AMjagjitsingh - Friday, October 20, 2017 10:37 AMHi
What is the use of select top(100) percent in a query in Sql 2008Thanks
Sounds like auto-generated code. It doesn't do anything and can be removed.
If removed from a VIEW or CTE where there's also an ORDER BY clause, such removal will break the code unless the ORDER BY is also removed.
True, but it doesn't do anything. SQL Server will ignore the order by even with the top (100) percent. SQL Server determines you are returning the whole table and ignores the order by in the CTE or VIEW. You want the results ordered the ORDER BY needs to be on the final SELECT.
October 28, 2017 at 3:07 pm
Lynn Pettis - Friday, October 20, 2017 3:03 PMsgmunson - Friday, October 20, 2017 2:02 PMLynn Pettis - Friday, October 20, 2017 10:39 AMjagjitsingh - Friday, October 20, 2017 10:37 AMHi
What is the use of select top(100) percent in a query in Sql 2008Thanks
Sounds like auto-generated code. It doesn't do anything and can be removed.
If removed from a VIEW or CTE where there's also an ORDER BY clause, such removal will break the code unless the ORDER BY is also removed.
True, but it doesn't do anything. SQL Server will ignore the order by even with the top (100) percent. SQL Server determines you are returning the whole table and ignores the order by in the CTE or VIEW. You want the results ordered the ORDER BY needs to be on the final SELECT.
I'm pretty sure that the only time an ORDER BY will be ignored is with TOP (100) PERCENT even in a CTE or View. I use frequently use ORDER BY (With a TOP 2000000000) in CTE's as a blocking "node" in the execution plan to force other desired behavior. I've also used it in sub-queries to quickly resolve things like MODE calculations in a high performance fashion. If ORDER BY didn't work there, I'd get the incorrect answers and that's not happening.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply