Ms SQL Server 2000 administrator manual series-10. Create Database Data Tables

Source: Internet
Author: User
Tags sql server books table definition

10. Create a database data table
Deployment of basic work
Basic concepts of data tables
Create a data table in the archive Group
Use null
Add the identity attribute
Create a data table using Enterprise Manager
Summary
When the database is created (The database includes archives and archive groups), the next step is to create a data table object to save the data. Creating a data table is a complex process. This chapter describes the considerations for creating a data table. First, we will introduce the basic requirements for creating a data table, study the project, and finally introduce the example.
This chapter describes the types of user data and user-defined data, and how to store data tables, null values, and identity attributes in the archive group. You will also learn how to use Enterprise Manager and transact-SQL (T-SQL) to create a data table, as well as limits, default values, and indexes for the data table.
________________________________________
Note:
Before creating a data table, you must familiarize yourself with the data and elements mentioned above.
________________________________________
Deployment of basic work
 
Before designing a data table, you must make some architectural decisions on the data table. These decisions allow you to have an overview of the entire data table and design each data table one by one under the overview, which of the following are the decisions you should consider:
• What data does a data table contain?
 
• What data rows should be created to store data? What are the names of these data rows?
 
• How to set the data range and type of data rows?
 
• Can a data row contain null values? Can I use the default value? (There are more steps to use the null value than to use the default value ).
 
• Which data rows should be set as primary index keys and which should be set as external index keys?
 
• What conditions should I assign to the table?
 
• Which type of index (cluster or non-cluster) should be used )? Which data rows need to be indexed?
 
• User Access to Data Tables
 
Before establishing a database, consider design issues as many as possible. The entire data table structure can be drafted on the paper to simulate the user's data access mode. For example, which data tables should be read-only and which data tables may be inserted, deleted, or added; which data is frequently queried and which data rows are frequently searched to determine whether the data is stored. By understanding the structure of a data table, you can determine how to create a data table, index, and limit data rows and default values. The following describes how to create a data table.
Basic concepts of data tables
 
In this section, we will review some simple but important ideas. This section describes the basic elements, describes the system data types, and describes how to create and delete user-defined data types.
Define data tables
 
A table is a database object that contains all the data in the database and stores data by combining data columns and data rows. A data table is defined by a data row. data can be classified in a format similar to a data table, as shown in Table 10-1. Table 10-1 is a sample database data table named product_info. In the example, we will create this data table in the database of mydb (following the database in chapter 9 ).
The product_info data table is used to store the data of each item in the store. When the item is on the shelf, a new data column is added to the data table. The data table has three columns and five columns. The data row names are product_id, product_name, description, price, and brand_id. We can also use the T-SQL command to create a data table (no actual input is required), which will be described later in the chapter "select the correct data type. (Chapter 1 of this book will teach you how to use the INSERT command to insert data into a data table ).
Table 10-1 product_info
Product_id product_name description price brand_id
1 tent for two people 80 12
2 vas furnace use natural gas 20 33
3 hiking bag durable 60 15
Defining data row attributes is an important part of creating a data table. For example, if the data type of a Data row is defined to determine whether the data range of the Data row is limited (such as the character length), SQL Server provides several data types for the user to choose from, or user-defined data types (you can only add new data types and cannot change existing data types of the system ).
Use the system data type
 
The data type of a data table can be set to the following attributes:
• Types of data that a row can contain, such as character, integer, or image)
 
• Data size or length in a data row
 
• The number of valid digits (for the value type), that is, the number of digits that can be included in the data row.
 
• Decimal number of words (For numeric data type settings), number of digits acceptable to the right of the decimal point
 
The data type also affects the data rows being viewed, the parameters of the pre-stored program, the variables, and the T-SQL functions that return one or more data values. Table 10-2 lists the built-in data types provided by SQL Server. SQL Server 2000 introduces three data types: bigint, SQL _variant, and table. (Except for the special circumstances indicated in the data table, all objects use the same basic data type ).
Table 10-2 System Data Types of Server 2000
Data Type description storage size
Bigint 8-byte INTEGER (full number) 8 bytes
Binary [(n)] n Bytes of binary data with a fixed length. N must be between 1 and 8000. When the data items in the Data row are of the same size, binary should be used. N + 4 bytes
The value of a bit integer is 1, 0, or null. Indexes cannot be created on the BIT data row. Use 1 byte space for 8-bit data rows in the data table, 2 bytes space for 9-16-bit data rows, and so on
Char [(n)] fixed-length non-UNICODE character data, n Bytes
The value of N must be 1-8000.
Cursor Data Pointer Reference, used only for variables and pre-stored program parameters.
Datetime date and time data, ranging from January 1, January 1-9, 1753 to January 1, December 31, 999, with an accuracy of 1/300 seconds and 8 bytes
Decimal [(p, [s])]
Or numeric [(p, [s])] fixed number of valid digits and number of decimal words
(Numeric is functionally equal to decimal ). Precision (p) specifies the maximum number of storage digits (including the left and right sides of the decimal point ). Decimal words (s) specify the maximum number of digits stored on the right of the decimal point. The number of decimal places must be less than or equal to the valid digits. The minimum valid bits is 1 5 to 17 bytes, depending on the valid bits
The maximum value is 28. If SQL Server is started with the-p parameter, the maximum value is 38.
Float [(n)] refers to the number of Float digits, from-1.79e + 308 to 1.79e + 308. N is the decimal word count of a floating point number, ranging from 1 to 53. 4 to 8 bytes, depending on the Accuracy
The variable-length binary data of the image. The maximum length is 2 ^ 31-1 (2,147,483,647) bytes. The items in the image data row point to the location where the image data value is stored. The data is stored separately from the actual data table. The pointer can contain 16 bytes.
Integer or Int INTEGER (complete number) data ranges from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647 ). 4 bytes
The monetary data value ranges from-2 ^ 63 (-922,337,203,685,477.5808) to 2 ^ 63-1 (+ 922,337,203,685,477.5807), and is accurate to the unit of one thousandth of money. 8 bytes
Nchar [(n)] UNICODE character data of a fixed length. The value of N ranges from 1 to 4000. Each UNICODE character is stored in 2 bytes and supports international characters. 2 bytes multiplied by the number of characters entered
Unicode data with a variable ntext length. The maximum length is 2 ^ 30-1 (1,073,741,823) characters. The ntext data row Project is a pointer to the data value position, which is stored separately from the actual data table. The pointer can contain 16 bytes, and the number of characters in the input data is multiplied by 2 bytes.
Unicode character data with a variable nvarchar length. The maximum length is 4000 characters. Each UNICODE character is stored in 2 bytes and supports international characters. 2 bytes multiplied by the number of characters entered
Real floating point digit data, from-3.40e + 38 to 3.40e + 38. The synonym for real is float (24 ). 4 bytes
Smalldatetime date and time data, from January 1-20, 1900 to June 6 (datetime data type is more accurate) 4 bytes
Smallint integer data, from 2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767 ). 2 bytes
The smallmoney data value ranges from-214.748, 3648 to + 214,748.3647, and is accurate to 10 points per cent. 4 bytes
SQL _variant: Data Types supported by SQL Server other than text, ntext, timestamp, and SQL _variant can be stored. SQL _variant can be used for data rows, parameters, variables, and return user-defined function values. SQL _variant allows these database objects to support other data type values. Storage size is not fixed
Sysname is a user-defined data type supported by the system, and sysname is defined as nvarchar (128) for reference to the data row storing the object name. 256 bytes
Just like using a temporary data table-the Declaration must contain a list of data rows and data types. It can be used to store the return values of region variables or user-defined functions. Depends on the data table definition
Non-Unicode data with variable Text length. The maximum length is 2 ^ 31-1 (2,147,483,647) characters. It is a pointer to the data value position. The data is stored separately from the actual data table. The pointer must contain 16 bytes.
Timestamp indicates the unique number in the entire database. Each time the data is 8 bytes
This number is updated when the column is updated. I
Data Tables can only have one timestmap
Field
Tinyint integer data, from 0 to 255. 1 byte
Uniqueidentifier guid ). 16 bytes
Varbinary binary variable-length binary data; Maximum length of input data + 4
It is 8,000 bytes. When the data row is in bytes
You can use
Varbinary.
Varchar [(n)] variable-length non-Unicode data, maximum length of actual input data
The length is 8,000 characters.
Select the correct data type
 
Choosing the correct data type is an important part of creating a database. The data value stored in the data row must be within the permitted range, and the data type that does not match the data type is not allowed to be stored (for example, when the data row only allows the input of numerical values, data cannot be input when a character value is input ). Once the data type is specified, the preceding conditions can be avoided. The following two questions help you determine the data type to be specified:
• Is this data type consistent with the input data?
 
• Is the data row set to a fixed or variable length?
 
The process of selecting the appropriate data type is intuitive, that is, the data entered in the Data row must meet the data type specified by the Data row. Therefore, you need to predict the range of data that the data row may enter. This range can cover the range of future data values without wasting space. A waste of space means that we reserve a range of data values for a data row, but the range is too loose, which wastes the reserved space. For example, if you need a data row to store integer data from 1 to 100, you can set the data row to the integer data type, however, each integer occupies 4 bytes of space. If the data row is set to a tinyint data type (value between 0 and 255), only one byte of space is occupied. Therefore, we recommend that you set this data row to tinyint.
Another consideration is whether the data row should use a fixed length or a variable length data type. If all values in the Data row are almost the same size, we recommend that you use a fixed length. The variable-length data type is applicable when the data value is not fixed or the data itself is not changed frequently. Variable-length data types include varchar, nvarchar, varbinary, text, ntext, and image. Flexible use of variable-length data types can significantly save storage space. If you define the data row as a fixed length according to the longest data value, the shorter data value in the Data row occupies the same storage space, it is better to set the variable length to save space. So why don't we set all data rows to a variable length? In fact, it takes a long time for the system to process data rows of variable length. Therefore, if you can determine whether the length of the data value is fixed, set the data row to a fixed length.
Create a product_info data table using the system data type
 
Before getting started, take a look at the creat table command for the T-SQL (used to create a product_info table such as Table 10-1 ). In this example, only the data types and fixed-length data columns provided by the system data are used.
When you use the T-SQL command to create a data table, the data table is created on the database in use. To use a database, use the use database_name command according to the following syntax. In this example, the name of our database is mydb. Keyword go indicates that all commands before this column should be executed now. (See Chapter 13th using T-SQL details .)
Use mydb
Go
Create Table product_info
(
Product_id smallint,
Product_name char (20 ),
Description char (30 ),
Price smallmoney,
Brand_id smallint
)
Go
After entering the create table command, specify the data table name as product_info. In the middle of the brackets, the Data Type of each data row is defined after the data row name. The length of the two char data types is set to 20 and 30 respectively (considering that product_name and description fall within this value range ). Because we expect more than 255 products and manufacturers to purchase, but less than 32,767 products and manufacturers, in order to avoid wasting space or insufficient space, therefore, set product_id and brand_id to the smallint data type (tinyint ranges from 0 to 255, while smallint ranges from 32,767), instead of tinyint or Int.
User-Defined Data Type
 
The user-defined data type (or alias data type) is the customized system data type. When the data types of several data tables in the same data row must be the same, for example, the corresponding data row must have the same type, length, and null value, you can use a custom data type. You can use descriptive names to define data types, simplify program design and maintain consistency.
For example, there are two data tables in the same database: one is the brands data table and the other is the product_info data table. The brand_id data column in the brands data table must have the same data type as the brand_id data column in The product_info data table, and the null value is not allowed. In this case, you can create a user-defined data type and assign it to the two data rows. It is assumed that there are several data tables, and several data rows must have the same attributes. You may not remember whether smallint, tinyint, or null values are used for data rows in a data table. However, if you have used a descriptive name to create a user-defined data type, you can use a custom data type without worrying about the actual data type.
________________________________________
Description
When a user-defined data type is created in a specific database, it can only be used in the database. However, if a data type is created in the model database (database template), it can be used in all newly created databases.
________________________________________
Use Enterprise Manager to create user-defined data types
 
