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

SQL Saturday 167–Columbus GA–Sept 8 2012

It’s almost here! SQL Saturday #167, the first ever for Columbus GA. It looks to be quite a nice event, with a great variety of topics. There’s a lot of BI, including a PreCon on PowerPivot. I’ll be giving a presentation on Saturday on “The Decoder Ring for Data Warehousing/BI” in which you’ll cut through all the hype to get an understanding of all the words behind data warehousing and business intelligence.

This looks to be a great event, so hurry up and register before it sells out. It seems to be in the perfect location, centrally located (in other words just a few hours drive) between Birmingham, Atlanta, Montgomery, Troy and Dothan. So no more excuses, get your SQL Saturday on!

SQL Server Data Tools in Visual Studio 2012–Publish Database Profile

One of the new features in SSDT, and what I consider to be my favorite, is the Publish Database Profiles. With database projects you could set a multitude of settings, everything from ANSI NULLS to whether to drop and create the database with each build. The only issue was these settings applied to the entire project; you had to change them each time you wanted to deploy to a different server, or to change the rules (overwrite vs. incremental for example).

New with SSDT are Publish profiles. They allow you to establish a set of rules and save them for reuse. To start with, right click on the project name and pick Publish from the menu.

image

You’ll now see a blank publish page.

SNAGHTML360f9789

Let’s start by tweaking some database settings. Click the Advanced button on the lower right.

SNAGHTML36114075

Here you can get to all of the options you can use to fine tune your database deployment. The most common appear at the top, the less changed ones appear in the list below. In this image I’ve checked on the option to Always re-create the database. This option will wipe out the existing database and recreate it from scratch.

Use this particular option with caution, especially if you are doing it to a database you are sharing with your co-workers (or even worse, production!). When your rebuild the database you’ll also lose any data and have to reload. Sometimes this is a good option, especially in the early stages of development when you’ve made massive changes to the database, or perhaps have gone into the database and made a lot of changes outside the scope of SSDT.

There may be other options you need to change, based on your environment or DBA requirements. Once you’ve changed your options click OK to return to the previous screen.

Back on the Publish Database settings dialog I’ll set the target database connection, and the name I want to use for the database. I can also set the output script name if I wish.

 

SNAGHTML36153d0b

Next, I want to be able to save this profile so I can reuse it later. Check on the “Add profile to project” option in the lower left, then click the Save Profile As… button.

SNAGHTML36195ad8

I gave it a good name, and made sure to include the most important options such as RecreateDB to indicate a database recreate was one of the options.

As I write this however, there is a bug with SSDT. When you click the “Add Profile to project” button it immediately adds a profile with the original default name. Then when you click the Save button in the dialog above, it adds the profile again, totally ignoring the name you give it. Instead it uses the default name again, only this time with an _1.

I’ve been assured that this bug is already known and has been fixed, and will be released with the next update to SSDT in VS2012. So depending on when you read this, it may or may not be an issue. Regardless, the fix is very easy, just rename the new .publish.xml file to reflect what you wanted it to be.

Once saved come back and hit Publish. The database will now be deployed to the server and the profile will be added to the solution. Here it is, after I’ve renamed the publish profile.

image

Note that I’ve given it a naming convention that specifies the database name, the target server, and any critical options. Here I’ve added “Overwrite” to indicate what will happen when I run it.

To run it, just double click on it. First, Visual Studio will do a build of the SSDT project. If there are any errors the process will be halted and you’ll need to fix them. If not, you’ll be presented with the publish dialog, this time with everything filled out.

SNAGHTML98453db

All you have to do is click Publish and the database will be created/updated using the options you’d picked previously, to the server which you had previously indicated.

Now for the real fun. Repeat the above steps only this time do NOT check the overwrite database option. Now, (after renaming the new profile) you have two publish profiles to pick from.

image

Take this even further. In my current project I have 8 profiles. An incremental and overwrite option for my local computer, the development server, the user acceptance testing server, and the production server. (In my case it’s a one man project, I’m the developer and the DBA all in one.) No longer do I have to juggle the server name, or even worse do a publish but forget to change the server from production back to local.

By far I think this is my favorite feature in SSDT.

SQL Server Data Tools in Visual Studio 2012–Importing a Database

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.

image

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.

SNAGHTML35019c45

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.

image

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.

image

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.