• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2373
  • Last Modified:

How to remove TOP (100) PERCENT with MS SQL Server management studio 2008?

Hi experts!

When I make new query on Microsoft SQL Server Management studio 2008 in query designer I always get SELECT TOP (100) PERCENT dbo.LPrgPlans.LPrgPlanI

How to remove this TOP (100) PERCENT ???  Now I always remove it manually because I post the code in C# project and  ... but it is stupid to remove it manually…

And at all – which tool you use to make your DB - SQL tables, views, Sp ? At all ... this Management Studio is NOT designed to handle with 200+tables, 600 SP , look to me that is written from Microsoft’s “developers by paper” who never face with the real problems that developers works … Any feature for navigation is missing… a feature to group somewhere tables, views, sp IN ONE LIST is missing , the result is that 10-20 % of the developers time  is lost for navigation …
0
dvplayltd
Asked:
dvplayltd
  • 4
  • 2
  • 2
  • +2
1 Solution
 
TempDBACommented:
Hi,
   Are you talking about SSMS i.e. Sql Server Management Studio only. If yes, you can do it by modifying the file which is a template that is opened when you click new query:-
The filename is SQLFile.sql which is located in C:\Program Files...\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql
0
 
Robert SchuttSoftware EngineerCommented:
I think the TOP (100) PERCENT is added when you use ORDER BY in the view definition. It's recommended to use ORDER BY in the select on the view afterwards for efficiency (although I wouldn't know exactly how or why).

I use the express version and have always thought Management Studio is actually a great tool but I can imagine with 600 sp's it gets harder to use. The only thing I have seen is filtering, so at least I can have a look at any sp's that have to do with invoices by filtering for names containing 'Inv' but other than that I haven't come across advanced options in that area.
0
 
dvplayltdAuthor Commented:
To tempDBA

I find this file, but it is empty? Anyway, my ask is that the query designer almost every time ADD this Top 100, not the template when i use New Query button ...
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
TempDBACommented:
Sorry, can you please help me with the steps how you are getting that?
0
 
dvplayltdAuthor Commented:
Here you the steps:

1.      Create a view with Query Designer /the one that represent tables and join graphically/
2.      Copy  SQL of other view or SP and paste the code from this view to new one made in step 1 /I use this way all the time to modify a SQL code with help of the graphic designer and later return this  SQL code in Stored procedure/
3.      No matter in clipboard’s text there no TOP 100, i turn off the propery of query .. this code is added to the view.

Look the picture. No matter I put top specification to NO it again is added … and again i delete it. I do this all the time , it is a real problem to me.
Untitled.png
0
 
Robert SchuttSoftware EngineerCommented:
Have you tried removing the sort order and _then_ remove the top 100 percent?
0
 
DcpKingCommented:
In a view you cannot use ORDER BY in SQL Server since SQL Server 2005

However, you can avoid this limit by using SELECT TOP 100 PERCENT instead of just SELECT.

As for having 200 tables and 600 stored procedures, that certainly isn't a huge system. However, to assist you, you can right-click on the section title ("Tables", "Stored Procedures", etc.), select Filter and then Filter Settings, and then you can filter on the name or schema. You can, for example, put your naming standards to good use here! Again, give new projects their own schemas, and separate them from the crowd in that way.

hth

Mike
0
 
dvplayltdAuthor Commented:
To DcpKing

Thanks for your answer. I use SQL 2008.

For navigation, i use RedGate's SQL Search - it is free and is much better than the filtering. Of course, i use similar names for logic relation,but even the SQL search solve just one question and many time is lost in simple technical questions ...
0
 
pinaldaveCommented:
You have order by in the view which is making SSMS add TOP 100 PERCENT.

Remove order by from the view and add it outside of the view.

Here is the blog post which can be helpful to you.
http://blog.sqlauthority.com/2009/11/24/sql-server-interesting-observation-top-100-percent-and-order-by/
0
 
pinaldaveCommented:
Additionally, if you create stored procedure instead of View - you will not face this issue.
0
 
dvplayltdAuthor Commented:
10x. Thanks to you, now I'm clear why the sort do not works sometimes, so I should NOT remove the TOP 100 ..thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now