Page History

Required. Holds the data type of a column:

• BOOLEAN Does not require data-length or data-scale attributes. These attributes should not be specified. Implementation on all DBMSs:
  • CHAR(1) where T is the boolean TRUE value and F is FALSE.
• CHAR Requires data-length attribute. Implementation on all DBMSs:
  • CHAR(data-length)
• CLOB Requires data-length attribute. Implementation:
  • TEXT for Microsoft SQL Server
  • CLOB for Oracle
• DECIMAL Requires data-length (used for precision) and data-scale attributes. Implementation:
  • NUMBER(data-length, data-scale) for Microsoft SQL Server and IBM UDB Oracle
• FLOAT Requires data-length attribute. Implementation:
  • FLOAT(data-length) for Microsoft SQL Server
  • NUMBER without any parameters for Oracle
• INTEGER Requires data-length attribute. Implementation:
  • data-length less than three: TINYINT; data-length two-to-four: SMALLINT; data-length more than four: BIGINT for Microsoft SQL Server
  • NUMBER(data-length) for Oracle
• TIMESTAMP Does not require data-length or data-scale attributes. These attributes should not be specified. Implementation:
  • DATETIME for Microsoft SQL Server
  • TIME for Oracle
• UNIQUE INTEGER Does not require data-length or data-scale attributes. These attributes should not be specified. This is a data type for a unique integer whose values are generated automatically. Implementation:
  • IDENTITY for Microsoft SQL Server
  • NUMBER(20,0) for Oracle. In addition, a SEQUENCE and a TRIGGER BEFORE INSERT are created, which select the NEXTVAL of the SEQUENCE from DUAL.
• VARCHAR Requires the data-length attribute. Implementation:
  • VARCHAR(data-length) for Microsoft SQL Server
  • VARCHAR2(data-length) for Oracle
• VARBINARY Requires the data-length attribute. Implementation:
  • VARBINARY(data-length) for Microsoft SQL Server
  • RAW(data-length) for Oracle

Required only for the data types listed below. Specifies the column data length:

• CHAR 
• CLOB
• DECIMAL
• FLOAT
• INTEGER 
• VARCHAR
• VARBINARY 

Optional. Specifies the column default. Can have the following values:

• NULL if the column is nullable (null=TRUE).
• N/A if no default exists. If the attribute is omitted, this is the default.

A constant value that is equal to the column type:

• BOOLEAN TRUE or FALSE
• CHAR A textual constant, such as ABC CLOB; a textual constant, such as ABC DECIMAL; or a decimal point constant, such as 10.3
• FLOAT A floating point constant, such as 12E7
• INTEGER An integer constant, such as 27
• TIMESTAMP One of the following:
  • A timestamp constant of the format yyyy-mm-dd hh:mm:ss.fffffffff, which is jafa.sql.Timestamp’s format, such as 2020-02-20 23:07:35:175000000. Each DBMS displays a slightly different default. Implementation:
    • 2020-02-20 23:07:35:175 for Microsoft SQL Server
    • TO_DATE('2020-02-20 23:07:35') for Oracle
  • The literal string CURRENT TIMESTAMP. Implementation:
    • GETDATE() for Microsoft SQL Server
    • SYSDATE for Oracle
• UNIQUE INTEGER Default value not required and not allowed.
• VARCHAR A textual constant, such as ABC
• VARBINARY A hex string where every two hexadecimal digits represent one byte, such as A07C889F. Each DBMS displays a slightly different default. Implementation:
  • 0xA07C889F for Microsoft SQL Server
  • HEXTORAW('A07C889F') for Oracle

Required for the column role in columns of statistics tables. Can have the following values:

• IDENTIFIER A column identifying the sampled entity. The concatenation of all identifiers should uniquely identify the entity.
• DATE A column identifying the sampled period. Its type should be TIMESTAMP and be equal to the beginning of the sampled period.
• SUM A statistics column whose transfer to a higher summary level (such as hourly to daily) should be applied by the SUM function.
• MIN A statistics column whose transfer to a higher summary level (such as hourly to daily) should be applied by the MIN function.
• MAX A statistics column whose transfer to a higher summary level (such as hourly to daily) should be applied by the MAX function.
• AVG A statistics column whose transfer to a higher summary level (such as hourly to daily) should be applied by the AVG function.

Required. Has the value TRUE if this is a primary index and FALSE if it is not. Implementation:

• ALTER TABLE ADD CONSTRAINT for Microsoft SQL Server. Adding a primary constraint in Microsoft SQL Server always results in creating a unique index to enforce the constraint.
• An index is created using the CREATE INDEX statement for Oracle. Then an ALTER TABLE ADD CONSTRAINT is performed to add the primary constraint. The ALTER TABLE ADD CONSTRAINT is suffixed with the USING INDEX clause to instruct Oracle to use the already created index to enforce the constraint and not to create a new one.

Optional. Has the value TRUE if this is a clustered index and FALSE if it is not. The default is FALSE . A clustered index is an index that physically orders and organizes the table. Implementation:

• CLUSTERED clause for a clustered index, NON CLUSTERED clause for a non-clustered index in Microsoft SQL Server.
• In Oracle, this feature is not used frequently; it requires a complete entity to handle clustering.