Exploring Visual Studio 2010's Database Tools

Visual Studio has long been recognized as the premier tool for developing SQL Server database applications. However, Microsoft has been steadily improving the database tools that make Visual Studio an effective development tool for DBAs and database developers alike. I’ll take you on a quick tour of the most important new database tools found in the Visual Studio 2010 Premium and Ultimate editions. These tools let you navigate databases, create database projects, compare database schema and data, design tables, design queries, and write T-SQL scripts.

Related: Top New Features in Visual Studio 2012

Navigating Databases

Like SQL Server Management Studio (SSMS), Visual Studio 2010’s Server Explorer lets you browse and navigate through different databases and database objects. You launch Server Explorer by selecting the Server Explorer option on the View menu, right-clicking Data Connections, and selecting Add Connection. In the Add Connection dialog box, you fill in your SQL Server system’s name, add the required authentication information and database name, then click OK. This will add a database node to Server Explorer, which allows you to work with diagrams, tables, views, stored procedures, functions, synonyms, types, and assemblies. You can see Server Explorer in the left pane in Figure 1.

Figure 1: Server Explorer

Unlike SSMS, Server Explorer shows only one database per data connection. If you want to browse multiple databases, you need to add multiple connections. To navigate through the hierarchy of SQL Server database objects, simply click the arrow in front of the desired object to drill down into the specific objects. Right-clicking an object in the Server Explorer tree displays a context menu that enables you to act on the selected object. The items in the context menu vary, depending on the object selected. For example, right-clicking a table object displays a context menu that has the following options: Add New Table, Compare Data, Add New Trigger, New Query, Open Table Definition, Copy, Delete, Refresh, and Properties.

Creating Database Projects

With Visual Studio 2010’s Database Projects, you can create an offline representation of your database’s schema and create SQL Server CLR (SQLCLR) objects. To create a new SQL Server database project, select New on the File menu and choose the Project option. This will display the New Project dialog box that you see in Figure 2.

Figure 2: New Types of SQL Server Database Projects

Scroll through the list of project templates until you find the Database node. Expand the node and select SQL Server. You can build database projects for SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005. As Figure 2 shows, you can create the following types of database projects with Visual Studio 2010:

Database solutions enable you to create definitions for database objects. They also enable you to deploy these objects to a target SQL Server instance.

Comparing Database Schema and Data

Two of the most powerful database features new to Visual Studio 2010 are the ability to compare databases’ schema and compare their data. These features were carried over from the discontinued Visual Studio 2008 Team Edition for Database Professionals.

Comparing database schema. To compare two databases’ schema, right-click the database connection node in Server Explorer and select the Compare Schema option. This brings up the Compare Schema dialog box shown in Figure 3. On the right side, select the database that you want to use as the target for the comparison and any subsequent changes. On the left side, select the source database that you want to compare the target database to. How long the schema comparison takes to run depends on the size of the database. For small databases such as AdventureWorks, it takes less than a minute.

Figure 3: The Compare Schema Dialog Box

Figure 4 shows sample results from a schema comparison. The top pane lists and compares the objects found in each database, noting each object’s comparison status. Table 1 shows the possible statuses. You can click the arrow in front of each status to display the fine-grained details about any differences found.

Figure 4: Sample Results from a Schema Comparison

The top pane also lists the update actions, which are determined by each object’s status. However, you can sometimes change the default action, as Table 1 shows.

When the update action is set to Drop, Create, or Update, Visual Studio 2010 generates a T-SQL script that will synchronize the target database object with the source database object. When the update action is set to Skip, a change script isn’t generated.

The Object Definitions pane shows the T-SQL creation script for each object in the source and target databases. Differences between the source object and the target object are highlighted. Clicking the Refresh Update Script button (the toolbar button with the two green arrows) generates a synchronization script, which you can see in the Schema Update Script pane at the bottom of the window. Clicking Write Updates in the toolbar executes the script. If you want to edit the script before you run it, you can click Export To Editor in the toolbar, which opens the script in Visual Studio 2010’s T-SQL editor.

