SQL Server Data Tools in SQL Server 2012 – Part 2

Introduction

As developers, we have always wanted to have a single Integrated Development Environment (IDE) for not only writing application code but to also to write database code in a single place without switching the environments. SQL Server Data Tool (SSDT) is a new feature in SQL Server 2012 that provides this capability primarily intended for developers and lets developers write their application as well as database code in a single development environment of Visual Studio.

In my last article, I talked in detail about SQL Server Data Tools and how you can get started to use it in disconnected mode, along with all of the possibilities that it provides while working in disconnected mode. In this article, I am going demonstrate how you can use it in connected mode and also I am going to talk in detail about some of different common features that are available both in Disconnected and Connected modes.

Getting Started with SQL Server Data Tool (SSDT) – Connected Mode

SSDT allows you to work in connected mode for a SQL Server instance, both on or off-premise. SSDT includes SQL Server Object Explorer, which offers a view of your database objects similar to SQL Server Management Studio. It allows you to do design and development work along with light-duty database administration. With this, you can easily create, edit, rename and delete tables, stored procedures, types, and functions. You can also edit table data, compare schemas, or execute queries by using contextual menus right from the SQL Server Object Explorer.

To work in connected mode, SSDT has introduced a new window called “SQL Server Object Explorer” and in order to launch it you need to go to the View menu, and click on SQL Server Object Explorer as shown below; (you can even use short cut key to launch it – CTRL+\, CTRL+S):

SQL Server Object Explorer
SQL Server Object Explorer

In the SQL Server Object Explorer, you can click on the Add SQL Server menu item or icon to connect to the SQL Server instance you want to work with:

Add SQL Server
Add SQL Server

Once you get connected to the SQL Server instance you will be able to browse all the databases and objects you have permissions on as shown below:

Browse Databases and Objects
Browse Databases and Objects

Common Features That Work in Either of the Modes Discussed

Whether you are working in connected mode or disconnected mode there are some features that work seamlessly and give you a similar experience, as discussed next.

Schema Comparison

The schema comparison feature allows you to compare schemas of two SQL Server databases, two projects or two DACPACs (Data Tier application) or combination of these. To launch the Schema Comparison Wizard, go to Schema Compare under the SQL menu and click on New Schema Comparison as shown below:

Schema Compare
Schema Compare

If you are working in disconnected mode, you can right click on the Database Project name and click on Schema Compare to launch the Schema Comparison wizard as shown below:

Disconnected Mode - Schema Compare
Disconnected Mode – Schema Compare

If you are working in connected mode, you can right click on the Database name and click on Schema Compare to launch Schema Comparison wizard as shown below:

Connected Mode - Schema Compare
Connected Mode – Schema Compare

The benefit of using the second and third option is by default, the database project or the database will be selected as the source database and saves you from one more step to take.

There might be certain types of objects that you would like to consider during comparison or you might like to specify some settings, which need to be used during comparison; to specify all of this information you can go to the Schema Compare Options window and change it as per your requirement:

Schema Compare Options
Schema Compare Options

Database Project Snapshot

One of the nicest features is creating a snapshot of a database project; it helps developers to create a snapshot of the database project before making changes (especially if it is going to be a big change) to the database project and once the change is done, it does the schema comparison to validate the right changes and publish the changes if it looks good during validation. Right click on the project and click on Snapshot Project menu item as shown below:

Snapshot Project
Snapshot Project

This will create a database project snapshot to that point of time (when it was created) in the form of dacpac (Data Tier application with <Project Name>_YYYYMMDD_HH-MM-SS.dacpac naming pattern) as shown below:

.dacpac
.dacpac

Now you can go ahead and do the changes required, run the schema comparison to see what changes were done since the time you created the database project snapshot. For example, after creating the database project snapshot I made some changes in one of the stored procedures and here is what I got after schema comparison:

 Schema Comparison Results
Schema Comparison Results

In cases where you think the changes are not valid or not good and want to revert back you have the database project snapshot to do so.

Multi-mode Editing of Objects

When you start creating or modifying an object there are three different ways or modes to do so. Based on your past experience or comfort level you might like to use T-SQL, using designer or use the Properties window and the best part is no matter where you are making changes, the rest of the other view gets synchronized instantaneously.  

Design Mode
Design Mode

By default, the table designer pane si on the top and T-SQL is on the bottom. Some people are more comfortable with writing T-SQL code and would like to have the T-SQL pane on the top; in order to swap the view you can click on the Swap icon available between these two panes and after this, screen will look like this:

Design Mode - T-SQL on Top
Design Mode – T-SQL on Top

You can right click on the header of the table designer if you want more columns to be included in the view:

Design Mode - Additional Columns
Design Mode – Additional Columns

Static Code Analysis

Static Code Analysis lets you analyze your code based on rules defined to ensure your code is not violating the rules and its adhering to the defined standard. I will talk in detail about code analysis in some other article though for now, if you want to enable running code analysis when building your database project, you can go to the Properties page of the project and click on the Code Analysis tab on the left and Check the “Enable Code Analysis on Build” checkbox and then you specify which rule to use during analysis. For example, one good practice is not to use SELECT * but rather use specific columns to be selected and there is a rule for that.

Code Analysis
Code Analysis

By default all of the violations will be shown as warnings and if you want them to be treated as errors you can check the checkboxes as shown in the image above for the respective rule.

Database Unit Testing

SSDT introduces database a unit testing feature in database project develop, debug and execute database unit tests interactively in Visual Studio, which can then be run from the command line or from a build machine. Detailed discussion on this topic is beyond the scope of this article though you can find more details about it here.

Code Refactoring

SSDT brings some code refactoring capability for database developers writing database code in a database project.
Code Refactoring Capability

For example, suppose you have SELECT * statement, and as because it is not a recommended practice you would like specific column names to be specified with the SELECT statement, this is where code-refactoring can help you. Select the SELECT statement, right click on this and then click Expand Wildcards under Refactor as shown above. The result of this action has been shown in the below image:

Expand Wildcards Results
Expand Wildcards Results

Dependency Tracking

One of the nicest feature is creating a snapshot of a database project; SSDT helps developers to create a snapshot

Some of the features discussed above were part of the earlier release as well and hence if you are using an earlier version and want to use these features please get these details from online documentation.

Conclusion

In my last article, I talked in detail about SQL Server Data Tools and how you can get started using it in disconnected mode with all of the possibilities that it provides while working in disconnected mode. In this article, I demonstrated how you can use it in connected mode and also I talked in detail about some of different common features that are available both in Disconnected and Connected modes.

Resources

Data Tier Application

SQL Server Data Tools (SSDT)

SQL Server Data Tools Team Blog

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles