In the process of using a database, the most contact is the table in the database. The table is where the data is stored. Is the most important part of the database, the management table also manages the database well. This chapter describes how to create and manage database tables.
The
table is made up of rows and columns. The process of creating a table is primarily the process of defining a table's columns, so you should first understand the properties of the table's columns.
7.1.1 Column Properties
The column names of the
table are unique in the same table, and the data for the same column belongs to the same data type. In addition to specifying the properties of a column with column names and data types, you can define additional properties: null or NOT NULL, and identity properties.
(1) null or NOT NULL
If a column of a table is specified to have a null property, the value of the column is allowed to be omitted when the data is inserted. Conversely, if a column of a table is specified to have a not NULL property, it is not allowed to insert data rows that omit the column value without specifying a column default value. In SQL Server, the lack of a column is not NULL. To set the default property to NULL or NOT NULL, you can modify "ANSI NULL default" in the Database property options in Enterprise Manager as true or false. You can also use the following two kinds of statements to set:
- Set ansi_null_dflt_on or set ansi_null_dflt_off;
- sp_dboption database_name, ' ANSI null default ', True/false.
(2) IDENTITY
The IDENTITY property enables the columns of the table to contain the numbers that are automatically generated by the system. This number can uniquely identify each row of a table in a table, where each row of data in the table has a different number on the column specified as the IDENTITY property. The column that specifies the identity property is called an identity column. When you define a column with the IDENTITY property, you can specify an initial value and an increment. When inserting data into a table that contains an identity column, the initial value is used when inserting the first row of data, and the new identity value is subsequently obtained by SQL Server based on the identity value used last time. If you do not specify an initial value and an increment value, the default value is 1.
The
IDENTITY property applies to int, SMALLINT, TINYINT, DECIMAL (p,0), N:::::.
umeric (p,0) data type columns.
Note: A column cannot have both a null attribute and an identity property, only one of them can be selected.
7.1.2 creates a table using the CREATE TABLE command
creates a table using the CREATE TABLE command quickly and clearly. The syntax is as follows:
CREATE TABLE [database_name.[ owner].| Owner.] TABLE_NAME
({<column_definition> | column_name as Computed_column_expression |
<table_constraint>} [,... n])
[on {filegroup | DEFAULT}]
[TEXTIMAGE_ON {filegroup | DEFAULT}]
<column_definition>:: = {column_name Data_type}
[[DEFAULT constant_expression]
| [IDENTITY [(Seed, increment) [Not for REPLICATION]]]
[ROWGUIDCOL]
[COLLATE < collation_name >]
[<column_constraint>] [... n]
The
parameters are described as follows:
The
- database_name
Specifies which database the newly created table belongs to. If you do not specify a database name, the tables that you create are stored in the current database.
- owner
Specifies the user name of the database owner. The
- table_name
Specifies the name of the newly created table, up to 128 characters long.
Database_name.owner_name.object_name should be unique to the database. The
- column_name
Specifies the name of the newly created table, up to 128 characters long.
Database_name.owner_name.object_name should be unique to the database. The
- computed_column_expression the expression for the column value of the
specified computed column (computed). An expression can be a column name, a constant, a variable, a function, or a combination of them. A computed column is a virtual column whose value is not actually stored in the table, but rather the result of some calculation of the other columns in the same table. For example, when an employee's employment date is stored in an employee information table, and the employee's seniority can be calculated by the expression "date of hire", the seniority column can be used as a computed column.
- on {filegroup | DEFAULT}
Specifies the filegroup name where the table is stored. If you use the default option or omit the ON clause, the new table is stored in the default filegroup. The
- textimage_on the filegroup of the data store for the
specified text, NTEXT, and image columns. Without this clause, these types of data are stored in the same filegroup as the table. The
- data_type the
specified column data type
- default
To specify the column's default value. When you enter data, if the user does not specify a column value, the system uses the set default value as the column value. If the column does not specify a default value but allows null values, the null value is the default value. The default values can be constants, NULL values, SQL Server internal functions (such as the GETDATE () function), niladic functions, and so on. The
- constant_expression A constant expression of the
column default value, which can be a constant or system function or null.
- IDENTITY
Specifies column identitY column. There can be only one identity column in a table. The
- seed
Specifies the initial value of the identity column. The
- increment
Specifies the increment of the identity column. The
- not for REPLICATION
Specifies that the identity property of the column does not function when inserting data copied from other tables into the table, which is insufficient to generate column values, leaving the copied rows of data in their original column values. The
- ROWGUIDCOL
Specifies to be listed as the globally unique authentication row number (ROWGUIDCOL is the abbreviation for row Global unique Identifier column). The data type of this column must be a uniqueidentifier type. Only one column in a table with a data type of uniqueidentifier can be defined as a ROWGUIDCOL column. The ROWGUIDCOL property does not make the column value unique, nor does it automatically generate a new value for the inserted row. You need to use the NEWID () function or the default value of the specified column in the INSERT statement as the NEWID () function. The
- collate indicates the checksum used by the table. The
- column_constraint and table_constraint
Specify column constraints and table constraints, and we will describe their specific definitions in the next section. The remaining parameters of the
are described step-by-step in later chapters.
Note: A table has at least one column, but not more than 1024 columns. You can create up to 2 million tables per database. The unit of measurement that the table uses when it is stored is the panel (Extent). A panel is divided into 8 data pages, 8KB bytes per page. When a new table is created, it is assigned an initial storage space of only one disk area. When the table is added to the storage space, the disk area is increased.
7.1.3 Create a table with Enterprise Manager
Create a table in enterprise Manager by following these steps:
(1) When you select the Tables object in the database where you want to create the table, right-click, select the New table option from the shortcut menu, or select the icon in the toolbar, the Definition Column dialog box shown in Figure 7-1 appears. Here you can set properties such as the column name, data type, precision, default value, and so on for the table.
(2) Click the Save button in the Figure 7-1 toolbar to appear as shown in Figure 7-2 to enter a dialog box for the new table name.
(3) When you enter the table name and click OK, the table is saved to the database. Then a few buttons on the right side of the toolbar in Figure 7-1 will become available, and you can use them to set additional information for the table, which will be used in future chapters.
7.1.4 Create a temporary table
You can create table-local or global-action temporary tables with the CREATE TABLE command. The syntax is essentially the same as creating a generic table, except that the symbol "#" is used before the table name of the local temporary table, and the symbol "# #" is used before the table name of the global temporary table to distinguish it from the general table. Because SQL Server stores the table name of a temporary table in the sysobjects table in the tempdb database, it automatically adds a system-generated 12-bit numeric suffix, so the table name of the temporary table can only be specified as long as 116 characters, with no more than 128-character naming restrictions.
Example 7-2 creates a local temporary table test123
CREATE TABLE #test123 (
test_id smallint,
Test_name Char (10),
) on [Primary
