Using the MS-SQL Server Enterprise Manager to Bind a Default to a Data Type or Table Column

Before MS-SQL Server will use a default value you've created, you must bind the default to a table column or a user-defined data type. In Tip 50, "Using the MS-SQL Server Stored Procedure sp_bindefault to Bind a User-Created Default to a Table Column," you learned how to use the stored procedure sp_bindefault to bind a default to a table column. In this tip, you will learn how to use the Enterprise Manager.

To use Enterprise Manager to bind a default to a table column, perform the following steps:

1. To start the Enterprise Manager, click your mouse 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 your mouse on the plus (+) to the left of SQL Server Group.

3. To display the list of resources on the SQL Server with the database in which you wish to bind the default to a column, click your mouse 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 your mouse 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 your mouse on the plus (+) to the left of the Databases folder. The Enterprise Manager will expand the database branch of the SQL Server list to show the list of databases on the SQL Server you selected in Step 3.

5. Click your mouse on the plus (+) to the left of the database in which you wish to bind the default. For the current example, click your mouse on the plus (+) to the left of SQLTips (if you created the database in Tip 41, "Using the CREATE DATABASE Statement to Create an MS-SQL Server Database and Transaction Log"). (If you don't have an SQLTips database, click your mouse on the plus (+) to the left of Northwind, the example database.) Enterprise Manager will display a list of database object types.

6. Click your mouse on the Defaults icon in the list of database object types. Enterprise Manager will use its right pane to display the list of user-defined defaults in the database you selected in Step 5, similar to that shown in Figure 54.1.

   

7. Figure 54.1: The Enterprise Manager application window displaying the user-defined defaults for a database

8. Double-click your mouse on the name of the default you want to bind to a table column. For the current example, double-click your mouse on ud_minimum_wage (if you created the UD_MINIMUM_WAGE default in Tip 52, "Using the MS-SQL Server Enterprise Manager to Create a Default for a User-Defined Data Type or Table Column"). Enterprise Manager will display a Default Properties dialog box similar to that shown in Figure 54.2.

   

9. Figure 54.2: The Default Properties dialog box after selecting the UD_MINIMUM_WAGE default

10. Click your mouse on the Bind Columns button. Enterprise Manager will display a Bind Default to Columns dialog box similar to that shown in Figure 54.3.

    

11. Figure 54.3: The Bind Default to Columns dialog box

12. Click your mouse on the drop-down list button to the right of the Table field to display the list of tables in the database. Click your mouse on the table with the column to which you want to bind a default. For the current example, click your mouse on the EMPLOYEE table. Enterprise Manager will display a list of the table's columns in the Unbound columns list.

13. Find the column to which you wish to bind the default in the Unbound defaults list along the lower-left side of the Bind Default to Columns dialog box, and click your mouse on it. For the current example, click your mouse on REGULAR_PAY_RATE.

14. Click your mouse on the ADD button. Enterprise Manager will add the REGULAR_PAY_RATE column to the Bound columns list. (Since the left list shows only unbound columns, you will see that Enterprise Manager removed REGULAR_PAY_RATE from the Unbound columns list when it placed the column name in the Bound columns list.)

15. Click your mouse on the OK button. Enterprise Manager will return to the Default Properties dialog box.

After you complete Step 12, Enterprise Manager will note your default bindings in the database system tables. The DBMS will then set the column to the default value whenever a user adds a row to the table without giving the value of the column to which you've bound a default.

If you later decide that you no longer want the DBMS to supply a default for a particular column execute the stored procedure sp_unbindefault, or perform Steps 1–9 of the procedure to bind a default to a column. Then, instead of selecting an unbound column in Step 10, select the column you want to unbind from the Bound columns list in the lower-right side of the Bind Default to Columns dialog box. Next, in Step 11, click your mouse on the Remove button. Finally, finish unbinding the default by clicking on the OK button in Step 12.

In addition to binding defaults to table columns, you can also bind a default to a user-defined data type. Once you do so, the DBMS will supply the default value instead of NULL whenever a user does not supply a value for a table column defined as being of the data type to which you've bound the default.

For example, to bind the UD_MINIMUM_WAGE default to the HOURLY_PAY_RATE data type you defined in Tip 53, "Using the MS-SQL Server Enterprise Manager to Create a User-Defined Data Type," perform the following steps:

Note

If you exited the Default Properties dialog box or did not perform the bind default procedure at the beginning of this tip, perform Steps 1–7 of the bind defaults procedure, as necessary, to display the Default Properties dialog box.

1. Click your mouse on the Bind UDTs button on the Default Properties dialog box. Enterprise Manager will display defaults you've defined in a Bind Default to User-Defined Data Types dialog box similar to that shown in Figure 54.4.

   

2. Figure 54.4: The Bind Default to User-Defined Data Types dialog box

3. Find the data type to which you wish to bind the default, and click your mouse on the check box in the Bind column until the check mark appears. For the current example, click your mouse on the check box to the right of the HOURLY_PAY_RATE data type (if you created it in Tip 53).

4. If you want a default value bound only to future columns defined as being of the data type to which you are binding the default, click your mouse on the Future Only check box until the check mark appears. For the current example, leave the Future Only check box clear—the DBMS will then use the default both for columns already declared as being of the HOURLY_PAY_RATE data type as well as those for you will define as being of the data type in the future.

5. Click your mouse on the OK button. Enterprise Manager will note your default bindings in the DBMS system tables and return to the Default Properties dialog box.

If you later decide you that you no longer want the DBMS to supply a default for a particular columns defined as a specific user-defined data type to which you've bound a default, you can execute the stored procedure sp_unbindefault (which you will learn about in Tip 650), or return to the User-Defined Data Types dialog box and clear the Bind check box for the user-defined data type.

When you unbind a default from a data type, you must decide whether or not you want the default to remain bound to existing columns of the data type. If you click a check mark into the Future Only check box, the DBMS will continue supplying the default value for existing columns of the user-defined data type. If you clear the Future Only check box, the DBMS will supply NULL (stop supplying the default value) for both existing columns of the data type and any that you create in the future.

To return to the Enterprise Manager application window, click your mouse on the OK button on the Default Properties dialog box.