A Guided Tour of Microsoft Visual Studio 2010 Database Development

Introduction

Microsoft Visual Studio has been a bit slow to embrace the database developer. Until Visual Studio 2005 for Database Professionals there was no serious Visual Studio database development environment. If you're like most TSQL database developers you probably use features of Visual Studio Database Edition for source control and syncing TSQL code, but your development is probably done in some other tool.
Visual Studio 2010 may change all that. This article will demonstrate new database development features in Microsoft Visual Studio 2010.

Beta 2, Licensing, GDR

This article is based on features in the Visual Studio Ultimate Edition Beta 2. I'm not only using a Beta version, I'm using a high-end license version. Though a second Beta will likely have feature parity with a product release, not all licenses will have all the features I demonstrate in this article.

Also, some of the features described below already ship with the Visual Studio 2008 Database Edition GDR. If you're evaluating whether you should move to Visual Studio 2010, but you're already using Visual Studio 2008, you should look at the GDR features before assuming you need to upgrade to Microsoft Visual Studio 2010. A complete GDR article can be found in the sources at the end of this article.

Creating a Project in Microsoft Visual Studio 2010

CLR and Data Tier development is beyond the scope of this article. I'm going to focus on more traditional TSQL development using the SQL Server 2008 Database Project and SQL Server 2008 Server Project templates.

A database project and server project include many of the same template items. Server projects include some additional server specific items like Service Broker Endpoints, Server Accounts, and linked servers. Database projects include some additional storage item templates like filestreams. As you explore each project template you'll also note differences in the template folder structure and deployment options.

To demonstrate key features across the templates I created 3 projects.

An empty Database Project.

An empty Server Project.

A Database Project imported from the AdventureWorks database.

Unless I note otherwise, all demonstrations in this article will use the AdventureWorks database project.

Editor Feature Overview

Intellisense is probably the first feature a developer will notice. As I mentioned earlier, prior versions of the database development environment lacked a good TSQL editor. Below is a short intellisense demonstration.

Figure 2: Using Intellisense

There are some tricks you'll want to follow to work with the intellisense. As you may have observed, I filled in the FROM clause before adding fields. FROM is entered first because there is no way intellisense can guess what you will place in the SELECT portion, unless each field is fully qualified with a Schema and table name. If you do find intellisense annoying you can disable it.

Instead of typing all your code, from the Schema view you can drag and drop fields into your TSQL statements. Unfortunately there is no way to simply drag and drop the field without fully qualifying it.

Code snippets are another nice editor feature. Below is what this behavior looks like.

Figure 3: Code Sinppets

I always forget the signature of various TSQL statements. Creating temporary tables and building database structures are common in Stored Procedures. Creating the signature from an item template would take almost as long as combing through help for the statement signature. Rather than hunting for a statement signature, it was nice to see all the standard statements already built into the editor through code snippets.

If you develop in other Visual Studio languages, you'll appreciate living in a single tool for all of your development. The improved editor completes all parts of application development, from the database, through the data tier, and into the core of the application.

A new editor is great, but database development is difficult without some test data.

Data Menu

Schema compare was what originally drew me to using the Database Project in Visual Studio 2005. In Visual Studio 2010 the Schema compare is there and enhanced. A developer will notice quite a few more compare options to activate and deactivate.

In prior versions, you could create test data with a separate testing project and with Data Generation plans. Data Generation plans have been enhanced a bit in Visual Studio 2010. A Data Generation plan appears below:

Figure 4: Data Generation Plan

You can create a Data Generation Plan from the Data menu or add a Data Generation Plan from the project "Add Items" menu. Selecting the column properties and the Output allows a developer to configure what sort of data is generated in the given column. Output properties appear below.

Figure 5: Data Generation Column output

Data Generation plans are useful for Testing and Debugging, the next stop in the tour.

Testing and Debugging

To do proper testing with other parts of an application a developer must deploy the database. The Deployment section on the project properties includes some basic configuration information. A snippet from the deployment section appears below.

Figure 6: Deployment Properties

Later in the article I'll show where you can extend and customize deployment scenarios. Debugging can be done with a test database or from a local "Database File". Target Connection options appear below.

Figure 7: Target options

Thus far, all the features I've discussed have been productivity boosts. There are other features extending a developers ability to configure and control database execution context.

Properties

A database design often goes beyond table, view, function, and stored procedure definitions. Assumptions about case sensitivity and how NULL is handled play a large role in the behavior of the code. I think of the collective configurations as the code execution context. A lot of this information is stored in a set of Properties files. Below are the AdventureWorks project Properties.

Figure 8: Database Properties

Earlier I mentioned configuring and customizing deployment scenarios. Many more deployment options can be found under the Database.sqldeployment property. Database.sqlsettings stores things like: NULL handling, Warning levels, Recover mode, and other general database behavior under.

SQL Server Projects have different properties. As you might guess some properties are targeted to the server configuration. Below are properties from a SQL Server Project.

Figure 9: Server Properties

The final feature to discuss is Code Analysis.

Code Analysis

Code Analysis tools have been part of Visual Studio for some time, but have really begun to shine in Visual Studio 2008. I've always been intrigued by analysis results and code analysis potential. Though Analysis is not a silver bullet for maintaining supportable code, it can bolster what a developer may know intuitively, but may not have "hard facts" to support. Code analysis can also be embedded in "check-in" and "check-out" policies.

Analysis has been extended to TSQL code. Analysis options can be configured on the Project properties. Analysis properties appear below.

Figure 10: Configure Analysis

Executing Code analysis generates a report. A developer can configure how forcefully to apply code analysis or remove an analysis rule altogether.

Conclusion

Visual Studio 2010 includes a feature rich database development project template for the SQL Database as well as the server. New features include an improved editor, testing, deployment options, and code analysis.

Resources

About the Author

Jeffrey Juday

Jeff is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server.
Jeff has been developing software with Microsoft tools for more than 15 years in a variety of industries including: military, manufacturing, financial services, management consulting, and computer security.
Jeff is a Microsoft BizTalk MVP.
Jeff spends his spare time with his wife Sherrill and daughter Alexandra.

Comments

How can I create folder for newly created database ?

Posted by Nauman Ikram
on 09/14/2012 01:10am

need to know how can i pass instructions to create folders for each databse on remote database server in deployment scripts.
Regards,

At present, the most commonly deployed parallel file system is Lustre, and its adoption is growing across the HPC industry. According to Intersect 360 Research, "Through its leadership and involvement with OpenSFS, Cray advocates for the development of features that drive efficient performance at scale." Moreover, with help from Cray and OpenSFS, Lustre is gaining greater adoption across broader commercial application categories. As data and I/O requirements grow in commercial markets, technology vendors -- …