This is the first of two parts on using SQL Server Management Studio Table Designer to create and set properties on SQL Server database tables. This part focuses on creating a table and adding columns; the second part looks at setting database constraints.
More on SQL Server Management Studio
Restrict access using SSMS
Check out the six ultimate SQL Server management tools
See how one reader is having a hanging problem with SSMS
Creating a table in SQL Server can seem daunting, especially if you're new to database development. Not only must you define the columns that make up the table, but also the constraints that restrict the type of data you can insert into those columns. In addition, you must account for schema association, filegroup placement and character collation. To help with this process, you can use SQL Server Management Studio Table Designer, an easy-to-use interface for defining the various components that go into a table's configuration.
Creating the table
To launch SQL Server Management Studio Table Designer, connect to a SQL Server instance, expand the Databases node in Object Explorer, and then expand the database where you want to add the table. (For this article, I use the AdventureWorks2012 database on a local instance of SQL Server 2012.) Once you've expanded your target database, right-click the Tables node and then click New Table. This launches Table Designer in its own window, as shown in Figure 1.
SQL Server Management Studio Table Designer is the center panel that's divided into two panes. You define your initial columns in the top pane, and you configure additional properties for each column in the bottom pane. The properties displayed in the bottom pane are associated with the column selected in the top pane. Because we have not yet defined any columns, no properties appear in the bottom pane.
When using Table Designer, you'll usually want to open the Properties window, which is the window to the right in Figure 1. The Properties window lets you configure properties associated with the table as a whole. For example, you can name your table, provide a description, assign the table to a schema and specify a filegroup. Figure 2 provides a closer look at the Properties window for our new table, after configuring the Name and Schema properties.
At the very least, you'll want to change the Name property to a custom name for your table. By default, SQL Server assigns a name such as Table_1, but for this example, I've changed the name to SpecialtyProducts. In addition, I changed the Schema property from the default, dbo, to the Production schema.
I left the other properties untouched; however, you can modify any option that is not grayed out. For example, you can select a filegroup other than PRIMARY, if one is available, or you can change the lock escalation. As with all the properties available in Table Designer, it's worth taking the time to understand what each one does. If you're questioning what a property means or how it works, refer to SQL Server Books Online for clarification.
Now let's return to the SQL Server Management Studio Table Designer window and start adding our columns. The first one is StoreID, which is configured with the int data type. To add the column, type the column name in the first column in the first row of the grid in the top pane, as shown in Figure 3. For the data type, select int and then deselect the Allow Nulls option. (The Allow Nulls option is selected by default.)
When you add a column to the top grid, the bottom panel displays the column's properties. The Name, Allow Nulls and Data Type properties are repeated in the lower pane. You can modify these properties in either pane. You can also modify any other properties that are not grayed out. For example, you can configure the column as an identity column by expanding the Identity Specification property group and then changing the Is Identity property value to Yes.
The properties you can change depend on the column's data type. Let's add a couple more columns to demonstrate how this works. The next column is ProductID, which, like StoreID has the int data type and nulls not allowed. Next, we'll create the AltID column, which is configured with the char(7) data type and null values permitted, as shown in Figure 4.
If you compare Figure 4 to Figure 3, you'll see that different properties are grayed out. Even properties you can't see are different. For example, you cannot configure the Is Identity property on a character data type, but you can configure the Length, Collation and Is Sparse properties.
Now we'll add the ProductName and DateAdded columns. The ProductName column takes the nvarchar(50) data type and the DateAdded column takes the datetime data type. Neither column permits null values. However, for the DateAdded column we'll define a default value, which in this case is the getdate() function. Figure 5 shows Table Designer with the DateAdded column selected.
Notice that the value for the Default Value or Binding property is getdate(). We use this property to define a DEFAULT constraint on the column. Because the default in this case is the getdate() function, whenever a row is added to the table, the current date and time will be inserted into the DateAdded column unless another value has been specified.
As you can see, adding columns to a table in Table Designer is a straightforward process. At a minimum, you specify the column name and data type, and SQL Server does the rest. However, you can also set a number of other properties, depending on the data type, so you can create columns that meet your business requirements.
To dive deeper, read about adding constraints to tables using SQL Server Management Studio Table Designer.
About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.
This was first published in October 2013