Why does Query Designer always use TOP 100 PERCENT?

posted Thursday, May 14, 2009 at 3:42 PM

Ever wonder why when using SQL Query Designer that it always puts TOP 100 PERCENT at the beginning of your query?  Well, I wondered too and I researched and found out.  It was beginning to annoy me.  I would go through other people’s code and see it all over the place.  When you see TOP 100 PERCENT in peoples stored procedures you can automatically assume they are not proficient at SQL.  TOP 100 PERCENT is useless in most cases.  

The reason that TOP 100 PERCENT is used in Query Designer is that it is a limitation of views.  When you are designing a query in Query Designer it assumes the query is going to be designed for a view.  A view can not be ordered using the ORDER BY clause.  If you put TOP 100 PERCENT at the top of your view it will then order!  So that is a tip if you are trying to order your view.  One thing to note, if you are ordering a view, please ensure that you have the latest service pack installed on your SQL Server.  There was a bug in both SQL Server 2005 and 2008 that caused the TOP 100 PERCENT statement to not work.  Applying the latest service pack fixes it.

Back to Query Designer… There is no reason to keep the TOP 100 PERCENT in your query.  It has no performance benefits.  Only keep it if you are creating a view.

Twitter Updates

follow me on Twitter