Today I’m presenting SQL Server Data Tools in Visual Studio 2012. While the bulk of the information can be found in the blog posts over the last few weeks, I wanted to upload the slide deck. You’ll find it here:
A new feature of SSDT, one not found in VS Database Projects, is the ability to do snapshots. A snapshot captures a copy of your database project as it exists at the time you take the snapshot. Once you have a snapshot there are several useful things you can do with them.
Taking a snapshot is simplicity itself. Using the same project we’ve been using over the last few posts, right click on the project name, and pick “Snapshot Project” on the menu.
Once you do, you’ll see a new file appear in the solution. You’ll be given the chance to rename it, but for this demo I’ll just take the default name.
So now that you have a snapshot, how do you use it? Well let’s start with a database comparison. First, let’s make a change of some type to the database. I’m going to use the safe refactor (see my blog post on this). I’m going to open the Employee table in the HumanResources schema and safe refactor the JobTitle column to become JobName.
With the file saved, let’s now decide we want to compare our current database project to what we’ve done in the past, namely our snapshot. The same tools we used for schema compare can also be used with a snapshot.
Right click on the snapshot and pick Schema Compare (for more info see my previous post). When the schema compare window appears the snapshot will be in the source side. Over on the right, use the pick target to pick the current project, then click compare. You will now see the differences between your snapshot and your current project.
So looking at differences is nice, but what if you want to dig in and see the entire snapshot? If you’ll notice, the snapshot is created as a dacpac file. If you happened to notice in the earlier post on creating an SSDT project, in addition to importing from an existing database, we also have the option to import from a dacpac file.
Right click on the solution, and pick Add, New Project.
Now pick a new SSDT project and give it a name. I’m going to name mine after the snapshot. Now right click on the project, and pick Import. then pick “Data-tier Application (*.dacpac).
Now navigate to the snapshot dacpac file you created and import it. (Hint, to find out where it’s at, before you start the import right click on the snapshot file, and pick properties. One of the properties is “FullPath”, it will hold the full path / file name of the snapshot.)
Once you click start, your new project will have an exact copy of your project at the time the snapshot was created.
Move down the AdvWorks_20120821_07-19-43 project tree and open up the Human Resources schema, then the Employee table in both projects. Assuming you were following along you will now see the snapshot as the Job field as JobTitle, the name prior to the change.
Within our AdvWorks project, also navigate down to the Employees table, and you’ll see it has the new name of JobName for the job column.
While snapshots will capture versions of your database over time, be aware they are not a substitute for good source code control. Snapshots are manually created, and are part of the project. Source control will capture each version upon check in, but more importantly serves as a good back up.
Snapshots can also be useful when asking for help. Simply take a snapshot and e-mail it to your friend. They can simply import it and create a copy of your project. Much easier than trying to zip up the entire project and mail it around.
A feature carried over, but improved upon, from Visual Studio Database Projects is the Schema Comparison tool. The tool allows you to compare one database to another, a database to your project (or vice versa). It will also allow you to do comparisons between dacpacs and projects or databases (or them to dacpacs).
Doing one is pretty simple. We’ll keep using the project we’ve been using in previous lessons. For today’s example I’ve done a safe refactor on the JobTitle column in the HumanResources.Employee table, renaming it to JobName. I have also added a table and view to the dbo schema. For your example simply rename something in your project. After you make the changes, don’t publish them! We need something different for this example.
To kick off the schema compare, go to the SQL menu, then pick Schema Compare, New Schema Comparison. The dialog will be mostly empty, we’ll start with the upper portion.
As you see, on the left we hit the drop down and will pick Select Source.
Here you have three choices. The top is to pick a project as your source. The second will let you pick an existing database as a source. The final choice will allow you to pick a dacpac file. For this example, we’ll pick the database that we created from our AdvWorks project, one prior to the changes you just made.
On the right, hit the drop down and for the target pick your AdvWorks project. Now click the Compare button in the upper left. SSDT will do the comparison and populate a dialog with the results.
In the upper half you will see a list of all the changes found. If you click on a change, the Object Definition area in the lower half populates with the code that creates the objects, and highlights the differences. In this example you’ll see that our source system has the JobTitle column, whereas the project on the right has our change to JobName.
The check boxes allow you to select or deselect individual changes. For example, you could go to the upper half which is designated to delete the ArcaneCode table and view and uncheck them. Then when you apply the changes these would be left untouched.
If you have a lot of changes you wish to omit, you can buik apply the exclusion (and likewise the inclusion) of files. Right click on a grouping (here the groups are changes and deletes), in the popup menu you can choose to Include All or Exclude All.
To apply the changes, simply click the Update button.
You could have a situation that could result in data loss, for example the deletion of a table. By default SSDT will block any changes that will result in data loss in the target. Your target, however, might be a test database in which you don’t care if you lose data. There may also be other options you wish to override.
To see the options for applying updates, click on the gear immediately to the left of the grouping toolbar button.
Here you can see a vast list of options available to you, that will affect the way in which SSDT applies updates to the target. You can see the “Block on possible data loss” option under the mouse, and could uncheck it to force your changes. There’s a lot of options here, so scroll through the list to see what other options you might be interested in.
Between the dropdowns for source and target is a little double arrow symbol. Click it will swap the source and target. Do it now, so the database now becomes the target and the project becomes the source. Now run the Compare again.
Click it an a new window will appear with all the T-SQL that will change the target database to make it in sync with the project.
Let me stress something. This is not the way you should apply changes to your databases! The publish feature is the proper way to do that, in it are options to generate incremental updates.
This option is more for times when you have a test database you want to quickly get in sync, but for whatever reasons don’t want to create a full publish profile.
The Schema Comparison tool is surprisingly useful. A true story, I was working on a project one time that had considerable changes to an existing database. It was a short term project, so not a lot of time. In theory the source database was supposed to be left unchanged. Note I say “in theory”.
Bright and early Monday morning the very new to the job DBA comes to us and says “Oh by the way we had some issues over the weekend so I had to apply a bunch of changes.” When we asked for his scripts he just shrugged and said “I didn’t keep any of that junk. The changes are in the database just go get ‘em.” And with that wandered away, presumably to provide more sunshine to the lives of other dev teams.
At one time this would have been a major set-back. Fortunately the Schema Comparison tool quickly brought our project up to date. We were able to see the database changes before we applied them to our project, and in a few cases exclude the automatic changes and instead make them manually in our project.
Play around with the Schema Comparison tool, you can run it without actually applying changes. Knowing how to use it will help you on that fateful day when a new dba spreads a little sunshine into your own life.
With this post I want to show you a few of the nice shortcuts provided to you in Visual Studio SSDT for quickly customizing the layout of your designers. A few of the items only apply to the table designer, but many apply to other windows within Visual Studio, no matter what project type is being hosted.
Number 1 points to the pane swap button. Clicking it will simply swap the positions of the grid and T-SQL windows, like so:
The double bar pointed to by number 2 is the resizing handle. Click and drag to adjust the amount of space used by either pane.
Note the change of the cursor shape when it’s hovered over the double bars.
There are three buttons pointed to by number 3. The middle one is the default, and indicates you want to split the panes horizontally. If you click the left most of these 3, it will split the panes vertically.
Vertical mode is really nice when you have a super wide screen monitor. As you can see, the three buttons have now shifted to the bottom center of the screen, next to the mouse in the above image.
What if you are working on a really small screen, and don’t even have enough real estate to work comfortably with any size split? Well that’s where the right (or bottom if vertically split) button comes in. Click it to shift to tabbed mode. (Note, I suggest you shift back to the default horizontal split first, otherwise the tabs will be on the right instead of the bottom and not quite as easy to use).
The last button, number 4, is for the T-SQL pane. It’s also found in almost all code editor windows in Visual Studio. Using it you can split the code view so you can see two different sections of you code at the same time.
Great for working with especially large code bases. And this split should exist in any text editor, not just the designer. Whether it’s straight T-SQL, VB.Net, C++, F#, or C# it should work for you.
For a typical desktop user, you’ll probably set these once and forget. But for folks like me who travel a lot, these are a real blessing. When I’m at home, with my laptop hooked up to my 25inch wide screen monitor, I can quickly shift to split screen vertical mode to take advantage of all that width.
When I’m on the road though, working on my laptops small screen (12 inches), I can shift back to horizontal mode, or more often (for me) tabbed mode, for doing my work.
Experiment with different layouts, and find out what works best for you!
With Visual Studio Database Projects (VSDB), you entered into Safe Refactoring mode through the Schema View window. But in SSDT, the Schema View window no longer exists. So how the heck do you do it??
Well first off, let’s define safe refactoring for those who may not have been familiar with the feature in VSDB. It allowed you to right click on a column name, and pick rename. You could then enter a new name, and hit the preview button. Visual Studio would comb through your source code and find every occurrence of that column name for that table and show it to you. If you clicked OK, Visual Studio would then go through and make the change everywhere for you, ensuring you didn’t miss anything.
Before we can do a safe refactor, we need to have something to refactor, and to test with. For this demo, we’ll create a simple view to read the dbo.ArcaneCode table we created in the previous post. Since there is no folder in the dbo schema for views, we’ll first have to create it.
Still working with our AdvWorks sample, open the dbo folder. Right click on it, and pick Add, Folder. Name the folder Views. Let me interject that strictly speaking, this isn’t a requirement. We could have placed the new view source file we’re about to create anywhere. But keeping a file/folder structure consistent with this and other SSDT projects will make maintenance far easier.
OK, once the folder is there, right click on it, again pick add, only this time pick a new View. Name the view dbo.vwArcaneCode.
CREATE VIEW [dbo].[vwArcaneCode] AS
SELECT [BlogUrl], [BlogAuthor]
And save it. Now return to the table designer for the new ArcaneCode table.
Let’s decide we don’t like the name BlogUrl, and we’d rather call it BlogSite. Well, first we could try putting our cursor into the T-SQL area, and just typing over BlogUrl with BlogSite. What happens?
Well, as you can see above, the designer on the upper half changes, but the column name in Create Index did not update. Not nearly what we wanted.
Let’s start over. Restore the name in the T-SQL area back to BlogUrl. Now go into the grid at the top, and change it there.
Hey, that’s better! Once we tabbed out of the Name column (or clicked elsewhere) it changed the name in all the locations in the T-SQL area. Just what we wanted!
Or is it?
Remember, we also used this column in the vwArcaneCode. Go take a look at its code.
As you can see, it remained unchanged. Even worse, there’s now a big red squiggly under the BlogUrl name, indicating we have now created an error in our project. Sigh. OK, one more time.
Go back to the table designer, and reset the name back to BlogUrl. Now we’re finally ready to do this the right way.
Put your cursor somewhere in the column name in the T-SQL area, then right click on the column name. Note, this will not work if you try it in the grid area at the top!
From the menu, pick Refactor, Rename.
You’ll now see a dialog appear, giving you a place to enter a new name. Change the name to BlogSite, and make sure the Preview Changes is checked on, then click OK.
Once you do, a new window will appear. It will show you everywhere the change would be made.
Looking at the window, the upper part shows you all the files and the line of code in the file for which a change would be made. If you click on a line, the new version of the code will appear in the lower window. Ahh, there’s the view, and you can see it is being shown with the new change that will be made.
Note that no change has occurred as of yet. You must click the Apply button for anything to actually change. You can also click Cancel to abandon the change.
If you need to know everywhere a column name is used, but don’t want to change it, there’s an easy way to do that too. When we right clicked on the column name and picked Refactor, well a few menu options down was another option called “Find All References”. Picking that will populate a window in your Visual Studio environment.
When you click on a line in the above window, your central display in Visual Studio changes to now show the file you clicked on in this window.
There you go, you can now not only safely refactor column names in Visual Studio 2012 SSDT, but you can also find all references to that column within your project.
Above is the screen shot of where things were left at the end of the previous post. We had just added a new table and used the designer to create four columns, along with their data types. We also saw how we can edit the table in T-SQL and see updates in the designer’s grid above.
But what’s all that stuff to the right of the grid? It would seem to list the various objects that would be associated with a table. In this case there’s only one, the unnamed Primary Key. But through this are we can also add new objects.
Let’s add a simple index. Right click on the Indexes (0) area. from the menu pick Add New, then for this example we’ll pick a standard Index.
When you do, a new index name appears below the Indexes (0) area, with a default name.
Since this is going to be for the BlogUrl, I’m going to change the name to IX_ArcaneCode_BlogUrl. You’ll see the Indexes collection area has updated to reflect the new name, and the number in parenthesis has been updated to (1), to reflect the number of Indexes.
In the T-SQL area at the bottom, you’ll see some new T-SQL setup and ready for you to update. Here, all you have to do is change the [Column] to be the actual field name you wish to use in the index. In this case, [BlogUrl].
Think of the new designer as a combination of easy to use UI with a code generator. Creating other objects works just like you’ve seen here. You right click, pick what you want to add, and Visual Studio SSDT inserts the code template read for you to update.
One of most noticeable enhancements to the data tools (over the previous database projects) is the table designer. Using the AdvWorks project we started in previous posts, let’s add a new table. Since the dbo schema has few tables, let’s add it there.
Expand the dbo schema, right click on the Tables folder, right click and pick Add, Table as you can see in this illustration.
Next you’ll be asked to confirm the type of object you wish to add, and what you want to name it. Ensure the “Table” object type is selected (the red arrow points it out below). Then, give your new table a good name. If you use multiple schemas in your database (and you should) then get into the habit of always typing in the schema name before the table name, even if it’s the default schema. This will prevent you from putting tables into the wrong schema, then having to clean up the mess later.
You are now presented with the spiffy new table designer. Using it is fairly straightforward, but has some nice abilities.
You can begin by simply going to the Name area, and typing in new column names. I’m going to start by changing the word Id to ArcaneId. Next, move to the Data Type box and hit the dropdown. You’ll be presented with a dizzying array of data types!
For now I’ll leave it as int, since this will be my primary key, but I’ll add other types momentarily. I’ll leave Allow Nulls off, as well as leaving the default empty. Now add a column by moving down to the next row in the grid, perhaps call it BlogUrl, nvarchar(256). Note that when you pick the nvarchar column type, you’ll have to type right inside the Data Type text area to change the length of the column. Finally add a DateUpdated column, Date data type, an set the default to GETDATE().
Note that as you’ve filled in your columns in the designer, the T-SQL in the box underneath is also updating. It’s a two way street, shift down to the T-SQL code on the bottom. Let’s add a fourth column, but put it under the BlogUrl but above the DateUpdated. Let’s name it BlogAuthor, nvarchar(256), NULL (we’ll allow nulls) and no default.
When you get to the end of the line and VS has confirmed this is valid T-SQL code, it will update the designer area on the top to reflect what you’ve done below.
There is one more thing we should do, something that’s quite common especially in data warehousing. We should have the primary key be an Identity type, that is a column whose value auto-increments with each inserted record. We can’t do that via the designer area at the top. While we could move down to the T-SQL area at the bottom and just type it in, there is a way to do it graphically.
In the designer, click on the row with the ArcaneId. Now go to the Properties window (generally over on the right, below the Solution Explorer if you still have the default VS seutp). About 2/3 the way down you’ll see a property called Identity Specification. Using the + button expand it, then change the Is Identity property to true.
Now your designer window should look something like:
But that’s just the start, for this isn’t just a table designer, but a designer for keys, constraints, indexes, and more! But that will wait for the next post in the series.