Share via

Presentation Transcript: Introducing SQL Server Data Tools for Visual Studio 2010

  • Article

SQL11UPD04-TSCRIPT-01

This wiki is a transcript of a previously recorded video.

Related content assets:

  • Presentation: Introducing SQL Server Data Tools for Visual Studio 2010 (SQL11UPD04-DECK-01)
  • Video: Introducing SQL Server Data Tools for Visual Studio 2010 (SQL11UPD04-REC-01)

Introducing SQL Server Data Tools for Visual Studio 2010

Hi, I’m Greg Low, a SQL Server MVP and part of the Microsoft Regional Director Program. In this section, I want to spend a few minutes having a look at SQL Server Data Tools as introduced in SQL Server 2012.

 

First up, SQL Server Data Tools will be a new name to most people. And there are two sets of tools that are basically supplied with this. First up, there’s an evolution of the existing business intelligence development studio, and so when you deploy SQL Server, if you choose to have any of the business intelligence pieces, what we will end up with is new templates installed inside Visual Studio that provide and are branded as SQL Server Data Tools and give you options for analysis services, integration services, reporting services, and so on. So you’ll go looking for them now as SQL Server Data Tools.

 

But in addition to this, we’ve got a new or next evolution of tools we’ve been using for database development, where previously you may have used Visual Studio Team Addition for Database Professionals or similar tools. And so, this is now a new evolution of this. When you install the SQL Server Tools, what you will find is that you have a stub in your menus that allows you to go off and install these tools using the Web Installer technology, the Web Platform Installer. For people that need to do off-line admin installs, also the intent is to have off-line installable options there. But the aim with this sort of tooling is to be able to have a way of refreshing it easily on an ongoing basis. So most developers won’t ever need to install SQL Server Management Studio. The aim here is to continue to create a set of tools that would allow people to do all their sort of development along with their database development and higher language level development in the same environment without needing to go off and use their other tools.

 

In a disconnected development model, this allows us to build while we’re disconnected from the server and we’ve had the beginnings of this with previous versions of the tools, and the same sort of richness continues on. And so we have things like code analysis – or, basically, like static code analysis, often people would call it – so this allows me to do things like I could have a rule that says, you know, even though you can write “select asterisk from a table”, I’d really rather you didn’t do that . And so, or perhaps I may have a rule that says I want to make sure that all of the column names are qualified, perhaps with an alias, and so on. You can build and you can have these sorts of rules in place and determine that they will be used, and go through, and have it go through your code, and find any of the places where that needs to be corrected.

 

If things are located that need to be modified, we also have some refactoring options available. These allow you, for example, to do things like expand wildcards and that says, when you find this sort of thing like an asterisk, perhaps let’s replace that with a set of columns. Now, each time you do that you get a preview to make sure that what you are going to end up with is what you’re after there. IntelliSense has been a real boon and a real nice addition to the product along the way. This works both off-line and online. We also have a connected model, so if I wish to execute queries, look at query execution plans, and so on, I can do this from inside the same set of tools without having to open up query windows in SQL Server Management Studio.

 

And finally, we’ve had support for the data-tier applications that were introduced in SQL Server 2008 R2. Now the data-tier application model has been enriched in this and the output from those database projects can either be data-tier applications, but it’s still possible to generate scripts if that’s what you’d prefer instead.

 

A new addition to this tooling is SQL Server Object Explorer. So, similar to the Object Explorer we have inside SQL Server Management Studio, there’s now a way of drilling down in amongst server objects, SQL Server objects in particular, inside our tooling with SQL Server Data Tools. This supports – you see in the example I’ve got here – that I’ve got a connection to server called KIWI, and I’ve looked at the database that’s sitting underneath that, but notice I’ve also got an instance of LocalDb, which is the attached version of SQL Server Express. And this can be launched and run from within Visual Studio, and can be used for local debugging. But much of the functionality that we’ve had in SQL Server Management Studio and Object Explorer is directly available now inside SQL Server Object Explorer, inside SQL Server Data Tools.

 

Another nice aspect of this is the multi-mode table editor. In previous versions, if I created a new table, what I had was, just effectively, a template for the table. Now what we have here, if you note what I’ve got in the screenshot, is I’ve opened up a table called Product.sql and note that I’ve got sort of a GUI-based version of this. I can see this in a nice grid, and I can set properties and so on. And I also get a summary that shows me how the table is constructed with things like keys, check constraints, indexes, foreign keys, triggers and so on. Down the bottom, though, I have the T-SQL equivalent of the same thing. And also, in the properties window, I’ve got details here that could relate to this. So, the identity column, for example, is Product Domain. Now the nice thing with this is that if I edit any of these windows, the changes ripple through and affect the other windows. So if I wander up here and change the data type, for example, of a column, you’ll find that down here immediately in the T-SQL, that change also occurs down there.

 

Now the projects that you’re building, the aim is to try and get to a point where I can build once and deploy against multiple targets. But what this allows us to do is to set a target platform for our project as part of our project properties. And note here I’ve got SQL Server 2012, I’ve got earlier versions of SQL Server, 2005, 2008 and R2, but in addition, I’ve got SQL Azure. What I can do with this is go and say, this is the platform I’m targeting for this project, and then when I do a build, I’ll get warnings if – or errors – that tell me if I’m using things that are not supported by the platform that I’m targeting at this point. The output of this can also be a script or it can be DACPACs. And notice the option down here at the bottom here – create additional downlevel DACPAC file. That allows me to create a DACPAC file, which is the package file for a data-tier application, in this case to target SQL Azure.

 

In an upcoming demonstration, we’ll take a look at Declarative Database Development using these SQL Server Data Tools.

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]