How to Edit More than 200 Rows in SQL Server 2008 Management Studio

Change the default settings of "Edit Top 200 Rows" or "Select Top 1000 Rows" in SQL Server 2008 Management Studio.
by Updated April 5, 2012

In SQL Server 2008 Management Studio, you may want to change the default settings to allow you to edit more than the 200 rows at a time, or select more than 1000 rows, which are the default settings for SQL Server.  To modify "Edit Top 200 Rows" or "Select Top 1000 Rows" setttings do the following:

  1. In SQL Server 2008 Management Studio, go to "Tools" -> "Options" -> "SQL Server Object Explorer" -> "Commands".
  2. Now in the right-hand side "Table and View Options" section, you can change either:
    • Value for Edit TopRows command, to a value greater than or less than 200.
    • Value for Select TopRows command, to a value greater than or less than 1000.
  3. By specifying a value of 0, SQL Server will return all rows.  (If you sql tables are really large, you will definitely NOT want to set these values to 0.
  4. Click OK to save your changes.

You can also open up the SQL pane and change the query for SELECT TOP (200).  To do this follow these steps:

  1. In the Object Explorer, right click on the Table you want to edit and select "Edit Top 200 Rows"
  2. Once the 200 rows populate in the Main window, right click anywhere in the Main pane and click -> Pane -> SQL.
  3. A "SQL Pane" will open up above your Table of 200 rows, where you can then change the SELECT statement to query as many rows as you would like.




anonymous by Preetham k a on 11/30/2010
thank you, you saved my is very usefull....
anonymous by Rahul on 12/9/2010
ya really help full one
anonymous by Suresh Nair on 5/9/2011
Thank you. Saved a lot of time.
anonymous by James Kirk on 2/24/2012
Awesome, thanks a lot :) 
anonymous by Ngon on 3/21/2012
Thank you!
anonymous by RAJEEV RANJAN on 5/5/2012
Thank u so much....
anonymous by Meenakshi on 5/17/2012
Thanks a lot..I had been searching for this for a long time....Good One
anonymous by bkd on 10/11/2012
Simple but VERY effective. Thanks.
anonymous by Tony on 10/22/2012
Cheers, saved me a heap of time writing a query to get the 201ist DB entry...

anonymous by bias on 1/24/2013
Is there any way to show grid in descending order?
anonymous by Parminder on 1/30/2013
Awesome and so straightforward. Thank you
anonymous by Alano on 9/13/2013
Very useful...thanks.
anonymous by munusamy on 9/17/2013
thank u
anonymous by sothen on 1/8/2014
thanks u
anonymous by Dewesh Pushkar on 2/4/2014
Thanks a lot............

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts

Here's one way to setup your connection string for a SQL Server 2008 Express database for a live website using ASP.NET on Windows Server 2008. Open "Sql Server Configuration Manager" -> the click on "SQL Server Network Configuration" -> then click...  more »

I recently upgraded from Microsoft SQL Server 2005 to Microsoft SQL Server 2008 on my Windows Vista computer. After successfully installing SQL Server 2008, I could not find the Reporting Services instance installed. I have a feeling this was due to the...  more »

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #. 1) First make sure the SQL Server Authentication is enabled and the User name that...  more »

Here's how you can enable SQL Server Authentication in a SQL Server 2008 database, and then add a User login and password account for connecting to the database. Open SQL Server Management Studio In the Object Explorer sidebar, right-click on the top SQL...  more »

Are you backing up your SQL Server 2008 databases daily? You should be, especially if you don't want to lose any of your precious data that you're storing. It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or...  more »