The SQL Server Data Tools (SSDT) provide access to many SQL Server features from within Visual Studio. One of those features, available from the SQL Server Object Explorer, is the visual Data Editor.
While the Data Editor has always been great for inserting, updating, and viewing data, it did not support any sorting or filtering … until now!
- For an introduction to SSDT and the SQL Server Object Explorer, see this post.
- For an introduction to the SSDT Data Editor, see this post.
The March 2014 release of SSDT added support for SQL Server 2014 databases. But it ALSO provided new features in VS 2012 and VS 2013 for sorting and filtering the data in the Data Editor!
If you are using VS 2012, you can use the update option to get this update (SQL | Check for Updates).
If you are using VS 2013, the update should appear in the Notification window when you click the notification flag:
If not, you can look for it under Tools | Extensions and Updates.
Once you have it installed, your Data Editor will have two additional buttons:
The first button disables sorting and filtering.
The second button displays a dialog for entry of the sorting and filtering criteria.
In this example, I set a sort on the FirstName column. Any number of sorts can be added, either ascending or descending. I also added a filter on the LastName so only customers with a last name that begins with B will be listed.
This dialog also allows you to uncheck columns to remove them from the Data Editor display. This helps you focus on only the columns you need to see.
Notice at the bottom of the dialog is the SQL expression SSDT will use to query the table. This expression changes as you modify the columns, sorting, sort order, or filter.
Clicking Apply immediately executes the query and re-populates the data in the Data Editor. This allows you to view the results without closing the Filter and Sort dialog.
When you have the results you need, click OK to close the dialog.
NOTE: When I attempted to edit the sorted and filtered data, I received an error message:
HOWEVER, the edits were actually made to the data.
Try out these new features any time you want more control over the data displayed in the Data Editor.