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

By: Ashish Kumar Mehta | Read Comments (3) | Print Ashish has been contributing to the MSSQLTips.com community since 2009 with over 60 tips. Related Tips: More

Problem

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.

Solution

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.

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.

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.

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

Next Steps

• Read Using Object Explorer Details and Object Search Feature of SQL Server 2008 Management Studio
• Read Performance Analysis Using SQL Server 2008 Activity Monitor
• Read more tips on SQL Server Management Studio Tips and customize SSMS to meet your needs

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Friday, March 11, 2011 - 1:59:09 AM - Arun
Interesting feature.. this is really helpful..

Friday, March 11, 2011 - 8:21:16 AM - Hilary
Thanks for this tip! This was the one thing that bugged me about SSMS 2008 and now it is fixed.

Friday, March 11, 2011 - 9:47:14 AM - Tim
Very cool.  Didn't know there was a setting for that.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code