Often we open table in SSMS to view/edit the content by right clicking on the table and NOT by writing simple select or alter query. Almost all of us do that on a daily basis. And now comes the next question How many of you are aware of that you can change the default values to view/edit rows which is provided by default in SSMS, for select we have “Select Top 1000 Rows” and for edit we have “Edit Top 200 Rows”.
In the previous version of SQL Server Management Studio we had an option to open table by right clicking on the table to view all the records. The major drawback 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 our table has huge amount of data.
In SQL Server 2008 and above Management Studio we have an option to select/edit n number of rows, by default we can only Select Top 1000 Rows or Edit Top 200 Rows for a table as shown below:
If you want to change the default values and to reflect the same whenever you work with your SSMS then this can be done by doing one time settings by following these steps:
1)In your SSMS under Tools, click Options as shown below:
2)In the Options dialog box, expand SQL Server Object Explorer and then select the Commands tab as shown 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 and then click OK.
3)Once the changes are saved go ahead and right click on a table to see the changes as shown below:
You will see that the default select/edit values are now changed.
What if You dont want to retain these changes and want to set per table basis. No problem we have another way of doing this.
This can be done by following these two simple steps:
1)Edit the select query when you right click -> pane -> SQL.
2)In SQL statement you will see Top(200). You just have to change the number of rows you need.
And you are done!