Comparing database data. Using the Compare Data option is very similar to using the Compare Schema option. To compare the data between two databases, right-click the database connection node in Server Explorer and select the Compare Data option. This brings up the New Data Comparison dialog box shown in Figure 5.

Figure 5: The New Data Comparison Dialog Box

In this dialog box, pick the source and target database whose data will be compared. You can choose to compare different records, data from the source database only, data from the target database only, or identical records. By default, all of these options are selected. Clicking Next prompts you to select which tables and views to compare. By default, the data comparison selects only tables, so if you want to include views, you must explicitly check the Views option. From there, clicking Finish runs the data comparison and displays the data comparison results, which you can see in Figure 6.

Figure 6: Sample Results from a Data Comparison

In the top pane of the data comparison results, you can see the tables and views that were analyzed. For each table or view, the results include a summary of how many rows contain different data, are unique to the source database, are unique to the target database, and contain identical data. Selecting a table or view that has different rows displays the data in the Different Records pane. The key columns are listed, then the columns that differ between the source and target table or view. Data differences are highlighted in bold. Clicking the Refresh Update Script button generates a synchronization script, which you can see in the Data Update Script pane. The comments in the generated script tell you about the order of the actions that will be performed in the data synchronization operation. In this case, the script will first disable all foreign keys, after which it will perform all the deletes. The script will then perform all the updates followed by all of the inserts. Clicking Write Updates in the toolbar executes the script.

Designing Tables

Like its name suggests, the Table Designer in Visual Studio 2010 lets you create new tables in a database. Much like SSMS’s table designer, Visual Studio 2010’s Table Designer provides a visual editor in which you enter the table’s column names, data types, and their nullability, as well as define keys. The Table Designer is aware of all the SQL Server 2008 data types, including hierarchyID, varbinary(max), and the new geographic and geometry data types.

To use the Table Designer, you open a SQL Server database connection in Server Explorer, expand the connection node, right-click the Tables node, and select the Add New Table option. The Table Designer will open in the Visual Studio editing pane. When you exit the Table Designer, you’ll be prompted to save the table definition, which will result in the creation of the new table in the database defined by your data connection.

Designing Queries

Visual Studio 2010 includes a visual Query Designer that you can use to design queries. Visual Studio 2010’s Query Designer is almost identical to SSMS’s Query Designer. You can run Visual Studio 2010’s Query Designer by opening Server Explorer, right-clicking the database connection node, and selecting New Query from the context menu. The Query Designer will be displayed in the Visual Studio 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.

Writing T-SQL Scripts

Being a development environment, it’s no surprise that Visual Studio 2010 includes a T-SQL editor that lets you create T-SQL scripts and other database objects, such as triggers, functions, and stored procedures. This editor provides T-SQL IntelliSense, but it’s more limited than what you might be used to in SSMS. In addition, it has different capabilities, depending on whether you’re in online or offline mode.

Like you’d expect, the T-SQL editor has full support for color-coded syntax, statement completion, outlining, and T-SQL debugging. If you need to connect to a remote SQL Server system during debugging, you need to make sure your firewall has TCP/IP port 1433 open. If you’ve changed the default SQL Server port, you need to use your updated value. In addition, remember that debugging is something that’s best done in the development environment—you shouldn’t make it a practice to debug scripts on your production servers.

You can start Visual Studio’s T-SQL editor a number of ways. For example, if you want to use it to create a T-SQL script for a database solution, you can right-click the database solution’s name in Solution Explorer and select Add, Script.

After you’re done writing the script, you can execute it by right-clicking somewhere in the T-SQL editor and selecting the Execute SQL option from the context menu, as Figure 7 shows. The script’s results will be displayed in a pane in the lower portion of the window. In addition to the Execute SQL option, the right-click context menu has options that let you connect to and disconnect from the target database, validate T-SQL code, and display estimated execution plans.

Figure 7: The Execute SQL Option in the T-SQL Editor

Tapping Into Visual Studio 2010’s Database Tools

Visual Studio 2010 provides a number of new database tools that can make your database development tasks faster and easier. In particular, I’ve found the new schema and data comparison features to be a valuable addition to my SQL Server developer tools. They make it easy to find the differences in the different versions of the database that you’ve been developing, testing, and deploying.