Sybase's data definition language

Used to define database objects. Database objects are logical entities that Sybase uses to store data, mainly:

Tables (table), views, temporary tables (temp table);

Primary KEY (Primary key), foreign key (Foreign key), index, rule (rules), defaults (default);

Stored Procedures (Stored Procedure), triggers (Trigger)

Basic syntax

The syntax for creating primary database objects is given below:

1. Table

The basic syntax for creating a table is:

Create table[database.[ Owner]. table_name

(column_name datatype [default {constant_expression|user|null}]

{[{Identity|null|not null}]| [[Constraint constraint_name]

{{Unique|primary key}[clustered|nonclustered]

[With{fillfactor|max_rows_per_page}=x]

[On Segment_name]

|references[[database.] Owner.] Ref_table

[(Ref_column)]

|check (Search_condition)}]} ...

When building a large database, you can consider the process of creating tables and even other database objects written to a text, when the database system problems, in the worst case, the reconstruction process can be simplified, but also a better monitoring of the construction of the database.

The process of creating a table completes the following activities:

• Define each column of the table;

• Defines the column name and the data type of the column and specifies whether the column handles null values;

• Specifies whether the column has an identity attribute;

• Define column-level integrity constraints and table-level integrity constraints

The procedure above is visible, the process of creating a table can set the fill factor, place the column on a segment, design the index, foreign key, and so on.

2. Index

The index has a great impact on query performance and should be taken seriously.

Indexing accelerates data retrieval, adaptive server has three types of indexes:

• Composite Index-The index contains multiple columns, which can be established when two or more columns are queried as a whole because of their logical relationship;

• Unique index--the value of the indexed column does not allow duplication;

Cluster index and nonclustered index--clustered index forces server to sort or reorder data in tables continuously to ensure the physical and logical order of data in the table. Clustered index has great effect on range query performance; Non-clustered indexes have no such requirement, and nonclustered indexes are advantageous to modify operation.

When do I build an index?

• Create a unique index to ensure that no existing value is inserted if the identity column is inserted manually;

• Columns that are frequently sorted, that is, columns that are listed in the ORDER BY clause, preferably indexed so that adaptive server can take full advantage of the indexing sequence;

• If the columns are often connected by hand, the columns can be indexed so that the system can perform the connection faster;

• Columns containing primary keys generally have clustered indexes, especially if they are frequently associated with columns in other tables;

• It is best to set up clustered indexes for the columns that are frequently queried, and once the first value within the scope of the query is found, the subsequent values will be physically similar. Clustered indexes have no advantage over single valued queries.

Basic syntax for creating an index:

Create [Unique][clustered|nonclustered]index index_name

on [[Database.] Owner.] table_name

(Column_name[,column_name] ...)

[On Segment_name] [With Consumers=x]

The above syntax contains the hint that the clustered index is separated from its base table on different segments; The segment is a logical concept, but the segment can be on different physical devices, and the clustered index and the base table are physically separated. This is not allowed, we will discuss the device, database, segment, table partition in detail later.

3. Key (Key)

Understanding keys is the key to understanding associations.

Keys and indexes are often the same thing. The meaning of the key is conceptually, the key is used for referential integrity constraints.

A primary key is a collection of single value columns of a table, and the primary key implements its single value by creating a single value index on the table on which they are placed. The primary key is actually present as a flag table identifier, and once the primary key is determined, the table determined by the primary key is determined.

A foreign key is a column related to a primary key in another table, and the relationship between the primary key and the foreign key determines the value range of the foreign key, which is the scope of the corresponding primary key. In this way, the referential integrity between table and table is enforced theoretically.

The syntax for creating the table earlier contains the component that creates the key. You can also create primary and foreign keys in other ways.

The difference between ◇unique constraint and PRIMARY KEY constraint

Unique constraints and Primary key constraints are used to guarantee that there are no duplicate values on the columns specified in the same table, both of which produce a unique index to ensure data consistency, by default, the unique constraint produces a unique nonclustered index, and the Primary key constraint produces a unique clustered index. The Primary key constraint is stricter than the unique constraint: The Primary key column does not allow null values, and the unique column allows null values.

4. View

A view is a way to view data in multiple tables, derived from a base table, not physically, but as a logical table; A view also provides a security mechanism for managing tables. Views enable users to focus on the data set that interests them.

Syntax for creating a view:

CREATE VIEW [[Database.] Owner.] View_name

[(Column_name[,column_name] ...)]

As SELECT [DISTINCT] select_statement

[WITH CHECK option]

Views with the DISTINCT keyword cannot be updated. When a view involves an association, it is important to define the view, when it is a multiple-table operation.