SQL Server Data Tools in Visual Studio 2012–Table Designer

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.

SNAGHTML28a093

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.

SNAGHTML2ae15a

You are now presented with the spiffy new table designer. Using it is fairly straightforward, but has some nice abilities.

image

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!

image

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.

image

Now your designer window should look something like:

image

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: