Ms SQL Server 2000 administrator manual series-15. Use Transact-SQL and Enterprise Manager to manage data tables

15. Use Transact-SQL and Enterprise Manager to manage data tables
Modifying a data table with a T-SQL
Use Enterprise Manager to modify data tables
Impact of modifying a data table
Delete A data table
Summary
In Chapter 10th, you have learned how to create a data table by defining data rows and data types. Once a data table is created, it may be modified, even if the data table already exists. This chapter describes how to modify a data table, including modifying, adding, deleting, and renaming data rows, and deleting the entire data table. Create and modify conditions for a data table (one way to ensure data integrity) and trigger programs (a special type of pre-stored program that is automatically executed under certain conditions) it will be introduced in chapter 16th and Chapter 22nd.
In this chapter, we will look at two ways to manage data tables: Using Transact-SQL (T-SQL) or Microsoft SQL Server 2000 Enterprise Manager. Note that the T-SQL and Enterprise Manager provide different elasticity when you modify a data table. Using Enterprise Manager is more advantageous than T-SQL because it is easier to use enterprise manager when you perform some specific changes. If you try to make an improper modification, Enterprise Manager will display an error message or provide suggestions. However, T-SQL offers the benefit that if you execute commands in the form of a script, you can have a record that tracks how you change data.
Before the official start, we need to create two data tables in the mydb Database: bicycle_sales and bicycle_inventory as examples used in this chapter. The data table bicycle_sales contains the sales information of the bicycle dealer, including the following data rows: make_id, model_id, description, year, sale_id, price, quantity, and sale_date. The make_id and model_id data rows are specified as external index key condition constraints. The make_id and model_id data rows in the bicycle_inventory data table are referenced to form a unique Cluster Index. As described in chapter 16th, the external index key condition constraints can only reference one primary key data row in the data table or reference other data rows with unique constraints (constraints will be detailed in chapter 16th, the index will be introduced in chapter 17th ).
The sale_id data row is declared as the primary key cluster index of the bicycle_sales data table. The create table used to create these data tables is described as follows:
Use mydb
Go
Create Table bicycle_inventory
(
Make_name char (10) Not null,
Make_id tinyint not null,
Model_name char (12) not null,
Model_id tinyint not null,
In_stock tinyint not null,
On_order tinyint null,
Constraint mi_clu_indx
Unique clustered (make_id, model_id)
)
Go

