Search Java Programs

Monday, March 1, 2010

Using the ALTER TABLE Statement to Add a Column to a Table

Adding a column to a table is perhaps the most common use of the ALTER TABLE statement. The syntax of the ALTER TABLE statement to add a column is:

 ALTER TABLE 
 ADD   [DEFAULT ]
  [NOT NULL] [IDENTITY][UNIQUE][CHECK ()]
For example, to add a BADGE_NUMBER column to an EMPLOYEE table  defined by
 
 CREATE TABLE employee
 (employee_id            INTEGER,
  first_name             VARCHAR(20),
  last_name              VARCHAR(30),
  social_security_number CHAR(11),
  street_address         VARCHAR(35)
  PRIMARY KEY (employee_id))
 
you could use the ALTER TABLE statement:
 
 ALTER TABLE employee ADD badge_number INTEGER IDENTITY
 
The DBMS will add a new column, BADGE NUMBER, to the EMPLOYEE  table. In addition (MS-SQL Server only), IDENTITY characteristic in the current  example will have MS-SQL Server set the value of the BADGE_NUMBER column in each  row, starting with 1 and incrementing the value by 1 for each subsequent  row.
 
When you use the ALTER TABLE statement to add a new column to a  table, the DBMS adds the column to the end of the table's column definitions,  and it will appear as the rightmost column in subsequent queries. Unless you  specify a default value (or use the IDENTITY constraint on MS-SQL Server), the DBMS assumes NULL for the  value of the new column in existing rows.
 
Since the DBMS assumes NULL for the new column in existing rows,  you cannot simply add the NOT NULL constraint when you use the ALTER TABLE  statement to add a column. If you add the NOT NULL constraint, you must also  provide a default. After all, the DBMS assumes NULL for the new column in  existing rows if you don't provide a default value, and thus would immediately  violate the NOT NULL constraint.
 
When you add column to a table, the DBMS does not actually  expand existing rows. Instead, it expands only the description of the table to  include the new column(s) in the system tables. Each time you ask the DBMS to  read an existing row, it adds one (or more) NULL values for the new column(s)  before presenting query results. The DBMS will add the new column(s) to new rows  and to any existing rows as the DBMS stores updates to them.
Posted by at No comments:

Understanding the ALTER TABLE Statement

No amount of prior planning completely eliminates the need for changing the structure of tables over the course of time. This is not to say that you should spend little time designing tables because they are going to change anyway. Quite the opposite—the more time you spend on design and deciding exactly what data goes in what tables, the less time you will spend later changing your tables and trying to input several months' worth of information that you initially omitted from the database.

That being said, changes to database tables, like changes in life, are inevitable. Perhaps you created your customer table before everyone had an e-mail address (yes, that was not too many years ago). So, now you need to add not only an e-mail address but also a Web (home page) address to the customer record. Or, maybe your company no longer accepts back orders. As such, your inventory table now needs a "minimum_stock_level" column so the DBMS can alert you to order parts from your supplier before you run out and can't take a customer's order. Finally, suppose that your company expands its product line and now makes purchases from multiple vendors. You may need to add a foreign key linking a new "vendor" column in the item master table to a newly created vendors table. None of these changes are necessitated by poor design. The database tables must change because the entities they represent (the business, business rules, and relationships) do not remain constant.

Fortunately, the SQL ALTER TABLE statement allows you to:

  • Add a column to a table

  • Drop a column from a table

  • Change or drop a column's default value

  • Add or drop individual column constraints

  • Change the data type of a column

  • Add or drop a table's primary key

  • Add or drop foreign keys

  • Add or drop table check constraints

