Table Design with SSMS

Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart.

However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same results.

Today, I am going to continue the series of articles that will recreate the [AUTOS] sample database while demonstrating how common T-SQL tasks in my script can be done with SSMS. I will be going over section 1I in the sample code with a little literary lead way to go off topic when necessary to demonstrate my ideas.

I want to explore how the CREATE TABLE, ALTER TABLE and DROP TABLE statements can be executed within the SSMS graphical interface.

When ever I write code to be executed in a batch script, I always determine if an object exists before creating it. My code seldom breaks since I delete the object if it exists.

The screen shot below shows an existing [ACTIVE].[MAKES] table in the [AUTOS] database. To delete the table (DROP TABLE), select it in the object explorer and right click. Choose the delete sub-menu option.

This action will bring up the delete object dialog box. It shows the name, type and owner of the object. Double check these values before clicking OKAY to remove the table or click CANCEL to abort the action. For this article, please remove the table since we are going to rebuild it over a series of talks.

To create a new table, select the Tables parent node in the object explorer and right click to bring up the sub-menu. Choose the New Table option.

Every table must have at least one column defined. The table designer allows a developer or administrator to enter in the column name, data type, and all the various options that you can choose.

For our example, we are just going to add the [MAKERS_ID] column to the table as a not null integer. Find the identity specification in the column properties list and change it to yes. It will default to a seed value of 1 and a increment value of 1.

Now that we have a column defined, do you think we can just click save? The answer is no since we want the table to be under the [ACTIVE] security schema and storage to be allocated from the
[FG_AUTOS_ACTIVE] file group.

We need to bring up the properties window of the table to make these selections. Hitting the F4 key will bring up this floating window. Change the Schema and Regular Data Space Specification to the values we want.

The quickest way to save changes to both the table properties and table designer is to click the SAVE ALL button on the tool bar. This action will create (CREATE TABLE) the new simple table that we can build upon.

Table and Indexes are two commonly used objects in a database. Choosing the correct properties of each can lead to either a successful or disastrous design.

Let’s select the new table in the object explorer, right click and select properties. You will notice a dialog box will five different pages is shown.

The first page shows the general properties of the table. This includes schema name, table name, creation date, and system table flag to name a few.

The second page shows the user or role permissions given on the table. This is empty right now since we did not give out rights at the table level. We will explore administering rights with SSMS next time.

The third page shows the change tracking properties of the table. Again, this is empty since we did not enable change tracking on the table in the [AUTOS] database.

The fourth page shows the storage properties of the table. Please note the file group that the table is stored on.

Some other cool things shown in the window are the data and index space used as well as the total number of records. Why execute a SELECT COUNT(*) FROM [ACTIVE].[MAKES] query when the information we want is a right click away, or at least in a system table?

The fifth page shows the extended properties of the table. This is where used defined properties associated with the table can be declared.

If we script out the Microsoft Transaction SQL for the action to add a comment to the table, we get the following command.

Compare this to the syntax used by Oracle PL/SQL. I have to say Oracle wins for simplicity on this battle.

In summary, we can perform many of the Data Definition Language (DDL) tasks with the SQL Server Management Studio (SSMS) graphical interface. I went over the CREATE TABLE and DROP TABLE commands.

We started creating our first table named [ACTIVE].[MAKES]. We will use the ALTER TABLE command behind the scenes to build out our table so that it matches my sample T-SQL script.

Next time, I will be talking about administering table permissions with SSMS.