Create Table bicycle_sales
(
Make_id tinyint not null, -- used in foreign
-- Key constraint
Model_id tinyint not null, -- Also used in foreign
-- Key constraint
Description char (30) null,
Year char (4) not null,
Sale_id int not null identity (1, 1) primary key clustered,
Price smallmoney not null,
Quantity tinyint not null,
Sale_date datetime not null,
Constraint sales_inventory_fk foreign key (make_id, model_id)
References bicycle_inventory (make_id, model_id)
)
Go
________________________________________
Note:
Be sure to create a bicycle_inventory data table before creating a bicycle_sales data table. If you want to create a data table named "bicycle_sales", an error message is displayed. The bicycle_sales data table uses condition constraints to refer to the bicycle_inventory data table. Therefore, if the bicycle_inventory data table does not exist, a condition constraint cannot be created, and an error is returned.
________________________________________
Now we have created a data table for the sample database. Below we will make several modifications to them. Use the T-SQL first and then Enterprise Manager.
Modifying a data table with a T-SQL
　
In this section, you will learn how to use the T-SQL command to modify, add, delete, and rename data rows in an existing table. The T-SQL command for modifying a data table is alter table.
Modify data rows
　
Once a data table is created, you can use the alter table command to modify the data type, precision (For numeric type), and whether to allow null values (nullability) of the data row ), you can also add or delete the rowguidcol attribute of a Data row. Other modifications can be made by using other T-SQL commands, such as adding a new default value, which will be described in chapter 16th.
Not all data rows can be modified. Generally, the following types of data rows cannot be modified:
• Data rows that are part of the primary index key or one of the external index key conditions
　
• Data row for rewrite (For details, refer to Chapter 26th)
　
• Data rows with text, ntext, image, timestamp, and other data types
　
• Calculate data rows or data rows used for calculation
　
• A rowguidcol data row (however, you can add or remove the rowguidcol attribute of the Data row)
　
• Use data rows in the Index
　
• Data row used for check or unique condition constraints (condition constraints are described in chapter 16th)
　
• Data rows used to execute statistics using the create statistics Statement (SQL Server generates statistics that can be deleted using ALTER TABLE .)
　
• Data rows related to default values
　
All other data rows can be modified using the alter table statement. In the previous examples, you can remove the restriction before modifying the data row. For example, you can remove the external index key or other condition constraints of the Data row, or delete the index. If there are no other restrictions, you can modify the data row.
Modify Data Type
　
To modify the Data Type of a Data row, the original data type must be converted to a new data type. The list of data types that can be converted can be found in the topic "cast" and "convert" in books online. To obtain this topic, search for Cast in online books, and then select cast and convert in the displayed topic dialog box.
The command syntax for modifying the Data Type of a Data row is as follows:
Alter table <table_name>
Alter column <column_name> <new_data_type>
You can reduce the space used by each data column. As an example in this chapter, you only need to change the data type of the sale_date data row of the bicycle_sales data table from datatime to smalldatetime. Because datatime occupies 8 bytes, while smalldatetime only needs 4 bytes. To make this modification, run the following command:
Alter table bicycle_sales
Alter column sale_date smalldatetime not null
Go
Any existing data table data is converted to the new data type smalldatetime. This data row does not change if the allowed null property value-not null.
To change the data type of the description data row from Char (30) to varchar (20) (short), run the following command:
Alter table bicycle_sales
Alter column description varchar (20) null
Go
Note that the original data type char (30) has been changed to the new data type varchar (20), because varchar (20) is short, if the description data row in the original data column contains a value of more than 20 characters, it will be truncated and converted to varchar (20) without any warning ).
________________________________________
Description
When the modified data type in a data row is shorter than the original data type, and the value in the existing data column exceeds the new length, it is automatically truncated to the new row length.
________________________________________
Modify whether the null attribute is allowed
　
In addition to the data rows in the primary index key condition constraints, you can change the allowed null attribute of the Data row from not null to null. (The primary index key condition does not allow null values for such data rows .) You can also change the NULL data row to not null, but this data row must have no null value. If there is a null value in the Data row, you must first execute the update statement to change the null value to another value to change whether the null attribute of the Data row can be changed from null to not null. If the modified data row does not specify whether to allow null, the default value is null. Let's look at some examples.
To change the quantity data row to allow null values, execute the following statement:
Alter table bicycle_sales
Alter column quantity tinyint null
Go
The data type of the Data row tinyint remains unchanged, and only whether the null attribute of the Data row can be modified. Currently, the quantity data row allows the insertion of null values. If no value is entered, null is automatically inserted. This change does not affect the value of the quantity data row in the existing data column, but the row of quantity data in the newly added data column in the data table can be inserted null.
Now let's change the description data row to not null. We assume that there are some null values in this data row. Therefore, we must first set the null value to another value-in this example, we change null to none, so that it is compatible with the Data Type of this data row. To test the null value, using the T-SQL keyword is null or is not null is safer than using an equal operator (=. This is because null is an unknown value, and whether a equals operator (=) can compare to a pair of null values depends on whether the ANSI nulls database option is set to on or off. If it is set to off, the expression that holds the null value (expression = NULL) will return true, and the expression that is not null will return false. If this option is set to on, all similar comparison expressions = NULL will be returned to unknown, and no result set will be returned. As you may have guessed, when this option is set to on, SQL server does not return any null values. Using the keyword "is null" and "is not null" can achieve the same purpose without worrying about the setting of ansi_nulls. To change the null value of the description data row to none, you can use the update set statement as follows:
Update bicycle_sales set description = "NONE"
Where description is null
Go
Change the descripition row to not null:
Alter table bicycle_sales
Alter column description char (30) not null
Go
Similarly, we did not change the original data type char (30), but only changed whether to allow the null attribute. You can modify the data type and whether the null attribute is allowed in a single alter table command at the same time, as shown below:
Alter table bicycle_sales
Alter column description varchar (20) not null
Go
This statement modifies the Data Type of the descripition data row and whether the null attribute is allowed.
Adds or removes the rowguidcol attribute.
　
To add the rowguidcol attribute or remove the rowguidcol attribute of a Data row, you can use the following syntax:
Alter table <table_name>
Alter column <column_name> Add | drop rowguidcol
You can only add the rowguidcol attribute to the data row with the Data Type uniqueidentifier. Assume that there is a unique_id uniqueidentifier row in our bicycle_sales data table. You can use the following command to add the rowguidcol attribute:
Alter table bicycle_sales
Alter column unique_id add rowguidcol
Go
You can use the following command to remove the rowguidcol attribute:
Alter table bicycle_sales
Alter column unique_id drop rowguidcol
Go
Add data row
　
You can also use the alter table command to add data rows to a data table. Whether you create or modify a data table, the definition of data rows is similar. You must specify the name and type of the Data row, and specify other features, attributes, and condition constraints.
When you add a new data row not null, you must also declare a default value so that existing data columns can save the default value to the new data row. You can use the keyword default to specify the default value. To add a data row, use the following syntax:
Alter table <table_name>
Add <column_name> <data_type> <nullability>
Default default_value
For example, to add a data row named salesperson_id to the bicycle_sales data table, run the following command (the new data row does not allow null values and the default value is 0 ):
Alter table bicycle_sales
Add salesperson_id tinyint not null
Default 0
Go
Since the data row is declared as not null, the default value is 0 for new data rows in all existing data columns in the data table.
If the salesperson_id data behavior of the newly added data table is null, the default value is optional, as shown below:
Alter table bicycle_sales
Add salesperson_id tinyint null
Default 0 -- Optional Default Value
Go
Even if the default value is specified, null will still be assigned to the salesperson_id data row of the existing data column-the default value will only take effect in the newly inserted data column.
To force existing data columns to be saved to the default value 0 rather than null, use the default with values option, as shown below:
Alter table bicycle_sales
Add salesperson_id tinyint null
Default 0 with values
Go
With values specifies that the new default value will be stored in the new data row of the existing data column, rather than null.
Delete A data row
　
You can also use the alter table command to detach data rows from a data table. All data on the detached data row is deleted from the data table. However, the following types of data rows cannot be detached by the T-SQL:
• Data rows used for primary index keys, external index keys, uniqe, or check condition Constraints
　
• Data rows for rewrite
　
• Data rows used for indexing (unless the index has been removed first)
　
• Fasten the data row to the rule
　
• Data rows related to default values
　
________________________________________
Description
When Enterprise Manager is used to unload data, these restrictions still apply, but they are handled differently. Later in this chapter, the "use Enterprise Manager to modify data tables" section describes in detail.
________________________________________
To remove data rows from a data table, use the following syntax:
Alter table <table_name>
Drop column <column_name>
The following command can remove the description data line from the bicycle_sales data table:
Alter table bicycle_sales
Drop column description
Go
Description data rows and memory values in all data columns of the data table will be deleted.
________________________________________
Note:
Be especially careful when removing documents. If you do not have a backup database, you cannot restore the data in the data row. Even if the transaction occurs after the backup, you need to execute the transaction record file to reply to the database. You can also recreate data rows and store new values.
________________________________________
Data row rename
　
To rename a data row using a T-SQL, you must use the following syntax to execute the sp_rename system pre-stored program:
Sp_rename 'table. original_column_name ', 'new _ column_name', 'column'
For example, to rename the description data row as bicycle_desc, you can run the following command:
Sp_rename 'bicycle _ sales. description', 'bicycle _ desc', 'column'
Go
The original data row name must be in table. column format, but the new data row name does not need to contain the data table name.
Use Enterprise Manager to modify data tables
　
As mentioned earlier, using Enterprise Manager to modify data tables is easier and more flexible than using T-SQL. You can use the design data table window or database chart to complete all the modifications. Let's take a look at how to design the data table window. To open the design data table window for our bicycle_sales data table, follow these steps:
1. Expand the database mydb data folder in the left pane of Enterprise Manager.
2. Click the data table to display the list of all data tables in the mydb database in the right pane, 15-1.
　
 