The syntax of the ALTER TABLE statement is:

 ALTER TABLE 
 {ADD }
 {[WITH CHECK | WITH NO CHECK] ADD 
}
 {ALTER COLUMN  
    [(precision,scale)][NULL | NOT NULL]}
 {DROP COLUMN 
    [,...,}
 {CHECK | NO CHECK CONSTRAINT [ALL |
    
    [,...,]}
 {ENABLE | DISABLE TRIGGER [ALL |
    
    [,...,]}

 is defined as:
   
  [IDENTITY [(seed,increment)]|[NOT NULL]
  [DEFAULT ]][UNIQUE]
  []

 is defined as:
  CHECK ()

 is defined as:
  [CONSTRAINT ]
       
     | 
     | DEFAULT 
         FOR 
     | CHECK ()

 is defined as:
  PRIMARY KEY ( [, ])

 is defined as:
  FOREIGN KEY () REFERENCES 

  
 
 Note   
Do not include the ellipses ({}) in your ALTER TABLE  statement. The ellipses delineate the different forms of the ALTER TABLE  statement, and you must select one (and only one) of the forms per statement.  Thus,
 
 ALTER TABLE  ADD 
is one valid choice, as is this
 
 ALTER COLUMN  
 [(precision,scale)][NULL | NOT NULL]
 
and so on.
 
Also, do not put the pipe ( I ) symbol in your ALTER TABLE  statement. The pipe symbol indicates "or," meaning you must select one clause or  another. As such, when specifying a table constraint, either define a primary  key, a foreign key, a default, or a check constrain, but not all four.
 
SQL ALTER TABLE statement clauses tend to be very DBMS-specific.  All products allow you to add columns. However, not all products let you drop  columns. (Using the ALTER STATEMENT to drop a column is not part of the SQL-92  specification.) Some products allow you to add and drop individual column  constraints on existing columns; others, including MS-SQL Server, do not.
 
In short, each DBMS vendor adds what it considers to be important  features to the ALTER TABLE statement and removes those clauses more easily  implemented with other vendor-specific constructs. MS-SQL Server, for example,  does not let you use the ALTER TABLE statement to change defaults on or add  defaults to existing columns, even though other DBMS vendors do. Instead, MS-SQL  Server, through Transact-SQL and stored procedures, provides CREATE DEFAULT,  sp_bindefault, sp_unbindefault, and DROP DEFAULT to manage column default apart  from the ALTER TABLE statement.
 
We will discuss many of the ALTER TABLE clauses later in this  book. The important thing to know is that every DBMS has an ALTER TABLE  statement you can use to change a table's structure. Check your DBMS  documentation for the specific clauses available to the ALTER TABLE statement in  your DBMS product.
Posted by at No comments:

Using the Transact-SQL DROP DEFAULT Statement to Remove a Default from a Database

When you no longer need a default you've created, you can use the Transact-SQL DROP DEFAULT statement to permanently remove the default from the database in which you created it. The syntax of the DROP DEFAULT statement is

DROP DEFAULT 
 {, , ... ,]

where the is the name you gave the default when you defined it. As you can see from the syntax of the statement, you can drop multiple defaults with a single DROP DEFAULT.

In Tip 49, "Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default," you used the Transact-SQL statements

 CREATE DEFAULT ud_value_unknown AS "Unknown"
CREATE DEFAULT ud_applied_for AS "Applied for"

to create two defaults: UD_VALUE_UNKNOWN and UD_APPLIED_FOR. If you no longer need these defaults, execute the Transact-SQL statement

 DROP DEFAULT ud_value_unknown, ud_applied_for

to remove them from the database.

One important thing to know is that you cannot drop a default that is currently bound to either columns or user-defined data types. You must first use either the Enterprise Manager or the stored procedure sp_unbindefault to unbind the default from all columns and user-defined data types. Once you've completely unbound the default, you can remove it from the database.

If you create and use defaults, it is essential that you keep an accurate list of them and (perhaps more importantly) the columns and user-defined data types to which they are bound. Unfortunately, the error message the DBMS returns if you try to drop a column with a bound default states only the error and does not identify the bindings that caused it. For example, if you execute the statement

 DROP DEFAULT ud_applied_for

and UD_APPLIED_FOR is bound to a column or data type, the statement will fail and the DBMS will display the error message:

 The default 'ud_applied_for' cannot be dropped because it
is bound to one or more column.

To successfully execute the DROP DEFAULT statement, you will need to run the stored procedure sp_unbindefault. However, sp_unbindefault requires that you supply both the table and the column name in order to unbind a default. Moreover, MS-SQL Server does not have a stored procedure that will list the columns to which a default is bound, so you will need to refer to your documentation.

If you don't have a list of bindings for the default you want to remove, or if you prefer to use a GUI tool, you can use the MS-SQL Server Enterprise Manager to unbind and drop a default. First, perform the first eight steps of the bind default procedure in Tip 54, "Using the MS-SQL Server Enterprise Manager to Bind a Default to a Data Type or Table Column." After you complete Step 8, the DBMS will display the Bind Default to Columns dialog box (refer to Figure 54.3, in Tip 54).

Once you have the Bind Default to Columns dialog box on your screen, perform the following steps to unbind the default:

  1. To find a table with one or more bindings to the default you want to remove, click your mouse on the drop-down list box to the right of the Table field. Enterprise Manager will display the list of database tables in the drop-down list box.

  2. If you know the name of the table you want, select it from the drop-down list. (If you don't know the table you want, you will have to select each of the tables in the database, one at a time.) The Enterprise Manager will display the table columns bound to your default in the Bound Columns list,

  3. To unbind the default from a column, click your mouse on the column name in the Bound Columns list, and then click your mouse on the Remove button.

  4. Repeat Step 3 until you've removed all of the column names from the Bound Columns list.

  5. If your documentation lists other tables to which the default is bound (or if you don't have documentation and you have not made it through the list of database tables), click your mouse on the Apply button and then continue at Step 3 to select the next table with which you want to work.

  6. When you are finished unbinding the default from table columns, click your mouse on the OK button. Enterprise Manager will return to the Default Properties dialog box (similar to that shown in Figure 54.2, in Tip 54).

  7. Click your mouse on the OK button. Enterprise Manager will return to its application window with defaults displayed in the right pane, similar to that shown in Figure 54.1, in Tip 54.

Now that you've unbound the default, you can perform the following steps to delete (drop) the default:

  1. To select the default you want to delete (drop), click your mouse on the name of the default in the list of defaults in the right pane of the Enterprise Manager's application window.

  2. To delete the default, click your mouse on the Delete button (the red X) on the Standard Toolbar (or select the Action menu Delete option). Enterprise Manager will display the name, owner, and type of the default you selected in the Drop Objects dialog box.

  3. Click your mouse on the Drop All button.

To exit Enterprise Manager, click on the application window's close button (the X in the upper-right corner), or select the Console menu Exit option.

Posted by at No comments:

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.

Posted by at No comments:

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.

Posted by at No comments:

Using the MS-SQL Server Enterprise Manager to Create a Default for a User-Defined Data Type or Table Column

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:

  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 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).

  4. Click your mouse on the Databases folder. The Enterprise Manager will display the databases on the SQL Server in its right pane.

  5. 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.)

  6. 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.


  7. Figure 52.1: The Enterprise Manager Default Properties dialog box

  8. Enter the name of the default into the Name field. For the current example, enter ud_minimum_wage and then press the Tab key.

  9. 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.

  10. 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.

Posted by at No comments:

Using the DEFAULT Clause in a CREATE TABLE Statement to Set Default Column Values

A default column value is the character string or number that you want the DBMS to enter into a column when you don't provide a value for the column. You learned how to create default column values in Tip 49, "Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default," and how to bind them to multiple columns in one or more tables in Tip 50, "Using the MS-SQL Server Stored Procedure sp_bindefault to Bind a User-Created Default to a Table Column." Unfortunately, the Transact-SQL CREATE DEFAULT statement and the sp_bindefault stored procedure are available to you only if you are working with MS-SQL Server.

The standard SQL-92 CREATE TABLE statement (available on all SQL relational DBMS products) gives you the ability to define default values for columns when you create a table. Not only is setting default column values standard across DBMS products, but it is also simpler than the Transact-SQL default value creation and binding process.

To define a column default value, simply add the keyword DEFAULT followed by the default value to the column definition in a CREATE TABLE statement. For example, the SQL CREATE TABLE statement

   CREATE TABLE employee
  (employee_ID             INTEGER,
   first_name              VARCHAR(20)  NOT NULL,
   last_name               VARCHAR(30)  NOT NULL,
   social_security_number  CHAR(11)     DEFAULT 'Unknown',
   street_address          VARCHAR(35)  DEFAULT 'Unknown',
   health_card_number      CHAR(15)     DEFAULT 'Applied For',
   sheriff_card_number     CHAR(15)     DEFAULT 'Applied For',
   hourly_rate             NUMERIC(5,2) DEFAULT 10.00,
   bonus_level             INTEGER      DEFAULT 1,
   job_rating_90days       SMALLINT,
   job_rating_180days      SMALLINT,
   job_rating_1year        SMALLINT
   PRIMARY KEY (employee_ID))

defines default values for SOCIAL_SECURITY_NUMBER, STREET_ADDRESS, HEALTH_CARD_NUMBER, SHERIFF_CARD_NUMBER, HOURLY_RATE, and BONUS_LEVEL. As such, when you execute the SQL INSERT statement

 INSERT INTO employee
  (employee_ID, first_name, last_name,
   social_security_number, street_address)
VALUES (1, 'Konrad', 'King', NULL, '77 Sunset Strip')

the DBMS will set the HEALTH_CARD_NUMBER and SHERIFF_CARD_NUMBER columns to "Applied For," the HOURLY_RATE column to 10.00, and the BONUS_LEVEL column to 1. Although the SOCIAL_SECURITY_NUMBER and STREET_ADDRESS columns have defaults, the default values were not used for the columns since the INSERT statement set the SOCIAL_SECURITY_NUMBER column to NULL and the STREET_ADDRESS column to "77 Sunset Strip." Finally, since the CREATE TABLE in the current example, did not define default values for the three job rating columns (JOB_RATING_90DAYS, JOB_RATING_180DAYS, and JOB_RATING_1YEAR), the DBMS will set these columns to NULL.


 Note

Before you set column default values in the CREATE TABLE statement, check to see if your DBMS will allow you to change or stop using the defaults after you've created the table. MS-SQL Server will not let you use the ALTER TABLE statement to add, change, or drop (remove) any column defaults you define in a CREATE TABLE statement. (You can use the ALTER TABLE statement to add a new column and assign a default value to the column. However, once the column is part of a table, you cannot change its default value). If you are using MS-SQL Server, you can get around this shortcoming by using the Transact-SQL CREATE DEFAULT statement to create a named column default object outside the table definition. MS-SQL Server lets you bind a name column default to a column using the sp_bindefault stored procedure. You can change the value of a named column default at any time by unbinding the default from all columns, dropping it, re-creating the column default with a new value, and then rebinding it to the columns in one or more tables.

Posted by at No comments:

Using the MS-SQL Server Stored Procedure sp_bindefault to Bind a User-Created Default to a Table Column

As mentioned in Tip 49, "Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default," you must bind defaults to table columns so the DBMS knows which columns it is supposed to set to which default values. The syntax to use when executing the stored procedure sp_bindefault to bind a default value to a table column is

 EXEC sp_bindefault
 @DEFNAME=,
 @OBJNAME=.
where  is the name you gave the default in  the CREATE DEFAULT statement, and 
. is the  name column in the table for which you want the DBMS to supply the default  value.
 
For example, if you executed the Transact-SQL CREATE DEFAULT  statements
 
 CREATE DEFAULT ud_value_unknown AS "Unknown"
CREATE DEFAULT ud_applied_for AS "Applied for"
 
the DBMS would store the default values UD_VALUE_UNKNOWN and  UD_APPLIED_FOR in the database system tables. Once it's created, you can use the  stored procedure sp_bindefault to bind the defaults to columns in tables (such  as the EMPLOYEE table defined by example in Tip 49).
 
To bind the default ud_value_unknown ("Unknown") to the  SOCIAL_SECURITY_NUMBER column in the EMPLOYEE table, execute the Transact-SQL  statement:
 
 EXEC sp_bindefault
 @defname=ud_value_unknown,
 @objname='employee.[social_security_number]'
 
To bind the default ud_applied_for ("Applied For") to the  SHERIFF_CARD_NUMBER column in the EMPLOYEE table, execute the Transact-SQL  statement:
 
 EXEC sp_bindefault
 @defname=ud_applied_for,
 @objname='employee.[sheriff_card_number]'
 
To bind the default ud_applied_for ("Applied For") to the  HEALTH_CARD_NUMBER column in the EMPLOYEE table, execute the Transact-SQL  statement:
 
 EXEC sp_bindefault
 @defname=ud_applied_for,
 @objname='employee.[health_card_number]'
 
After you bind defaults to the EMPLOYEE table columns, the DBMS  will supply the default value for the default-bound columns when you execute an  INSERT statement on the EMPLOYEE table, such as:
 
 INSERT INTO employee (employee_ID, first_name, last_name)
 VALUES (1, 'Konrad', 'King')
 
In the current example, the DBMS will supply "Unknown" for  SOCIAL_SECURITY_NUMBER, "Applied For" for SHERIFF_CARD_NUMBER and  HEALTH_CARD_NUMBER, and NULL for STREET_ADDRESS.
Posted by at No comments:

Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default

MS-SQL Server lets you create named, default values you can bind to columns and user-defined data types. Once you bind a default to a table column, the DBMS will supply the default value for the column (instead of NULL) if you insert a row that includes the column without specifying its value. The advantages of creating a default outside the CREATE TABLE statement are that you can use a descriptive name for the default, apply the same default to multiple columns in the same or different tables, and change or drop the default at any time.

The syntax of the Transact-SQL CREATE DEFAULT statement is:

 CREATE DEFAULT [.]
AS

Note

Transact-SQL consists of Microsoft's additions to standard SQL. No commercial DBMS product fully supports everything in the SQL-92 standard. Conversely, every vendor adds its own SQL extensions (such as CREATE DEFAULT) and provides procedural language constructs. Microsoft calls its SQL and extensions and procedural language additions Transact-SQL. Oracle uses PL/SQL and SQL *Plus. While most standard SQL-92 code is transportable across DBMS products, specific product extensions (such as Transact-SQL statements) are not. If you need to use a Transact-SQL statement in an Oracle DBMS, check your system manual. You will probably find a PL/SQL statement that performs the same function but has a different name syntax.

Defaults you create must comply with the following rules:

  • You do not have to supply the for the default. However, if you do not, the DBMS will supply your login name as the default for

  • The name you use for the default () must be unique by owner.

  • The must contain only constant values such as numbers, character strings, built-in functions, or mathematical expressions. The cannot include any columns or other database objects.

  • After creating a default, you must use the stored procedure sp_bindefault to bind the default value to a column before the DBMS will supply the value for the column when inserting a row.

  • The default must be compatible with the column to which you bind it. If you bind a character string to a numeric column, for example, the DBMS will generate an error message and not insert the row each time it has to supply the default value for the column.

  • If you supply a character string default for a character column and the default is longer than the column length, the DBMS will truncate the default value to fit into the column.

  • If a column has both a default and a constraint, the default value cannot violate the constraint. If a column's default value violates a column constraint, the DBMS will generate an error message and not insert the row each time it has to supply the default value for the column.

Suppose, for example, you had a table defined by

 CREATE TABLE employee
  (employee_ID            INTEGER,
   first_name             VARCHAR (20),
   last_name              VARCHAR(30),
   social_security_number CHAR(11),
   street_address         VARCHAR(35),
   health_card_number     CHAR(15),
   sheriff_card_number    CHAR(15)
   PRIMARY KEY (employee_ID))

and you want to supply "applied for" and "unknown" in place of NULL values if you don't know the Social Security number, health card number, or sheriff card number when adding a new employee to the EMPLOYEE table. You can create the defaults you need by executing the Transact-SQL statements:

 CREATE DEFAULT ud_value_unknown AS "Unknown"
CREATE DEFAULT ud_applied_for AS "Applied for"

 Note

You can only enter one CREATE DEFAULT statement at a time into the Query Analyzer's Query Pane, or into the ISOQL (or OSOQL) command buffer.

Before the DBMS will use a default, you must execute the sp_bindefault stored procedure to bind the default value to a user-defined data type or a table column. You will learn how to bind default values to a table column in Tip 50 and how to bind a default to a user-defined data type in Tip 594.

You can use the stored procedure sp_help to display a list of user and system-defined defaults. Since sp_help will display all defaults, not just the ones you create, you may want to group all of your defaults together in the list. To do so, use the same first one or two characters for the names of the defaults (such as UD_, short for USER DEFAULTS). Then, when you use sp_help to list the database defaults, the stored procedure will group all of the defaults you create together in its semi-alphabetized list of all defaults.

Posted by at No comments:

Creating MS-SQL Server Temporary Tables

MS-SQL Server lets you create two types of temporary tables: global and local. Local temporary tables are available only to the session in which they are created, and the DBMS automatically erases local temporary tables when the session ends. Global temporary tables are available to multiple database sessions. The DBMS drops a global temporary table after the last user using the table terminates his or her session.

Each login to the database starts a new session. As such, when you use Query Analyzer to attach to a database, you start a session. When you log off or terminate Query Analyzer, the DBMS ends your session. If you're logged in to the database, each time you execute a stored procedure or run an application program that logs in to the same or another database, the DBMS starts a new session. If you log in to the same database a second time, the DBMS keeps your original session open, but information in temporary tables created in the first session is not available to the second session.

Temporary tables are useful when you need to do several operations on the same set of data, such as creating summary reports on a subset of the data from multiple tables. By selecting and combining the raw data you need into a single table, you avoid having the DBMS extract and combine the data multiple times. In addition to eliminating multiple select operations, using a single temporary table increases execution speed because the MS-SQL Server can retrieve data faster from a single table than it can through references to multiple base tables.

To create a local temporary table, start the table name with a single pound (#) sign. As such, executing the statement

CREATE TABLE #customer_orders
 (customer_number INTEGER,
  customer_name   VARCHAR (35),
  order_date      DATETIME,
  amount          MONEY)

will create a local temporary table. The #CUSTOMER_ORDERS table is accessible only to the person who created it. Moreover, the DBMS will automatically DROP the table when the user logs out.

If you want to create a global temporary table, start the table name with two pound (#) signs. Thus, if you want to create a temporary table that is accessible to multiple users (and sessions), use a double pound (#) sign with a CREATE TABLE statement similar to:

 CREATE TABLE ##customer_orders
  (customer_number INTEGER,
   customer_name   VARCHAR (35),
   order_date      DATE_TIME,
   amount          MONEY)

The DBMS will not DROP the global temporary table until the last user that referenced the table during his or her session logs out.

Posted by at No comments:

SQL Tips and Techniques

SQL Tips and Techniques - Chapter 1



SQL Tips and Techniques - Chapter 2


Posted by at No comments:
Labels:

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).

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)

Website Design by Mayuri Multimedia