As usual, MS-SQL Server has both a command-line Transact-SQL statement way to create a default (which you learned about in Tip 49, "Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default") and a GUI method using the MS-SQL Server Enterprise Manager. The advantages of creating a default outside the CREATE TABLE statement are that you can give the default a meaningful name, use it for user-defined data types or multiple columns in one or more tables, change the default value at any time, or stop using it altogether.
To create a default using the MS-SQL Server Enterprise Manager, perform the following steps:
-
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.
-
To display the list of SQL servers, click your mouse on the plus (+) to the left of SQL Server Group.
-
To display the list of resources on the SQL Server with the database in which you wish to create the default, 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).
-
Click your mouse on the Databases folder. The Enterprise Manager will display the databases on the SQL Server in its right pane.
-
Click your mouse on the icon for the database in which you wish to create the default. For the current example, click your mouse on SQLTips (if you created the database in Tip 41). (If you don't have an SQLTips database, click your mouse on Northwind, the example database.)
-
Select the Action menu New option, and click your mouse on Default. The Enterprise Manager will display a Default Properties dialog box similar to that shown in Figure 52.1.
Figure 52.1: The Enterprise Manager Default Properties dialog box
-
Enter the name of the default into the Name field. For the current example, enter ud_minimum_wage and then press the Tab key.
-
Enter the default value into the Value field. You can enter either a number, a character string, a built-in function, or a mathematical expression. The value cannot include any columns or other database objects. For the current example, enter 7.35 into the Value field.
-
Click your mouse on the OK button. The Enterprise Manager will add the UD_MINIMUM_WAGE default definition to the system tables and close the Default Properties dialog box.
As you learned in Tip 51, "Using the DEFAULT Clause in a CREATE TABLE Statement to Set Default Column Values," you must bind the default value to a column or user-defined data type in order for the DBMS to actually use the default you created. In Tip 51, you used the stored procedure sp_bindefault to bind a default to a table column. In Tip 54, "Using the MS-SQL Server Enterprise Manager to Bind a Default to a Data Type or Table Column," you will learn how to use the Enterprise Manager to bind a default to a user-defined data type and a table column.
No comments:
Post a Comment