Figure 15-1 Enterprise Manager
3. Click the right button on the bicycle_sales table in the right pane. Select Design data table from the shortcut menu to open the design data table window, 15-2. This window displays the original unmodified bicycle_sales data table.
　
 
Figure 15-2 design data table window
Modify data rows
　
To modify data rows in the design data table window, just click the appropriate cell or check box in the pane and make the necessary modifications. Each column in the square represents each row in the data table. The title above the square represents the attribute set for each cell.
In some situations, the T-SQL may not allow you to perform specific changes and return error messages, but Enterprise Manager provides messages that guide you through the modification correctly. For example, if you try to use the T-SQL command alter table to modify the Data Length of a Data row that happens to have primary or external index key constraints, SQL Server Returns an error message similar to the following:
Server: Message 5074, level 16, status 4, Row 1
The 'sales _ inventory_fk 'object is dependent on the 'Make _ id' data row.
Server: Message 4922, level 16, status 1, Row 1
Alter table alter column make_id has failed because one or more objects access this
Data row.
However, if we use enterprise manager, a message box that allows you to modify the Data Length of a Data row will appear.
For example, to change the data type of the Data row make_id (this data row has an external index key constraint for reference to the make_id of the bicycle_inventory data table) from tinyint to smallint, click the arrow to display the Data Type drop-down menu, and then select smallint, 15-3.

　
 
