Demo Transcript: Declarative Database Development with SQL Server Data Tools

SQL11UPD04-TSCRIPT-02

This wiki is a transcript of a previously recorded video.

Declarative Database Development with SQL Server Data Tools

Hi, I’m Greg Low, I’m a SQL Server MVP and part of the Microsoft Regional Director Program. This short demo will show you Declarative Database Development Data Tools, as used in Visual Studio 2010.

So I’ve opened a copy of Visual Studio here and first up, the aim here is to avoid developers having to have multiple tools open at the same time. So we should be able to do all of our SQL Server work, or almost all of our SQL Server work, directly from inside SQL Server without the need to have SQL Server Management Studio open as well.

So here I’ll open a T-SQL file. In this case, I’ve got a script file that I’m going to execute commands against the database, and in this case, create a database. But note first up, down the bottom corner here, that this is a disconnected window at the moment. And even when I’m working in this disconnected mode, I have got features like IntelliSense and so on directly available to me. But I can connect the window up and work against the database as well. So here I’ll connect, and connect to our KIWI server. Notice this has now become a new connected window and I can execute code and everything just like I would if I had a code window in Management Studio. Notice I get my returned number of messages with the number of rows that are affected. In addition to this, I could get execution plans and both actual and estimated plans returned, and so on just like I might if I was using Management Studio as well.

But that isn’t the main story of what we can now do inside these tools. And so, I have a new SQL Server Object Explorer, and this is similar to Object Explorer in Management Studio. And most tasks I could do in there, I can now do in here instead. And so, for example, I notice we have no servers connected at the moment. I’ll say, give me a connection to that same server. And I’ve now got my KIWI server in place and I can drill down, I can see the databases. In fact, this is the database I just created a moment ago. This is the AirportManagement, if I look inside this, I can see the tables that I’ve created and so on with that script. Now, I could create databases here. Note if I right-click I can say, add new database. But importantly, I might want to use this integrated with database projects I’ve built in this environment. Another starting point is I could right-click the database that I’ve created here and say, create new project. I’ll give this a name. I’ll just call this FlightManagement and drop that into my case C:/Temp and select I’d like to create a new solution and give me a directory for that solution. Once I’ve done that, the Wizard will start going off creating a database project, and then it’ll start importing all the objects that will make up the project.

Now that’s – in some ways – the project, is similar to what we had in earlier versions, but now we have much better ways of editing, and so on, within these projects. And so, as another example, if we look in Solution Explorer, we can now drill down and see the tables that have been imported as part of this project. And notice that if I right-click, let’s say the Flights table, and say Open, I now have a multi-mode table editor that I can work with. Now, this has got several modes, and I’ll just momentarily give myself a bit of space in this bottom right corner and show you the Properties window as well. And so I’ve got three different ways of looking at the same data. So for example, if I now decided that Estimated Arrival needed to be a nullable column, note that it is present down here in the T-SQL, but if I change this to say, look, this is now nullable, notice that it’s immediately changed the T-SQL. In addition, in the Properties window, that same property’s been changed. So we have three different ways of editing that same detail and they’re all interrelated. So if I make a change in one, like again I’ll change that in the Properties window, notice it’s updated here in the entry here in the Graphical User Interface and also in the T-SQL window here. So I have multiple ways of doing this. In addition, for that table, I’ve got a summary of keys, constraints, indexes, foreign keys, and so on. Now with this project, once I have this created and to the point I want, if I do a build, to make sure that this is a buildable project, then we’ll make use of another new aspect of this environment. And notice it’s spun up a LocalDb instance. Now we talk about LocalDb in another demonstration, but this has spun up a LocalDb version of SQL Server Express with an instance that it’s named after the project.

Finally, the projects that we create with this, if I go to the Properties of the project, you see that I can choose the target deployment platform I want to work against, including SQL Azure and earlier versions of SQL Server. And so, if I choose a particular version to work against, the other nice aspect of this is that my build process would stop me from using features accidentally that aren’t supported by the target project.

I hope you find these new options to be very useful.

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