Using the MS-SQL Server Enterprise Manager to Create Tables

In addition to typing CREATE TABLE statements into the MS-SQL Server Query Analyzer's Query pane or at the ISQL (or OSQL) Ready prompts, MS-SQL Server gives you a GUI tool you can use. To create a table using the Enterprise Manager, perform the following steps:

1. To start the Enterprise Manager, click on the Start button, move your mouse pointer to Programs on the Start menu, select Microsoft SQL Server 7.0, and click your mouse on Enterprise Manager.

2. To display the list of SQL servers, click on the plus (+) to the left of SQL Server Group.

3. To display the list of resources on the SQL Server on which you wish to create a table, click on the plus (+) to the left of the SQL Server's name. For example, if you want to work with the SQL Server, NVBizNet2, click on the plus (+) to the left of NVBIZNET2. Enterprise Manager will display a list of folders that represent the resources managed by the SQL Server NVBizNet2 (in the current example).

4. Click on the Database folders. The Enterprise Manager will display the databases on the SQL Server in its right pane.

5. Right-click your mouse on the database in which you wish to create the table. For the current example, right-click your mouse on the SQLTips database. (If you did not create the SQLTips database, right-click your mouse on the TempDB database.) The Enterprise Manager will display a pop-up menu.

6. Move your mouse pointer to New on the pop-up menu, and then select Table. Enterprise Manager will display the Choose Name dialog box.

7. Enter the table name in the Enter a Name for the Table field of the Choose Name dialog box. For the current example, enter Item_Master and then click on the OK button. Enterprise Manager will display the SQL Server Enterprise Manager-New Table window shown in Figure 47.1.

   

8. Figure 47.1: The MS-SQL Server Enterprise Manager New Table window

9. Prepare to enter the first column name by clicking your mouse on the first cell in the Column Name column.

10. Enter the name of the column. For the current example, enter item_number. Next, press the Enter key to move to the input cursors to the Datatype field.

11. Select the field's data type. Either click on the drop-down arrow to the right of the Datatype field and select the data type, or enter the data type into the Data Type field. For the current example, enter INT. Next, press the Enter key to move to the insert cursor to the Length field.

12. If you are working with a character, image, or text type, enter the length of the character. In the current example, you are working with an integer, so the Length field is not applicable. Press the Enter key to move the input cursor to the Precision field.

13. If you are working with a decimal or floating-point (nonwhole) number, enter the total number of digits in the number into the Precision field. In the current example, you are working with an integer, so the precision is set to the default precision for your implementation (which you cannot change). Press the Enter key to move the input cursor to the Scale field.

14. If you are working with a decimal or floating-point (nonwhole) number, enter the number of digits you want to carry to the right of the decimal point into the Scale field. In the current example, you are working with an integer, so the Scale field is not applicable. Press the Enter key to move to the Allow Nulls check box.

15. To allow the field to hold a NULL value, click on the Allow Nulls check box until the check mark appears. For the current example, clear the Allow Nulls check box-every item in the ITEM_MASTER table must have an ITEM_NUMBER.

16. If you want to set the column to a constant default value if you don't supply an explicit value for the column when inserting a row into the table, enter the value into the Default Value field. For the current example, leave the Default Value field blank.

17. To have the DBMS supply an incrementing value for the column if you don't supply an explicit value for the column when inserting a row into the table, click on the Identity check box until the check mark appears. For the current example, click a check mark into the Identity check box-you want the system to supply the item numbers for new items you add to the ITEM_MASTER table. Then press the Enter key to move the input cursor to the Identity Seed field.

18. Enter the first value the DBMS should supply for the column-applicable only if you've identified the column as having the IDENTITY property. For the current example, enter 1000. Then press the Enter key to move the input cursor to the Identity Increment field.

19. Enter the value by which the DBMS is to increment the previous number it supplied for the column when inserting a new table row-applicable only if you've identified the column as having the IDENTITY property. For the current example, enter 100.

20. Click on the next empty cell in the Column Name field to enter another column name.

21. Repeat Steps 9-19 until you've defined all of the columns in your table. For the current example, add a second column named Description, with data type VARCHAR of length 35, which does not allow NULL values. Enterprise Manager will display your table definition similar to that shown in Figure 47.2.

    

22. Figure 47.2: The MS-SQL Server Enterprise Manager New Table window after defining two columns for the ITEM_MASTER table

23. To identity a column as the PRIMARY KEY, right-click your mouse on any field in the column, and select Set Primary Key from the pop-up menu. For the current example, right-click your mouse on ITEM_NUMBER in the Column Name field, and then select Set Primary Key from the pop-up menu.

    Note

    If you want to use a multiple-column (composite) PRIMARY KEY, right-click your mouse on any cell in the table and select Properties from the pop-up menu. The Enterprise Manager will display the Properties dialog box. Click on the Indexes/Keys tab and select the columns you want to include in the PRIMARY KEY in the Column Name list field in the Type area of the Indexes/Keys tab. When you are finished selecting columns for the PRIMARY KEY, click on the Close button. (You will learn more about using Enterprise Manager to create indexes in Tip 162, "Understanding MS-SQL Server CREATE INDEX Statement Options.")

24. To save your table definition, click on the Save button (first button on the left with the floppy disk icon) on the New Table standard toolbar.

25. To close the MS-SQL Server Enterprise Manager New Table window, click on the close button (the X) in the upper-right corner of the application window.

You can use the CREATE TABLE statement (which you learned about in Tip 46, "Using the CREATE TABLE Statement to Create Tables") or the Enterprise Manager GUI New Table tool to create MS-SQL Server tables. Both SQL and GUI allow you to define columns, set constraints, and identify table keys. If you have MS-SQL Server Enterprise Manager installed on your computer, the method you select to create your tables is a matter of personal preference (command line vs. GUI).