How to Change Default Value For Selecting and Editing Top n Rows in SQL Server 2012

This article is important for Developer DBAs or SQL Server Programmers. If you don't want to open an entire table when you right-click a table in a database then SQL Server Management Studio (SSMS) only shows the Top 1000 rows when selecting data and the top 200 rows when editing data for a table. This feature was introduced In SQL Server 2008.

So let us look at the SQL Server Management Studio, by default you can only Select the Top 1000 Rows or Edit the Top 200 Rows for a table as shown in the following screen.

You can see that the default option as shown above is to Select Top 1000 Rows or Edit Top 200 Rows.

Now I will change the default value for selecting and editing using the following procedure.

Step 1

In SQL Server Management Studio, under Tools, click Options as shown in the following screen.

Step 2

Now click the Options dialog box, expand the SQL Server Object Explorer and then select the Commands tab as shown in the following screen. Change the Value for Edit Top Rows command and/or Value for Select Top Rows command to an appropriate value as you desire as shown in the following screen and then click OK.

Step 3

When you have saved the changes go ahead and right-click on a table in the database to see the changes as shown in the following screen.

Summary

This article showed how to change a default value for selecting and editing N rows in SQL Server 2012.