Visual Studio vs. SQL Server Management Studio - Your Pick

Question

Just to preface: I work in a small company that does ASP.NET development and uses SQL Server 2005 for all of our database needs.

I was curious as to what were the pros and cons of using Visual Studio or SQL Server Management Studio for our development on the database side (i.e. table creation, stored procedure writing, etc.).

Right now we perform all of our database tasks inside Management Studio and I was wondering if there was some benefit to using Visual Studio instead. Would it make it easier to keep track of procedure changes and other modifications to the database (whether that is through Visual Studio itself or through some type of source control (planning on implementing subversion soon).

I don't personally have problems working with Management Studio but if it would be more efficient and more controllable through Visual Studio in ways that I am just not familiar with I would love to hear about it.

EDIT: I just wanted to note that my specific development environments are SQL Sever 2005, Visual Studio 2005 Professional Edition, and Visual Studio Team System 2008 Development Edition. We do not have team foundation server or any other extras running or installed.

Solution 1

My personal method is to use SQL MS for all the design-related stuff (schema design, diagrams, keys, indexes, etc.), but to craft all my stored procedures, functions and the like in Visual Studio in a "Database" project attached to my solution -- mainly because it lets me keep the procs better version-controlled that way (as I find they generally change more often than the schema), and I find it's particularly handy using the context menus in VS to run the procs on my test and staging machines directly, since I do that so often.

Solution 2

IMHO one big advantage to using the management studio is "script as". To my knowledge there is no equivalent in VS.

Solution 3

Visual Studio 2010 has made some pretty nice upgrades to the SQL Server projects and solutions. Read more here:

https://web.archive.org/web/20160407003056/http://leonardwoody.com/2011/08/27/introduction-to-visual-studio-database-management/

Solution 4

I used Management Studio for all of the SQL work earlier, but of late I've found that the in-house SQL manager of Visual Studio is pretty good too. I am using it more and more for running quick query checks, and modifications to the database scheme.

It's got a great feature of SQL indenting, and I've found it pretty helpful. In my view it beats SQL coloring of Management studio.

Solution 5

That's a difficult question. I guess it would depend on exactly what you're doing. If you're building DALs and stuff, it would probably be easier to use VS, since there's some nice integration (drag-and-drop, etc.). VS is probably adequate for 95% of your daily db activity.

But as someone already mentioned, you don't get the "Script As" helpers (which I use all of the time). You also won't get much (if anything) in the way of user management tools (logons, etc.). And as far as I know, there is no way to manage indexes via Visual Studio. You don't get anything with regard to importing/exporting data either.

View more solutions