Visual Studio 2010 Database Tools

Although SQL Server Management Studio (SSMS) is the primary development tool for most DBAs, when you’re developing applications you tend to spend a lot of time in Visual Studio (VS) and don’t really want to be jumping back and forth between development environments to work on both the database and the application.

Related: Exploring Visual Studio 2010's Database Tools

Fortunately, VS 2010 provides some database tools that you can use to make your database application development easier and more seamless. Some of the cool database development features in VS 2010 include these:

1. Server Explorer

Without a doubt the most essential database tool in VS 2010 is Server Explorer. Like SSMS, VS2010’s Server Explorer lets you browse and navigate different databases and database objects. You launch Server Explorer by using the View, Server Explorer option from the VS 2010 menu, then right-clicking Data Connections and selecting Add Connection. On the Add Connection dialog box, you fill in your SQL Server system name, add the required authentication information and database name, and click OK. This adds a database node to Server Explorer which allows you to work with diagrams, tables, views, stored procedures, functions, synonyms, types, and assemblies. 

2. Database Solutions

Database Solutions provide a way to group all of your SQL Server development into one or more VS 2010 solutions. To add a new Database Solution, select File, New, Project from the VS 2010 menu. This displays the New Project dialog box. Select Database, SQL Server from the list of installed templates. Give your new database solution a name and click OK. This feature also enables you to create definitions for all types of database objects and deploy these objects to a target SQL Server database.

3. Compare Data and Compare Schema

Another powerful database feature that’s new to VS 2010 is the ability to compare database schema and database tables. This feature was carried over from the earlier Visual Studio for Database Professionals edition, which has since been discontinued. You can run the Compare schema and compare data options by using Server Explorer: Right-click the database connection node, then choose either the Compare Schema or Compare Data options from the context menu. A dialog is displayed that lets you select the source and target databases. You can also generate a script to synchronize the source and target. 

4. Table Designer

VS 2010 Table Designer helps you to create new tables in a database, providing a visual editor that lets you enter the table’s column names, data types, and nullability. It is aware of all of the SQL Server 2008 data types. To start VS 2010 Table Designer, use Server Explorer to open a SQL Server database connection. Then expand the connection node, right-click Tables, then select Add New Table from the context menu. This opens Table Designer in the VS editing pane. Exiting the designer will prompt you to save the table definition.

5. Query Designer

VS 2010 also includes a visual Query Designer that you can use to design queries.

Run VS 2010 Query Designer by opening Server Explorer and right-clicking the database connection node, then selecting New Query from the context menu. Query Designer will be displayed in the VS editing pane. As you visually design the query, the corresponding T-SQL code is interactively displayed in the code pane. You can run the query by right-clicking the designer and selecting Execute SQL. 

6. T-SQL Editor

You can develop T-SQL scripts and other database objects in VS 2010 via the T-SQL editor, which provides full T-SQL IntelliSense as well as T-SQL debugging. You can start the T-SQL editor in several ways. To use it to create a T-SQL script, right-click your Database Solution name in the Solution Explorer and select Add, Script. Execute the script by right-clicking the T-SQL editor and selecting the Execute SQL option from the context menu.

7. SQLCLR Projects

Although you can use either SSMS or VS 2010 to create T-SQL–based scripts and database objects, you must use VS to create SQLCLR objects. SQLCLR objects can be created using either Visual Basic or C#. To create a new SQLCLR object, right-click your Database Solution in the Solution Explorer and select Add, New Project to display the Add New Project dialog box. Then select either Visual Basic SQL CLR Data Project or Visual C# SQL CLR Database Project.

Learn more: Visual Studio 2010 for Database Professionals