Prior to the release of SQL Server Management Studio 2008, a user with appropriate permissions to the database could right click on a table and choose to return all rows for editing (SQL 7) or top 1000 rows (SQL 2005), but the default for 2008 has been lowered even more. Now the default is to allow editing on the top 200 rows. However, I’ve been asked before how to change this option and here is how you could allow a bigger result-set to be edited. Now before you go overboard and changing the default records to return for editing to something crazy like 10,000,000 rows, please think about the potential issues of this. With that caution being said, here is how you could bump up that 200 row default.
Step By Step Walkthrough
Let’s fire up SQL Server Management Studio (SSMS) and connect up to your Database Engine. Technically, you don’t have to connect up to the database engine here because we need to change the options of SSMS, not the database itself. However, to show you the default behavior when trying to Edit Top N Rows, you will need to connect to it as I have shown in figure 1 below.
Figure 1: Default “Edit Top 200 Rows”
Next, what we want to do in SSMS is click on the Tools menu and then Options. In the left window pane, you will see a bunch of various options in a tree like structure. Select Commands under the SQL Server Object Explorer section. If you have not edited this default in the past, you should see something like shown in figure 2. Minus the squares that are highlighting the relevant sections of course. J
Figure 2: Default Setting in Options
Now we are to the point of no return. Just kidding, you can cancel out of this if you just wanted to see where the option is. At any rate, in the red square on the right of figure 3, you will notice the “Value for Edit Top <n> Rows Command” under the “Table and View Options” property group. Update this value to 500 or whatever number you wish to set it to. If you want to edit ALL rows, enter 0. Let me provide a word of caution here. When you change this value to more than you need, it may affect network or server performance. So, if it is a table with 1 million rows, you may want to re-think changing it to 0.
Figure 3: Modified Settings to allow 500
If you updated the value to 500, you should now see the option to Edit Top 500 Rows when right clicking on a table. Also note that the option just below the Edit Top <n>. What do you think the option for “Value for Select Top <n> Rows command” will do for you if you set it to 5000? Yeah, you probably guess it! It will change the option shown below from “Select Top 1000 Rows” to “Select Top 5000 Rows”.
Figure 4: Updated Menu Option for Top 500
There you have it! This is how you change the default Edit Top 200 Rows to Edit Top N Rows (where N represents a number). Again though, there are reasons that Microsoft set this default value, so please be reluctant to returning all rows.
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSDBusiness Intelligence Consultant – Pragmatic Works Consulting
Email: email@example.com | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter