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 …
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 …
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.
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.
ASKER
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 ...
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 ...
Sorry, can you please help me with the steps how you are getting that?
ASKER
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
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
Have you tried removing the sort order and _then_ remove the top 100 percent?
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
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
ASKER
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 ...
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 ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Additionally, if you create stored procedure instead of View - you will not face this issue.
ASKER
10x. Thanks to you, now I'm clear why the sort do not works sometimes, so I should NOT remove the TOP 100 ..thanks!
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\VSSh