What's the best way to work with SQL Server data non-programmatically?

Question

We have a SQL server database. To manipulate the data non-programmatically, I can use SQL Server Management Studio by right-clicking a table and selecting "Open Table". However this is slow for very large tables and sorting and filtering is cumbersome.

Typically what we have done until now is to create an Access database containing linked tables which point to the SQL Server tables and views. Opening a large table is much faster this way, and Access has easy-to-use right-click filtering and sorting.

However, since Access 2007, sorting in particular has been quite slow when working with large tables. The Access database can also inadvertently lock the database tables, blocking other processes that may need to access the data. Creating the Access database in the first place, and updating it when new tables are added to SQL Server, is also tedious.

Is there a better way to work with the data that offers the usability of Access without its drawbacks?

Answer 1

I don't know how well it will do with really large tables, but Visual Studio is much quicker than SQL Management Studio for basic table operations. Open up your database in Server Explorer, right-click on a table, and select either "Open" to just display the data or "New Query" to filter, sort, etc.

Answer 2

Editing raw data is a dangerous no-no. Better to identify the situations where you find yourself doing that and put together an application interface to act as an intermediary that can prevent you from doing stupid things like breaking a foreign key.

Answer 3

I don't know what the performance would be like for large datasets, but open office has a database program (Base), which is an Access clone and may just be what you are looking for.

Answer 4

I've used Visual Studio to do lots of stuff, just for convenience rather than having to log into the server and work on the database manager directly.

However, have you tried Toad for MS SQL (from Quest Software)? I use it all the time for Oracle, and have had good results (often better than Oracle's tools).

Answer 5

Joel Coehoorn's answer is of course correct, that if the data is critical or there are naive users using the data, then a application front end should be developed. That being said, I have cases where a wise user (ok, me) user needs to just get in there and poke around.

Instead of directly looking at the tables, use MS Access but use queries to narrow down what you're looking at both column wise and row wise. That will improve the speed. Then edit the query properties and make sure that the query is No Locks. That should eliminate any blocking behavior. You may want to limit the number of rows returned which again will improve the speed. You can still edit the data in the query as you look at it.

Depending on what you're looking at, it may also be useful to set up database Views in the SQL Server to do some of the heavy lifting on the server rather than on the client.

Answer 6

You might want to read Tony Toews's Access Performance FAQ, which provides a number of hints on how to improve performance in an Access application. Perhaps one of those tips will solve the problem in your A2K7 app.