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.

In the previous post we saw how to create a new project using SSDT. In this entry we’ll see how to import an existing database into the project. Start by right clicking on the project (not the solution) and pick Import, Database.

The Import Database is similar to the one from the 2010 database projects, but simplified. Use the New Connection button to setup a connection to your database (here I picked Adventure Works 2012). Target Project is disabled, since it’s in the context of the current project.

Import settings can be left at their defaults. The one thing to note is the Folder structure drop down. I personally prefer the default of Schema\Object Type. You can also pick None, which will put all the SQL files in the root of the project. I wouldn’t recommend this option, as it will quickly get difficult to find the files you need to edit. You can also organize by just Schema, or just Object Type. If you are a hard core DBA you might find Object Type more comfortable, since it’s closer to the Object Explorer in SSMS. As I said though, my experience has been Schema\Object Type is the easiest to work with.

When it’s done just click finish, and you’ll see the new structure in the Solution Explorer. Each folder at the top level represents a Schema, or database level object such as Database Triggers.

In the image below, you can see I expanded two of the schemas, HumanResources and Person. Under these are folders for all of the present object types.

Note that the HumanResources schema has a folder for Stored Procedures, while Person does not. This is simply because in the database the Person schema has no stored procedures. If you want to add a stored procedure to the Person schema, you’ll want to add a folder to the Person structure and name it Stored Procedures. This isn’t required, you can put the SQL file anywhere you want, but if you mimic the existing organization structure you’ll make it much easier to maintain and expand the SSDT project as you move forward.

Lets expand a branch to see all the files.

Finally! We’ve drilled down to the lowest level and can see the individual files that are needed to make up the project.

In the next installment we’ll look at altering some of the database settings. Over the next few weeks we’ll be looking at deployment tools, database snapshots, and how to edit the various file types, and some of the enhancements there, especially around the table editor.