Using the MS-SQL Server Enterprise Manager to Create a User-Defined Data Type

You learned about SQL data types in Tips 21–26, and you used them when you learned to create a table in Tip 46, "Using the CREATE TABLE Statement to Create Tables." As you now know, each table column must have a data type that defines the type of data you can put into the column. For example, if a column is of type INTEGER, you can store only whole numbers—characters and numbers with a decimal point are not allowed. Similarly, when you define a column as being of type CHAR(10), you know that it can hold up to 10 characters, symbols, or numeric digits.

A user-defined data type lets you use one of the standard SQL data types or domain you've created to define a descriptive name for the type of data a user will find in a column you define as being of that (user-defined) data type. Suppose, for example, that you were working with the REGULAR_PAY_RATE column in an EMPLOYEE table; you could define the column's data type as NUMERIC(5,2), or you could use a more descriptive user-defined data type such as HOURLY_PAY_RATE.

To use Enterprise Manager to create a user-defined data type, 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 create the data type, 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 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 create the data type. 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 User-Defined Data Types. Enterprise Manager will display the existing user-defined data types in the right pane of the application window.

7. Select the Action menu New User-Defined Data Type option. The Enterprise Manager will display a User-Defined Data Type Properties dialog box similar to that shown in Figure 53.1.

   

8. Figure 53.1: The Enterprise Manager User-Defined Data Type Properties dialog box

9. Enter the name of the data type into the Name field. For the current example, enter hourly_pay_rate.

10. Click your mouse on the drop-down list button to the right of the Data Type field to list the available SQL data types and select one for your user-defined data type. (Your are not actually creating a new data type. Rather, you are simply applying a descriptive name to an existing SQL data type.) For the current example, select money.

11. If you want to allow NULL values for columns of your user-defined data type, click your mouse on the All NULLS check box until the check mark appears. For the current example, click your mouse on the check box until the check mark appears—you want to allow a NULL value for the hourly pay rate if the employee is salaried or gets paid only on commission.

12. If you want to use a database rule to apply a constraint to limit the values a user can enter into columns defined as being of the data type you are defining, use the drop-down list button to the right of the Rule field to display the list of database rules and select the one you want. (You will learn how to create Rules in Tip 195, "Using the Transact-SQL CREATE RULE Statement to Create an MS-SQL Server Rule.") For the current example, select (none).

13. If you want the DBMS to supply a default value when a user does not provide a value when inserting rows that include columns defined as being of the data type you are defining, use the drop-down list button to the right of the Default Name field to display the list of defined defaults, and select the one you want. For the current example, select (none).

14. Click your mouse on the OK button.

After you complete Step 13, the Enterprise Manager will store your data type definition in the DBMS system tables. You can then use the data type you defined in the database anywhere you can use a standard SQL data type. In the current example, the SQL statement

 CREATE TABLE employee
 (id               INTEGER,
  name             VARCHAR(35),
  regular_pay_rate hourly_pay_rate)

would be valid once you performed the steps to create the HOURLY_PAY_RATE data type.

Note

User-defined data type names in a database must be unique by owner and must be defined in the database in which you want to use them. For example, if you define HOURLY_PAY_RATE in the SQLTips database, you must also define it in the Northwind database if you want to use HOURLY_PAY_RATE as a data type for columns in both SQLTips database tables and Northwind database tables.