HomeSQL ServerSQL Server – Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

SQL Server – Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

This blog post will explain in simple steps on how you can change the default behaviour in SQL Management studio to change the “Edit Top 200 Rows” and “Select Top 1000 Rows” to Select All.

Have you noticed the Context Menu , when you right click on the table in the SQL Server Management Studio Express?. The SQL Server Management Studio allows us to “Edit” or “View” the entire records in a table.

SQL Server – Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

You might see the following options in the SQL Server Management Studio Express 2008 .

1. Select Top 1000 Rows

2. Edit Top 200 Rows

SQL Server – Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

The idea to include this looks good for performance reasons .But what if you are a person like me who wants want to display all the records .

In SQL Server 2008 Management Studio Express , you can change the default settings thats allows to edit more than the 200 rows at a time, or select more than 1000 rows

To modify the “Edit Top 200 Rows” or “Select Top 1000 Rows” setttings do the following:

  • Run the SQL Management Studio
  • Click the Tools -> Options
  • Select SQL Server Object Explorer . Now you should be able to see the options
  • Value for Edit Top Rows Command
  • Value for Select Top Rows Command

4. Give the Values 0 here to select/ Edit all the Records

SQL Server – Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

5. Now you should see the Edit All and Select All options on the table .

SQL Server – Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

    22 Comments

  1. Dave Campbell
    September 13, 2010
    Reply

    Awesome, Senthil!

    I didn’t realize I could change those…

    Thanks!

    -Dave

  2. September 14, 2010
    Reply

    Thanks Dave …
    Even i did not realise it until i checked the Options in SQL Server 🙂

  3. Visitor
    September 14, 2010
    Reply

    Very nice thank you so much!

  4. jayesh goyani
    October 4, 2010
    Reply

    thanx for your help.

  5. October 4, 2010
    Reply

    Welcome

  6. sapexi
    October 15, 2010
    Reply

    nice.. greattt thank you

    -sapexi-

  7. Tom Psillas
    October 25, 2010
    Reply

    Great information on changing limit of top 200 rows to edit.
    Thanks.

  8. Lakshmi
    March 4, 2011
    Reply

    It works. Thank you so much!

  9. March 4, 2011
    Reply

    Welcome 🙂

  10. Vi
    May 2, 2011
    Reply

    Thanks Senthil for the tip. I was started to think that it is restriction in express edition.

  11. May 2, 2011
    Reply

    Welcome Vi…

  12. Safiyulla
    May 10, 2011
    Reply

    Thanks for instructions.

  13. bahman
    May 25, 2011
    Reply

    hi
    very thanks a lot .

  14. Mauricio
    June 6, 2011
    Reply

    Thanks my friend! This lesson was a real life saver!

  15. larry
    July 16, 2011
    Reply

    Great tip,
    Would you happen to know how to open an edit window without having to find the table and right click on it?
    My database has hundreds of tables, locating the table to right click on is a pain.

  16. Srikanth
    August 11, 2011
    Reply

    Thanks! That was great

  17. LAKS
    September 23, 2011
    Reply

    Great Tip! Thank you!

  18. YesZone
    November 13, 2011
    Reply

    That’s Gr8. Thank you.

  19. jeff
    March 27, 2012
    Reply

    Thanks, worked well.

  20. Abel
    June 1, 2012
    Reply

    Nice great!!! Thanks You!!

  21. shambhoo kumar
    January 21, 2014
    Reply

    Thanks for your great solution. 🙂

  22. Ahsan
    January 31, 2014
    Reply

    Great!!!

Leave a Reply

You May Also Like

When dealing with a relational database management system (RDBMS) like SQL Server, compatibility level is an important concept to understand....
In this blog post, let’s learn about the error message “49975 – Unable to load controller client certificate due to...
In this blog post, let’s learn about the error message “49973 – Cannot remove tempdb remote file to local tempdb...