Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Change Default Value for Select Top n and Edit Top n Rows in SQL Server Studio

By:   |   Last Updated: 2011-03-11   |   Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | More > SQL Server Management Studio


While looking through the features in SQL Server 2008 Management Studio (SSMS), I noticed that SSMS only shows the Top 1000 rows when selecting data and the Top 200 rows when editing data for a table. In SQL Server 2005 you used to be able to open the entire table, but this option no longer exists. In this tip we will take a look at how to change the default values for Select Top n Rows and Edit Top n Rows in SQL Server 2008 Management Studio.


In the previous version of SQL Server Management Studio you might have noticed that there was an option to Open Table by right clicking on the table to view all the records within the table. The disadvantage of using the Open Table option of SQL Server 2005 Management Studio was that SSMS used to hang or become unresponsive for a long period of time if you happened to open a large table.

In SQL Server 2008 Management Studio, by default you can only Select Top 1000 Rows or Edit Top 200 Rows for a table as shown in the below snippet.

in sql server 2008 management studio you can only select the top 100 rows

The default option as shown above is to Select Top 100 Rows or Edit Top 200 Rows. This can be changed by following these steps.

1. In SQL Server Management Studio, under Tools, click Options as shown in the snippet below.

in ssms under tools, click options

2. In the Options dialog box, expand SQL Server Object Explorer and then select the Commands tab as shown in the snippet below. Change the Value for Edit Top Rows command and/or Value for Select Top Rows command to a suitable value as per your needs as shown in the below snippet and then click OK.

expand sql server object explorer

3. Once you have saved the changes go ahead and right click on a table to see the changes as shown below.

sql server 2008

Next Steps

Last Updated: 2011-03-11

get scripts

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Tuesday, March 08, 2016 - 12:10:34 AM - HafizAsim Back To Top


 This tip is really helpful, thanks Admin


Saturday, April 28, 2012 - 5:40:02 AM - Manoj Back To Top
Golly . I absolutely HATE 2008 with a poissan because of the changes it's made to object explorer. On in particular is this object explorer details concept. I'm in an medium sized database with Several hundred procedures. However in order to see those procedures it would seem so far that I have to open this object explorer details page . So now I've got 10+ query pages open. I want to go check out the details of a table and open an SP. In 2005 this was so simple. It was effortless. In 2008 I have to play with tabs or do all sorts of weird stuff. It's aggravating. The information is organized poorly and is very unhelpful. If they were going to make it configurable, they should have made it configurable to the point that it would mirror 2005. Far better organization there. But that's just my opinion.-5 stars out of 5 for 2008 in my book. I've had nothing but trouble with it. Installing, utilizing, and so forth. It's default settings aren't even intelligent or useful. You have to go and reconfigure the entire thing before you can utilize it with a medium or larger database. It's like it's default setup for small databases or something. I mean, sure, no matter what you'll likely have to reconfigure something. But some of the default settings blocked remote login to my database . huh? Any respectable sized database is going to be remote The state of those default setting is silly stupid in my opinion.

Friday, March 11, 2011 - 9:47:14 AM - Tim Back To Top

Very cool.  Didn't know there was a setting for that.

Friday, March 11, 2011 - 8:21:16 AM - Hilary Back To Top

Thanks for this tip! This was the one thing that bugged me about SSMS 2008 and now it is fixed.

Friday, March 11, 2011 - 1:59:09 AM - Arun Back To Top

Interesting feature.. this is really helpful..

Learn more about SQL Server tools