Figure 15-3 Use Enterprise Manager to change the data type of a Data row
Because make_id has an external index key constraint, the "Change Data Type" dialog box appears, 15-4. Click here, the make_id data rows of the two data tables are automatically changed from tinyint to smallint.

　
 
Figure 15-4 dialog box for Data Type Change
Like a T-SQL, when you use Enterprise Manager to modify a data type, the original data type must be converted to a new data type. If you try to perform an improper conversion, Enterprise Manager returns an error message similar to figure 15-5. Figure 15-5 shows the conversion result that does not comply with the rules. This change attempts to change the data type of the sale_date data row from datetime to text. Click OK to close the error message and select a data type that can be converted.

　
 
Figure 15-5 the following error message is displayed when you try to change the data type to a type that cannot be implicitly converted
To save your changes, click the Save button in the design data table window tool column. The storage dialog box then confirms whether the listed data tables are indeed written to the disk, as shown in Figure 15-6. Click here to confirm that you want to store your changes.

　
 
Figure 15-6 Storage dialog box
Add data row
　
To add a data row, click the name field of the Data row in the first blank column in the design data table window, enter the name of the Data row of the new data row, and select its data type, and assign appropriate attributes (whether to allow null, default value, recognition, and so on) to it. As shown in Figure 15-7, a data row named salesperson_id is added. The data type is tinyint. null is allowed, and the default value is 0. Click the Save button to save your changes. SQL Server adds a new row to the data table.

　
 
Figure 15-7 Add a row named salesperson_id
Delete A data row
　
The process of using Enterprise Manager to unload or delete data rows is quite simple. In the design data table window, select the data row name or any attribute of the Data row to be deleted (any bucket in the same column as the data row name) and press the right button, select Delete data row from the shortcut menu. Remember to save your changes by pressing the Save button.
When the row you are trying to delete is part of a constraint or index, or has a default value or a binding rule, Enterprise Manager warns. You will see a message box similar to figure 15-8. Click here to delete the data row and all the associations of the Data row.

　
 
Figure 15-8 if you try to delete a data row associated with another data row or data table, the displayed message box
Create and use database charts
　
You can also use the database charts in Enterprise Manager to modify data tables. To create a database chart of two sample data tables in mydb: bicycle_sales and bicycle_inventory, follow these steps:
1. Expand mydb in the left pane of Enterprise Manager and press the right button on the chart. Select Add database chart from the shortcut menu to display the welcome to use the create database chart wizard screen, 15-9.
　
 
Figure 15-9 welcome to create a database chart wizard Screen
2. Click Next to display the screen of the data table to be added, 15-10. Select a data table that you want to include in the chart from the list of available data tables, and click Add. In this example, we add the bicycle_inventory and bicycle_sales data tables.
　
 
Figure 15-10 select the data table to be added
3. Click Next to display and complete the create database chart wizard screen. If the selected data table is correct, click here to complete the change, or click the previous step to make the necessary changes.
4. Click it to view your database chart, which is 15-11.
5. Click the Save button and enter a name to save your database chart.
In our chart, there is a vertical line connecting two data tables and ending with the key pattern, showing the external index key constraint associations between the two data tables. To display the associated volume label, press the right button in the background of the window and select the associated volume label from the shortcut menu. The name of the external index key condition constraint is displayed, 15-12.

　
 
Figure 15-11 database chart examples

　
 
Figure 15-12 shows the correlation label of a data table
To select a data table, click it on the data table. To select more than one data table, press and hold the ctrl key, and click on each data table. If you press the right button on a data table and select an option in the shortcut menu, all selected data tables will perform the same action. For example, if we select two data tables in the database chart and press the right button on one of the data tables, then select the data table view and standard from the shortcut menu, both data tables display all data row attributes, 15-13.

　
 
