Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

October 22, 2012

Adding Tables with SSDT

OK, no more SSDT jokes! SSDT, or SQL Server Data Tools, give you the features of SQL Server Management Studio within Visual Studio 2012. You can use the SQL Server Object Explorer from the SSDT to create and maintain tables in your databases.

See this link for an introduction to SQL Server Object Explorer.

See this link for details on creating a new database with SSDT.

To add a new table to a SQL Server database using SQL Server Object Explorer:

1) Open the SQL Server Object Explorer toolbox using View | SQL Server Object Explorer from Visual Studio.

2) Drill down to the Databases node, then to your database, then to Tables.

3) Right-click and select Add New Table…

image

4) Visual Studio creates a new table called "Table" and opens it in the Table designer.

image

At this point you can use the design grid in the upper left panel or the T-SQL in the bottom panel or any combination of both to define your table.

In the example below, the LastName was added in the upper left panel. Notice that Visual Studio automatically generates the matching T-SQL:

image

In this example, the FirstName field was added in the T-SQL. Notice that Visual Studio automatically generates the matching row in the grid:

image

To name the table, edit the Create Table statement in the T-SQL.

image

You can use the Properties window (View | Properties Window) to set additional properties for the fields on the table, such as the Identity information.

image

Looking in the SQL Server Object Explorer, you’ll notice that the new table is not there. All of the database changes are being made in memory using what is called SQL Server Data Tools (SSDT) Power Buffer technology. Seriously! That’s what it is called. Just don’t try to find out more about “Power Buffer” by entering it into Bing or Google!

The Power Buffer is like a scratch pad where you can make any of your database changes without actually altering the database. None of your changes are committed until you specifically select to perform an Update.

When you are done with the table design, click the Update button in the upper left corner. When you click the Update button, all database changes from the Power Buffer are applied, even if the changes encompass different database objects in different designers.

Visual Studio presents a preview dialog:

image

Click Generate Script to create a deployment script containing the T-SQL. Click Update Database to directly update the database with the new table.

The results of the update are displayed in the Data Tools Operations window that, by default, appears on the bottom of the page. It provides links to view details such as detailed results.

image

The SQL Server Object Explorer then displays the new table. You can drill down to the columns, keys, indexes, and so on.

image

A few issues I have had working with the Table designer:

  • There are sometimes issues when the Table designer is not docked to the main window, meaning that it is either floating or docked to another tab raft (say on a second monitor). At some point, the window stops accepting keyboard input. I have to then re-dock the window to type anything more.
  • The only way I was able to get the primary key to be named (instead of unnamed) is to remove the primary key and reset it after naming the table.
  • I was not able to set the table’s name using the Properties window.

Use the SQL Server Object Explorer any time you need to add or edit the table structure of your database.

Enjoy!

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2021 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs