15. Use Transact-SQL and Enterprise Manager management information sheets
Use T-SQL to modify the information sheet
Use Enterprise Manager to modify the information sheet
The impact caused by the modification of the data sheet
Delete information list
Summary of this chapter
In Chapter 10, you have learned how to build a data sheet through definition data lines and data type. Once a data sheet is established, it is of course possible to modify, even when the data has been present in the data sheet. This chapter describes several ways to modify the data sheet, including modifications, new, delete, and renamed the data line and delete tangible data list. Establish and modify the conditional constraints of the data sheet (a method of ensuring data integrity) and trigger procedure (a special type of pre-preserved program that automatically execute under certain conditions) will be introduced in Chapter 16 and 22.
In this chapter, we will study two ways of management information: TRANSACT-SQL (T-SQL) or using Microsoft SQL Server 2000 Enterprise Manager. It should be noted that when you modify the data sheet, the elasticity provided by the ENTERPRISE Manager is not the same. Using Enterprise Manager is more advantageous than T-SQL, because when you perform some specific modifications, Enterprise Manager is relatively simple. If you try to make an improper modification, Enterprise Manager displays an error message or a suggestion. However, T-SQL can provide a benefit: If you execute the command in a command code, you can have a record of how you change your information.
Before the official start, we need to build two data sheets in the MYDB database: bicycle_sales and bicycle_inventory as examples used in this chapter. Data Sheet Bicycle_sales contains sales information of bicycle vendors, including the following information: Make_ID, Model_ID, Description, Year, Sale_ID, Price, Quantity and Sale_Date. Make_ID and model_id two data rows are designated as external index key conditions, reference two data lines in the Bicycle_IDEL_ID in the Bicycle_Inventory table, which make up the unique cluster index. In Chapter 16, the external index key condition constraint can only refer to a primary key information in the reference table or the only unique constraints (constraints will be introduced in Chapter 16, the index will be on the 17th Introduction in chapter).
Sale_ID Data Line is announced as the primary key cluster of the Bicycle_sales table. CREATE TABLE described in these data sheets is 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, - orso 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 establish a BiCycle_inventory data sheet before establishing a BiCycle_sales table. If you want to build a BiCycle_sales table, an error message is displayed. Bicycle_sales information table refers to the Bicycle_Inventory data sheet through the condition constraint, so if the Bicycle_Inventory data sheet does not exist, the condition constraint cannot be established, and the result will be displayed.
Now we have established an example-of-instance table, which will make several modifications to them. First use T-SQL, then use Enterprise Manager.
Using the T-SQL Modify Data Sheet in this section, you will learn how to use the T-SQL command to modify, new, delete, and rename the data in the existing data sheet. The T-SQL command to modify the data sheet is Alter Table. Modify data line
Once the data sheet is established, the ALTER TABLE command can be used to modify the data line of the data line, accuracy (for digital type) and whether nullability is allowed, and the RowGuidCol property of the data line can be added or deleted. Other modifications can be made through the use of other T-SQL commands, such as adding a preset value, which will be introduced in Chapter 16.
Not all information lines can be modified. In general, the following types of information cannot be modified:
Data lines for part of the main index key or external index key condition constraint (Rewind request) Data line calculation information line with text, ntext, image or timestamp, etc. Multi-Row For Calculation Row A RowGuidcol Data Line (However, you can add or remove the data line RowGuidcol property) Use the information line for the CHECK or UNIQUE condition (Conditional Constraint in Chapter 16). It will be explained) Use the Create Statistics statement to perform statistics (SQL Server generated statistics can be deleted using the alter table.)
Data related to preset values Other types of data can be modified using ALTER TABLE statements. In some examples described above, the restrictions can be removed before performing modifications. For example, the external index key or other conditional constraint of the data line can be removed, or the index can be deleted. If there is no other limit, the data line can be modified. Modifying the data type to modify the data line of the data line, the original data type must be converted to the new data type. The list of data types allows the conversion to "Cast" in the "CAST" in the series, in the topic of "CONVERT". To get this topic, you can search for CAST on the book online, then select CAST and Convert in the topic dialogue in the found topic. Modify the data line of the data line as follows: ALTER TABLE
Alter column
You can reduce the space used by each information, in this chapter, as long as the data line of the BiCyle_sales table's Sale_Date data line is changed from DataTime to SmallDateTime. Because DataTime will occupy 8 positions, the SmallDateTime is as long as the group is set. To make this modification, use the following command:
Alter Table Bicycle_sales
Alter Column Sale_Date SmallDatetime Not Null
Go
Any existing data sheet will be converted to a new data type SMALLDATETIME. This information line allows NULL attribute values - NOT NULL has not changed. To change the data type of the Description data to VARCHAR (20) (length), you can use the following command:
Alter Table Bicycle_sales
Alter Column Description Varchar (20) NULL
Go
It is to be noted that the original data type CHAR (30) has been modified to new data type, VARCHAR (20), because VARCHAR (20) is shorter, if there is a length in the Description data in the original data column The value of 20 characters will be truncated and converted to varchar (20) without any warning.
Note When the modified data modified in a piece of data is shorter than the original data type, the value in the existing data column exceeds the new type of length, which will automatically cut off the length of the new line.
Modify whether NULL attributes are allowed in addition to the data lines in the main index key conditional constraint, you can allow the NULL attribute to change to NULL by NOT NULL. (The main index key condition constraints such information rows are not allowed to be NULL values.) You can also change the NULL data line to Not NULL, but there must be no NULL values in this data line. If there is a NULL value in the data line, you need to execute the Update statement to change the NULL value to other values to change the data line to NULL to NOT NULL. If the modified data line does not specify whether it is allowed to allow NULL, the preset is to allow NULL values. Let us see some examples. To change the Quantity data line to allow NULL values, do the following statements:
Alter Table Bicycle_sales
Alter Column Quantity Tinyint Null
Go
The information line of the data is tinyint remains unchanged, and whether the NULL attribute is allowed to be modified. Now the Quantity Data Line will allow the NULL value to be inserted. If no value is input, NULL will be automatically inserted. This change does not affect the value of the Quantity data line in the existing information column, but the Quantity data line in the new data column in the back data sheet will be inserted into NULL. Now let's modify the Description data to NOT NULL. We assume that some NULL values have been existing in this information. Therefore, we must first set the NULL value to other values - in this example, we change NULL to None so that it is compatible with the data type of this data line. To test the NULL value, use T-SQL keyword is null or is not NULL to be more secure than using the operator (=). This is because NULL is an unknown value, and one is equal to whether the operator (=) can compare the NULL value, then see the ANSI NULLS Database option is set to ON or OFF. If set to OFF, the arithmetic calculating of the holding null value will pass back TRUE, and it is transmitted back to False. If this option is set to ON, all similar comparison expression = null will return UNKNOWN and will not return any result set. You may have guess that when this option is set to ON, SQL Server will not pass any NULL value. Using is NULL and IS Not Null keywords can achieve the same purpose without having to pay attention to the setting of ANSI_NULLS. To change the NULL value of the Description Data to None, you can use the Update Set as follows: update bicycle_sales set description = "none"
Where description is null
Go
Then change the descripition data to NOT NULL:
Alter Table Bicycle_sales
Alter Column Description Char (30) Not Null
Go
Similarly, we have not changed the original data type, which only changes to the NULL attribute. You can simultaneously modify the data type and whether the NULL attribute is allowed in a single ALTER TABLE command, 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 to allow NULL properties to allow NULL properties. Added or unloading the ROWGUIDCOL attribute To new ROWGUIDCOL attributes or RowGuidcol properties of the Data Row, you can use the following syntax:
Alter Table
Alter Column
You can only add ROWGUIDCOL attributes for the data line of UNIQUEIDENTIFIER. Assuming that there is a UniqueIdentifier Data, which is named Unique_ID in our Bicycle_Sales 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_salesalter Column Unique_ID Drop RowGuidcol
Go
New information line You can also use the alter table command to add information on the data sheet. Whether it is establishing or modifying a data sheet, the definition of the data line is quite similar. You must specify the name and data type of the data line, and selectively specify other features, attributes, and conditional constraints. When you add a NOT NULL, you must also declare a preset value, allowing the existing information to deposit the new data line into the preset value. Use the keyword default to specify a preset value. To add information, use the following syntax:
Alter Table
Add
Default Default_Value
For example, to add a data line named SalesPerson_ID to the BiCycle_sales table, use the following command (this new data is not allowed to be empty and the preset value is 0):
Alter Table Bicycle_sales
Add salesperson_id tinyint not null
DEFAULT 0
Go
Since the data line is declared as Not NULL, all existing data columns in the data sheet deposits the preset value of 0. If we add SalesPerson_ID information behavior null, the preset value is selective, as shown below:
Alter Table Bicycle_sales
Add Salesperson_id Tinyint Null
DEFAULT 0 --Optional Default Value
Go
Even if we specify a preset value, NULL will still be assigned to the existing information column SalesPerson_ID Data Line - only the preset value in the newly inserted data column will work. To force the existing data list to deposit the preset value 0 instead of null, you can use the default with value, as shown below:
Alter Table Bicycle_sales
Add Salesperson_id Tinyint Null
Default 0 with values
Go
WITH VALUES Specifies the new preset value to store the new information line of the existing information column, not NULL. Removing the Data Line You can also use the alter table command to remove the data line from the data sheet. All information on the data line will be removed from the data sheet. However, the following types of data cannot be removed from T-SQL: Data rows for the main index key, external index key, UNIQE or CHECK conditional constraints, used to rewrite data line for indexing (unless Index has been unloaded) Data lines related to the rules to the rules.
These restrictions are still applicable when using Enterprise Manager to remove the data line, but the processing is different. This chapter is described in this chapter
Use the following syntax to remove the data from the information list:
Alter Table
Drop column
The following commands can remove the DESCRIPTION Data from the Bicycle_sales table:
Alter Table Bicycle_sales
DROP Column Description
Go
The Data Sheet All the DESCRIPTION Data Line and the value thereof will be deleted. Note It is particularly careful when you are unloading your information. If you don't have a backend database, you will not be able to restore the information in the Data Line. Even if the transaction is in the backup, you also need to perform a transaction record file to reply the database. You can also rebuild the data line and store the new value.
Data Line Rename To use the T-SQL to rename the data, you must use the following syntax to execute the sp_rename system pre-store:
sp_rename 'Table.ORIGINAL_COLUMN_NAME', 'New_COLUMN_NAME', 'Column'
For example, to rename the Description data to Bicycle_Desc, you can use the following command:
sp_rename 'bicycle_sales.description', 'Bicycle_Desc', 'Column'
Go
Original Data Ring Name must use the table.column format, but the new data line name does not need to include the data table name. Using the Enterprise Manager modified the data sheet as previously mentioned, using Enterprise Manager to modify the data sheet, it will be easier to use T-SQL and more features and flexibility. You can use the design information table window or the database chart to complete all modifications. Let's take a look at the method of using the design information watch window. To open the design information table window to our BiCycle_sales, please follow the steps:
Expand the data clip of the database MYDB in the left pane of Enterprise Manager.
Press the information table to display a list of all data sheets in the MYDB database in the right pane, as shown in Figure 15-1.
Figure 15-1 ENTERPRISE Manager
Press the right button on the Bicycle_sales table in the right pane. Select the design data table from the quick-see function table to open the design sheet window, as shown in Figure 15-2. This window displays the original unmodified Bicycle_sales table.
Figure 15-2 Design Data Sheet Window
Modifying the information line To modify the data line in the design data sheet window, just press the appropriate storage or verify the square in the square pane, and make the required modification. Each column in the square represents each of the information sheets. The title above the square represents the properties set by each storage. For some conditions, T-SQL may not allow you to perform specific modifications and return to an error message, but Enterprise Manager can provide some messages that you correctly complete the modified program. For example, if you try to use the T-SQL command ALTER Table to modify the data length of the data line, this data is just a main index key or an external index key constraint condition, then SQL Server will return to the following error message. :
Server: message 5074, level 16, state 4, line 1
The object 'sales_inventory_fk' is associated with the data line 'make_id'.
Server: message 4922, level 16, status 1, line 1
ALTER TABLE ALTER color column make_id has failed because there is one or more items to access this
Data lines.
But if we use Enterprise Manager, a message that allows you to modify the length of the data line. For example, you want to line Make_ID (this data line has a reference to BiCyle_INventory Table Make_ID's external index key constraint condition) modified from tinyint to Smallint, press the arrow to display the data type, don't pull the order list, then select Smallint. As shown in Figure 15-3. Figure 15-3 Data types using Enterprise Manager change data line
Since Make_ID has an external index key constraint condition, it is necessary to change the data type alternative dialogue, as shown in Figure 15-4. Press Yes, the MAKE_ID data of the two data sheets will automatically change by Tinyint to Smallint.
Figure 15-4 Requires Change Data Type Dialog
Just like T-SQL, when you use Enterprise Manager to modify the data type, the original data must be converted to a new data type. If you try to perform an improper conversion, Enterprise Manager will return a wrong message similar to Figure 15-5. Figure 15-5 shows the result of conversion that does not meet the rules, this change attempts to change the data type of the Sale_Date data to TEXT. Press to determine this error message, then select a data type that can be converted.
Figure 15-5 Attempting to change the data type to the type of error that cannot be implicitly converted, the error message displayed later
To store your changes, press the Design Table Window tool column. The storage dialog box then confirms whether the list of information listed will do write a disk, as shown in Figure 15-6. Press to determine if you want to store your changes.
Figure 15-6 "Storage Dialogue" square
New information row should be added to the new information line, press the number of information line names in the design data sheet window, enter the data line name of the new data line, select its data type, and appropriate The property (whether NULL, preset value, identification, etc.) are allowed to assign it. As shown in Figure 15-7, we added a number of data from SalesPerson_ID, the data type is Tinyint, allowing NULL, and has a preset value of 0. Press the archive button to store your changes. SQL Server will add a new information on the data sheet.
Figure 15-7 Add a news from SalesPerson_ID
Removing the Data Line Using Enterprise Manager to remove or delete the data line is quite simple. As long as in the design data table window, select the data line name or any attribute of the data to be deleted (any of the stored degenements with the data line name), select the delete data line from the quick function table. can. Remember to save your change by pressing the archive button. Enterprise Manager will warn when you try to delete the data lines that are constrained or indexes, or have a preset value, or when you have a tuned rule. You will see a message similar to Figure 15-8. Press to delete the data line and all the relevant lines of the data line.
Figure 15-8 If you have tried to delete a line of information with other data lines or data tables, the message block will be displayed
Establishing a database chart You can also modify the data sheet using the database chart in Enterprise Manager. To establish two sample information tables bicycle_sales with Bicycle_Inventory, please follow the steps:
Expand myDB in the left pane of Enterprise Manager, press the right button on the chart. Selecting a new repository chart from the quick function table will display the Welcome to build a database chart wizard screen, as shown in Figure 15-9.
Figure 15-9 Welcome to the establishment database chart wizard screen
Press Next to display the information table screen you want to add, as shown in Figure 15-10. From the list of available data sheets, select the information table you want to include in the chart, press to increase. In this example, we added BiCycle_inventory and Bicycle_Sales. Figure 15-10 Select the information table screen to add
Press the next step to display the completion of the establishment database chart wizard screen. If your selected information table correctly, please press it, or press the next step to make the necessary changes. After pressing, you can see your database chart, as shown in Figure 15-11. Press the archive button to type the name after prompting to store your database. In our chart, there is a vertical line that connects two data sheets and ends on the key pattern, showing the external index key constraint condition association between two data sheets. To display a associated tag, press the right button in the window background and select the label that displays the associated functionality in the flash function table. The name of the external index key condition constraint will be displayed, as shown in Figure 15-12.
Figure 15-11 Database chart example
Figure 15-12 Display data table association label
To select a data sheet, you can press the data sheet; if you want to select more than one data table, press and hold the CTRL button and click on each data sheet. If you press the right button on one of the data tables and select an option in the quick function table, all selected data sheets perform the same action. For example, if we chose the two data sheets in the database chart, press the right button on one of the data sheets, then select the information watch inspection and standard from the fast display, both of the data sheets will display all Data line properties, as shown in Figure 15-13.
Figure 15-13 Displaying data line properties from the database chart
Each data sheet shows that the information line properties are similar to the design data sheet window, but also modified with the same way as the design information table window. You can simply add or change the information line in the required storage, and press the Archive button to save your changes. You can also move or change the size of the data sheet in the database chart, or let the data table can have different appearances, and other changes. Try different commands in the fast display. It should be noted that once your information is modified, an asterisk will appear after the data table name is used to indicate that this information table has been changed. The impact of modifying the information sheet When you modify the data sheet, the inevitable changes in existing information will be immediately caused after you execute the T-SQL command alter table (or after using Enterprise Manager and the change is stored). When modified is being processed, SQL Server places a lock on the data sheet to allow other users from accessing this information sheet. For large databases, if you modify all existing information columns, such as adding a preset NOT NULL data line or unloading a data line, you should be cautious - because this action may take some time Even if the number of users at the same time is very small. When you do the data line modification is to change its data length, accuracy, or decimal digits, this data table needs to be rebuilt in the database, and existing information will be converted to new data type. If a system failure or a fatal error occurs during the modified period, all data sheet changes have records and can be restored. However, once you complete the entire modification, you can only restore the database from the backup and return the information table to reply to the original state (the backup and restore database will be described in Chapter 32 and 33). Deleting Data Sheet When you remove a data sheet, the data sheet is defined, and the information, index, conditional constraints, triggers, triggers, and permissions associated with this information table will be deleted. The reference table for the reference to delete the information table must also be explicitly deleted. If there is an external index key condition constraint in other information tables to this list, the data sheet will not be removed - in other words, if a piece of information is the object depending on the other data, it cannot be deleted. The data sheet has the possibility of removal unless the information constraint or reference is deleted. On the other hand, a data sheet is constrained even if the external index key condition is held, but if there is no other information table depending on it, it can still be removed. In this section, we will show how to use T-SQL and Enterprise Manager to delete the information sheet. Use the T-SQL to delete the data sheet to delete the T-SQL command syntax of the data sheet as follows: Drop Table
Use DROP View or Drop Procedure to delete the reference table and pre-depreciation of the reference delete data sheet. You can delete these inspection tables or pre-stored programs before or after deleting a data sheet. Once the information table is removed, you will not be able to retrieve it - you must rebuild the entire information list, information and all other related things. To remove our sample information table Bicycle_inventory, because the bicycle_sales data table has an external index key constraint condition reference to BiCycle_inventory, we must first remove the external index key to remove the external index key_fk, then remove the BiCycle_inventory data sheet, as shown below:
Alter Table Bicycle_sales
DROP constraint sales_inventory_fk
Go
Drop Table Bicycle_Inventory
Go
If you have not removed the external index key condition Constraint, try to delete this information sheet, it will return an error message and will not perform any deleted actions. Use Enterprise Manager to delete data sheet Enterprise Manager offers two ways to delete data sheets: Use the access to the item dialog or use the database chart. When you want to delete the data sheets that are not dependent by other information tables, the use to remove the item dialogue will be better. To delete a data sheet in this way, follow these steps: Expand the database in the left pane of Enterprise Manager (this database contains information sheet you intend to remove), press on the information table, then in the right pane Find out the information table you want to remove and press the right button on the information table name.
Select delete from the quick function table to display the block of the object, as shown in Figure 15-14.
Figure 15-14 "Removing the object" dialogue
If this information table has any dependent table, the dependence dialogue can be displayed, as shown in Figure 15-15. Any information sheet according to the table will appear in the list of the left side of the dialog. If there is any dependent sheet table here, you will not be able to delete this information table before these dependence has not been removed. If there is no other information table in accordance with the selected data sheet, press the Remove All to remove the data sheet in the Removal of the item dialog.
Figure 15-15 Bicycle_sales Data Sheet "Dependency" dialogue
To delete a document table with a dependent sheet, we will use the second method: Database chart. In this example, we will remove the Bicycle_Inventory data sheet, the removal focus is that the Bicycle_sales data table has an external index key constraint condition reference to the data sheet. If we use the way to remove the item dialog, the delete action will fail. However, if we use a database chart showing these two data sheets, we can remove the BiCycle_inventory data sheet and the external index key constraints are automatically removed. To delete a BiCycle_inventory sheet, follow these steps:
Expand the database you want to use in Enterprise Manager, press the chart, find the appropriate chart on the right pane, press two to open the database chart on the chart name. Select the table name you want to delete - this example is BiCycle_inventory. Press the right button anywhere on the data sheet and select from the Quick Function to remove the data sheet from the database. When you have determined that you want to pass a prompt to be permanently deleted in the database, press it. The data sheet is removed from the external index key conditional constraint from the chart. If you are sure you want to permanently delete this information table, press the Archive button to save your changes. The data sheet will then be deleted from the database. If you have changed your mind after you delete, you can leave the editing chart window if you turn off and not store the change. If you subsequently reopen the database chart, you will find that the original information still exists. No changes do not take effect unless you save your job.
This chapter summarizes this chapter, you have learned how to use T-SQL and Enterprise Manager to modify, new, remove, and rename the data line to complete the purpose of the modified database data sheet. The difference between T-SQL and Enterprise Manager functionality is also presented in our study. You have also learned how to use the establishment of the database chart wizard to build a database chart, and how to delete the data sheet from the database and all the information in the table. For information sheets, we can also do some other changes, including modifications, add, and unload conditional constraints and preset values. These changes will be described in Chapter 16.