Figure 15-13 display data row attributes from a database chart
Each data table shows the data row attributes, which is similar to the design data table window and can be modified in the same way as the design data table window. You can simply add or change row data in the required storage space. Click the Save button to save your changes. You can also move or change the visible area of the data table in the database chart, or make the data table have different appearances and other changes. Try different commands in the shortcut menu. Note that once you modify a data table, an asterisk is displayed after the data table name to indicate that the data table has been changed.
Impact of modifying a data table
　
When you modify a data table, it immediately changes existing data after you execute the T-SQL command alter table (or after you use Enterprise Manager and store changes. When the modification is in progress, SQL Server places a lock on the data table to prevent other users from accessing the data table. For large databases, if modified, all existing data columns will be changed. For example, adding a non-null data row with a default value or removing a data row, proceed with caution-because this action may take some time, even if the number of users simultaneously accessed is small. When you modify a data row to change its Data Length, precision, or decimal number of words, the data table needs to be rebuilt in the database and the existing data will be converted to a new data type.
If a system fault or fatal error occurs during the modification, all data table changes are recorded and completely recoverable. However, once you complete the modification, you can only restore the database from the backup and return the data table to restore the original state (Backup and Restore the database will be described in Chapter 32nd and Chapter 33 ).
Delete A data table
　
When you delete a data table, the definition of the data table and the data, indexes, conditions, triggers, and permissions associated with the data table will be deleted. The checklist and pre-stored procedures for deleting a data table must also be explicitly deleted. If there are external index key constraints in other data tables that reference this data table, the data table cannot be detached-in other words, if a data table is an object dependent on other data tables, it cannot be deleted. The data table can be detached only when the conditional constraint or referenced data table is deleted first. On the other hand, a data table can still be detached if no other data table depends on it even if it holds the external index key condition. This section describes how to use T-SQL and Enterprise Manager to delete data tables.
Delete data tables with T-SQL
　
The T-SQL command syntax for deleting a data table is as follows:
Drop table <table_name>
Use drop view or drop procedure to delete the View tables and pre-stored programs that reference the deletion of data tables. You can delete the View tables or pre-stored programs before or after deleting the data tables. Once a data table is detached, you cannot retrieve it-you must recreate the entire data table, data, and everything else.
To remove our example data table bicycle_inventory, because the bicycle_sales data table has an external index key constraint reference to bicycle_inventory, we must first remove the external index key constraint sales_inventory_fk before deleting the bicycle_inventory data table, as follows:
Alter table bicycle_sales
Drop constraint sales_inventory_fk
Go
Drop table bicycle_inventory
Go
If you try to delete the data table without removing the external index key condition constraint, an error message is returned without any deletion action.
Use Enterprise Manager to delete a data table
　
Enterprise Manager provides two methods to delete a data table: Use the unload object dialog box or use a database chart. When the data table you want to delete is not dependent on other data tables, the delete object dialog box is better. To delete a data table in this way, follow these steps:
1. expand the database in the left-side pane of Enterprise Manager (This database contains the data table you want to detach), click the data table, and then find the data table to detach in the right pane, press the right button on the data table name.
2. Select Delete from the shortcut menu to display the unload object dialog box, 15-14.
　
 
Figure 15-14 "delete objects" dialog box
3. If the data table contains any dependent data tables, the dependency dialog box is displayed, 15-15. Any data table that depends on this data table is displayed in the list on the left of the dialog box. If there are any dependent data tables, you cannot delete them before they are removed.
4. If no other data tables depend on the selected data tables, you can delete the selected data tables in the delete objects dialog box.
　
 
Figure 15-15 "dependency" dialog box of the bicycle_sales data table
To delete a data table with dependent data tables, we will use the second method: Database charts. In this example, we will remove the bicycle_inventory data table. The focus of the removal is that the bicycle_sales data table has an external index key constraints. See the data table. If we use the delete object dialog box, the delete operation will fail. However, if we use a database chart that shows the two data tables, we can remove the bicycle_inventory data table and the external index key constraints will be automatically removed. To delete a bicycle_inventory data table, follow these steps:
1. Expand the database you want to use in Enterprise Manager, click the chart, find the appropriate chart in the right pane, and click twice on the chart name to enable the database chart. Select the name of the data table you want to delete. In this example, the name is bicycle_inventory.
2. Click the right button on any location of the data table and select Delete from database from the shortcut menu. When you are prompted to permanently delete the data table from the database, click yes. Data Tables and external index key condition constraints are removed from the chart.
3. If you are sure to permanently delete the data table, click the Save button to save your change. The data table is then deleted from the database. If you have changed your mind after deletion, just click Close and leave the edit chart window without storing the changes. If you enable the database chart again later, you will find that the original data still exists. No change will take effect unless you store your work.
Summary
　
In this chapter, you have learned how to use T-SQL and Enterprise Manager to modify, add, detach, and rename data rows for modifying database data tables. The functional differences between T-SQL and Enterprise Manager are gradually presented in our learning. You have also learned how to use the create database chart wizard to create a database chart, and how to delete data tables and all data in the table from the database. For data tables, we can also make other changes, including modifying, adding, and removing constraints and default values. These changes will be introduced in chapter 16th.