When creating user-defined data types, three types of information must be provided:
• Data Type name.
 
• The new data type is based on the system data type.
 
• The Null Value Attribute of the data type, that is, whether to allow null values (this chapter will detail the use of null values ).
 
When the preceding information is determined, you can use enterprise manger to create a user-defined data type:
1. In Enterprise Manager, expand the SQL server group and then expand the server.
2. Expand the database data folder and expand a database, as shown in Figure 10-1.
 
 
Figure 10-1 define a data type using Enterprise Manager
3. Click the right button on the user-defined data type and select the new user-defined data type from the shortcut menu. The Custom Data Type attribute window is displayed.
4. enter a new data type name in the name column. Name brand_type first, as shown in Figure 10-2.
 
 
Figure 10-2 "user-defined data type attributes" Window
5. You must specify the data type and length of the SQL Server System referenced by the user's custom type. Take this example. First, define the data type smallint in the data column of the commodity code (the default length value is 5 ). You can define the length of a text data type.
6. If the data type allows null values, select allow null. (For more information about null values, see the section "use null values .)
7. If the data types Use pre-defined rules or default values, select them from the list. (Rules and default values will be discussed in detail in chapter 16th .)
8. Store the new data type as determined.
Use Enterprise Manager to delete user-defined data types
 
To delete unnecessary user-defined data types, follow these steps:
1. In Enterprise Manager, find the user-defined data type to be deleted (expand the SQL server group, server, and database data folder, and then expand the database to be deleted), as shown in 10-3.
2. Select the user-defined data type folder. User-Defined data types are displayed in the right pane, 10-3.
 
 
Figure 10-3 user-defined data type folder
3. Click the right button on the Custom Data Type of the user you want to delete, and select Delete from the shortcut menu to go to The unload object, as shown in 10-4.
 
 
Figure 10-4 "delete objects" dialog box
4. before deleting a data type, Click Show dependency to enter the dependency dialog box, as shown in Figure 10-5.
 
 
Figure 10-5 dependency dialog box
In the dependency dialog box, the list on the left shows the database objects that depend on the user's Custom Data Type. The list on the right shows the objects that depend on the user's custom data type. If any data table or object uses this data type, the system will not allow deletion. Otherwise, an error message is displayed, as shown in Figure 10-6.

 
 
Figure 10-6 delete an error message for a data type in use
5. If the data type to be deleted has no dependency problems, you can close the dependency dialog box and click Delete all in the Object window to delete the data type. You don't have to worry about removing all data types that are displayed in the object deletion window, rather than all user-defined data types.
Create and delete user-defined data types with T-SQL
 
The sp_addtype program is used to define T-SQL commands for user data types. Run this command when using the model database, the new data type will be used for all newly created user-defined databases (because the user-defined data types are the same as those of the model database ). When you use a user-defined database to execute this command, the database will only use the new data type. (Remember, to use a database, you must execute the use database_name command .) The following T-SQL command creates a user-defined data type in the model database: brand_type.
Use Model
Go
Sp_addtype brand_type, 'smallint', 'not null'
Go
The three parameters of sp_addtype are: User-defined data type name, System Data Type of the new data type, and Null Value Attribute of the new data type. The new data type brand_type will appear in all user-defined databases. If you have created a user-defined data type in the user database and want to see the type in Enterprise Manager, select "refresh" in the execution menu of Enterprise Manager.
To delete an unused user-defined data type, run the sp_droptype command in the database that defines it. The following example deletes the User-Defined brand_type from the model database:
Use Model
Go
Sp_droptype brand_type
Go
Use custom data types to create data tables product_info and Brand
 
Return to the database data table example. Use the customized data type-brand_type to recreate the product_info data table, and then create the brand data table. The brand_id data row exists in both the brand data table and the product_info data table. We use the same user-defined type. First, delete the old product_info data table and recreate it. The procedure is as follows:
Use mydb
Go
Drop table product_info
Go
Create Table product_info
(
Product_id smallint,
Product_name char (20 ),
Description char (30 ),
Price smallmoney,
Brand_id brand_type
)
Go
Create Table brand
(
Brand_id brand_type,
Brand_name char (30 ),
Supplier_id smallint
)
Go
By specifying the Data Type brand_type to the brand_id data row in the two data tables, you can determine that the two data rows have the same attributes without remembering the base data type.
It is usually important to remember that the user-defined type data is the DBA and the application's program designer. Needless to say, the DBA needs to understand this. The program designer may need to know the data type when writing the program code, but the user does not need to know these details.
Create a data table in the archive Group
 
If you have created a user-defined archive group, SQL Server allows you to specify the archive group in which data tables and data are stored. By default, data tables are stored in the primary archive group unless another archive group is specified as the default archive group. An archive group can span multiple disks or disk arrays. For more information about how data in a data table is stored in an archive and archive group, see Chapter 9th.
Create a product_info data table in the archive Group
 
Suppose we create an archive group named product_group in the mydb database. This archive group is placed on the E disk where the secondary archive group is located, and the primary archive group is on the C disk. This technique allows us to separate data and data tables from SQL Server System data tables. We will also create a record file in disk F to separate the record file I/O (See Chapter 1 of this book to create a database using archive groups ).
Use master
Go
Creat database mydb
On Primary -- clearly define major archival groups (optional)
-- Main data file
(Name = mydbroot,
Filename = 'C:/mssq12k/MSSQL/data/mydbroot. MDF'
Size = 8 Mb,
Maxsize = 10 MB,
Filegrowth = 1 MB ),
Filegroup product_group -- the archive group of the next Archive
(Name = mydbdata1, -- secondary data file
Filename = 'e:/mssq12k/MSSQL/data/mydbdata1.ndf ',
Size = 100 MB,
Max size = 1500 MB,
Filegrowth = 100 MB)
Log On
(Name = logdata1, -- Record File
Filename = 'f:/long_files/logdata1.ldf ',
Size = 100 MB
Max size = 1500 MB,
Filegrowth = 100 MB)
Go
Now you can use the create table command to create the product_info data table in the product_group archive group. The method is as follows:
Use mydb
Go
Create Table product_info
(
Product_id smallint,
Product_name char (20 ),
Description char (30 ),
Price smallmoney,
Brand_id brand_type
)
On product_group
Go
New data tables and all data inserted into the data table are placed on the E disk defining product_group. Therefore, as long as no other data tables are created in the same archive group, i/O of data in the product_info table has a dedicated disk.
Use null
 
Null value is an unknown value. We call it null. The null value attribute of a Data row indicates whether the data row accepts or rejects null values. A null value in a data row usually indicates that there is no value input in the Data row. This value may be unknown, not applicable, or will be added later. NULL values are neither empty nor 0; their actual values are unknown. Therefore, any two null values will never be equal.
So when will null be used? For example, if you cannot provide information about all data columns, a null value is required. For example, if the customer's website does not have a website address, the data column cannot be entered. In this case, the null value in a data column makes sense, that is to say, the data in this data column is not determined yet.
In general rules, avoid using NULL values as much as possible, because this increases the complexity of queries and updates, and some options (such as primary index keys and identity attributes) it cannot be used in data columns that contain null values.
________________________________________
Related information
In SQL Server books online, enter the keyword "null value" and select compare search criteria in the list to find related information. You can also refer to more information in the "add identity attribute" section in this chapter.
________________________________________
You can specify the default value for a data row to avoid null. Therefore, if a data row has no input value, the default value is automatically entered in the Data row. (For more information about default values, see chapter 16th .) If a null value is allowed in a data row, null will input the data row in two ways:
• If a data column is inserted in the data table but the empty data column is not specified, SQL Server specifies a null value for the data row (unless the default value is specified for the data row ).
 
• You can manually enter null. Please note that do not enter quotation marks (") to avoid confusion caused by null as a string.
 
Create a product_info data table using null
 
Return to the example of the product_info data table and add the null option for each data row. If you want to allow null values for a data row, add null after the data type. If you do not want to allow null values, not null is added after the data type. Unless a user-defined data type is used and the data type already has null or not null defined, it is a good habit to specify whether to allow null values for data rows. This helps you consider the null value attribute of a Data row every time you set a data row.
________________________________________
Related information
In SQL Server books online, enter "CREATE TABLE" and search for the topic of the null attribute rule in the data table definition to find the rule when null or not null is not clear, rules that the null value attribute of a Data row follows.
________________________________________
Return to the example of the product_info data table and accept the null value for the data column definition described by the product. Since the brand_type data type was previously defined as not null, you do not need to specify the null attribute for the brand_type data type. The new create table command is as follows:
Use mydb
Go
Drop table product_info
Go
Create Table product_info
(
Product_id smallint not null,
Product_name char (20) not null,
Description char (30) null,
Price smallmoney not null,
Brand_id brand_type
)
Go
Now, if the product description value is not specified, but the values of the other four data rows are specified (in the data rows that do not accept null values, product_name, price, and brand_id ), when you enter the data of a product, the description data row is inserted in the data table, and null is inserted in the new data column. If no data is input in four data rows that do not accept null values, the data column addition fails.
Add the identity attribute
 
When creating a data table, you can add the identity attribute in the Data row definition to specify a data row as a data row of the recognition item. If the created data row has the identity attribute, it allows the newly inserted data column to automatically compile the data column value based on the seed and incremental value. Seed is the identification number specified in the first data column of the inserted data table. Each time a data column is inserted, the incremental value is added to the initial value to generate the next recognition value. This recognition value ensures the uniqueness of the data table in which it is located. If each data row requires a unique identification number to determine the uniqueness of its data column (such as product_id data row), the recognition number attribute is very suitable, you do not need to consider where the numbers are each time you add a data column. In a data table, the row that identifies a data item is usually used as a primary index key condition to identify a data column with its uniqueness. In chapter 1 of this book, you can find more information about the primary index key condition constraints.
For example, if identity (1st) is specified, the value of the row that inserts the recognition item data in Column 2nd is 0, column 3rd is Column 10, column is column 20, and so on. If not specified, the default seed value and incremental value are ). You must specify two parameters or do not specify either. The data row of the recognition item cannot have a default value or a null value. A data table can have only one row of data that identifies items.
By default, the data row of the recognition item cannot be inserted directly or updated. If you want to re-insert a deleted column and retain the original value, you can use the following description to modify the default settings:
Set indetity_insert tablename on
This statement can be used to insert a data column and specify the value to identify the data row. After that, use the following command to reject data insertion from the data row of the recognition item:
Set identity_insert tablename off
After the preceding command is executed, SQL Server will incrementally increase the value of the identified value as the seed value when the new data column is inserted next time.
Add the identity attribute to the product_info data table.
 
Now we will add the identity attribute in the product_info data table. We will use the product_id data row as the data row for identifying items. Instead of manually inputting data in this data row, SQL Server automatically generates the recognition value to ensure the uniqueness of the recognition value. The following is the T-SQL syntax for creating a data table:
Use mydb
Go
Drop table product_info
Go
Create Table product_info
(
Product_id smallint identity (1, 1) not null,
Product_name char (20) not null,
Description char (30) null,
Price smallmoney not null,
Brand_id brand_type
)
Go
The value of the product_id data row starts from 1 and uses 1 as the incremental value of the data column subsequently inserted into the data table to ensure that each product has a unique recognition value, you do not need to enter the value manually. You can select any incremental value, but the recognition value is unique no matter what incremental value you use.
Create a data table using Enterprise Manager
 
Now we can use Enterprise Manager to create a data table. Remember, before creating a database, you should first understand all database data tables and their associations. To use Enterprise Manager to create a database data table, follow these steps:
1. Expand the SQL server group in Enterprise Manager and expand the database.
2. Expand the database data folder to view the existing database.
3. Expand the database you want to operate on. Here we use mydb.
4. In the data table folder, click the right button to display the shortcut menu and select Add Table. The new data table window is displayed, as shown in Figure 10-7 (this is the maximized graph ).
 
 
Figure 10-7 new data table window
The new data table window contains a square like a workbook. Each column in the square represents each row in the data table. Each row in the square represents an attribute, such as a data type, length, and null value.
________________________________________
Description
Certain standards should be adopted when naming data rows in a data table. It is not important to adopt naming standards, but the naming logic must be consistent. This consistency can avoid confusion during query execution.
________________________________________
5. Define each data row in the database (defined in one column and one column), enter the name into the name of the Data row, and select the drop-down list in the data type data row to define the data type. Select a data type and the length of the data column, press shift in whether to allow the null value data column or tick the box in the data column to allow the null value (if not checked, the null value is not allowed ). The product_info table is shown in Table 10-8. Note that in the Data row brand_id, we set the data type to the user-defined data type brand_type. Note that although the brand_type definition does not accept the null value when customizing this data type, however, whether or not to allow null value data rows will still set the allowed null value to a preset value (which will be checked ). Therefore, whether to allow the selection of null values must be canceled to maintain the consistency of the Null Value Attribute of the data type.
The data storage order is stored in the order you define the data rows. If you want to insert a new data column between the input data columns, click the data column you want to insert and click the right button to open the shortcut menu and select insert data row, if you want to delete a data column, select the data column you want to delete, click the right button, and select Delete data row from the shortcut menu. Now we will set the product_id data row as the primary index key in the product_info data table. After selecting the product_id data column, click the right button, select the primary index key in the shortcut menu, or select the product_id data column, and click the key icon in the tool column. You can see the key Icon 10-9 next to the data row name. In Chapter 2 of this book, we will discuss the primary index parts and other constraints.

 
 
Figure 10-8 new data table window for defining data rows
6. There is a data row volume page below this window, which allows you to change the attributes of some data rows above. For example, select the brand_id data row and enter some data in the description on the volume page of the Data row. Enter 0, 10, and 10 in the default value.
 
 
Figure 10-9 Key icon of the primary index key

 
 
Figure 10-10 Data row volume page
7. you can set other condition constraints and indexes in the data table. Click the right button on the row name and select the index/index key, relevance, and check condition constraints or attributes from the shortcut menu. Or press the data table and index attribute icon next to the disk icon in the tool column. Any method allows you to enter the Properties window, as shown in 10-11. The data table you created may have options such as Table1 and Table2 in the fields of the selected data table. Here we select Table2. You can change the name according to the next step. This window has four volume labels. This book will introduce how to set tabs in detail.
 
 
Figure 10-11 "properties" window for data tables and Indexes
8. to name the new data table, click the save disk icon. The Select name dialog box is displayed, and you can enter the data table name again. Enter the name and press OK to save the data table and all settings. When you close this window, you can see the newly added data table in the right window of Enterprise Manager.
Summary
 
In this chapter, we learned how to create a data table, including using and customizing data types, storing data tables in an archive group, using NULL values, and adding the identity attribute. In fact, there are many other attributes that can be added to the data table. You can add or delete fields, add condition constraints and indexes, and modify data tables. Of course, in the ideal situation, the created data table is the most suitable data table, but this is almost impossible, in many cases, you will find that you need to add indexes or condition constraints that have not been considered before creating a data table. Chapter 5 demonstrates several ways to modify a data table using a T-SQL. In the following chapters, you will learn how to install and set up the network, and learn about Microsoft cluster server.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.