Other related articles:

Recently viewed articles:

CREATE TABLE (SQL)

CREATE TABLE (SQL) is used to define new tables in Database Management System (DBMS) and make them ready to accept data. When the user creates a table using 'CREATE TABLE' in SQL, he automatically becomes the owner of newly created table. The new table is created as empty table; means there is no data in it as such. But 'INSERT QUERY' can be used to insert rows in this table later on.

where: Emp is tablename, Emp_id, Ename, Dept, Salary and DOJ are column names and number, varchar, date are datatypes and ( ) along with defines size for column... '

Image below shows the table created using above ‘CREATE TABLE’ statement. ‘Desc Emp’ is the command used to describe emp table:

This 'CREATE TABLE' statement consists of many parts. 'CREATE' and 'TABLE' are keywords. tablename is the value for name of table specified by user. The part in between the parenthesis ( ) defines various column names and their data types in the table.

The order of columns in 'CREATE TABLE' (SQL) statement is the order of actual columns in table from left-to-right. It is recommended to use underscore in table name and column names to make them more readable. Also all SQL command should be followed by semicolon (;).

Each column name must have data type mentioned with it. There are many different data types used in DBMS. The user has to determine what data type is to be used with which column. Each data type should have a defined size. This size is the maximum number of characters that can be stored in that column.

For more information on data types, please visit following link: SQL Datatypes

This create table query discussed above will create a very simple table definition without any constraints on any of the columns. For information on creating table using constraints please click here.

Discussed below are some of the advanced options used with ‘CREATE TABLE’ in SQL:

TABLESPACE: specify the tablespace that is storage location where the actual data for database objects can be kept. If you omit tablespace, the table will be created in default tablespace for that schema.

PCTUSED & PCTFREE: These are used to define data limits for data block. For example, in above create table statement, PCTFREE is set to 20 which means we can insert data until data block is 80% full (100-20). After this we won’t be able to insert data into this table. To start inserting data again we have to free this table up to 60% (100-40), which is the size defined for PCTUSED.

INITRANS & MAXTRANS: These parameters are used to define number of transaction that can be performed on data block (INITRANS) and the maximum number of transaction that can be performed simultaneously.

STORAGE ( INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE BUFFER_POOL ): Storage clause identifies the storage parameters for this object. INITIAL defined the size of first extent (a contiguous region of computer storage medium reserved for a file) allocated to this table. NEXT represents the size of next extent for this table. MINEXTENTS and MAXEXTENTS identify the minimum and maximum number of extents which can be allocated to this object respectively including INITIAL extent. PCTINCREASE defines the increase in size of extent to be allocated after second extent and so on. BUFFER_POOL let you specify the default buffer pool for this object.

LOGGING|NOLOGGING: specify whether the creation of table or any indexes on this table need to be logged in to the redo log file.

NOCOMPRESS|COMPRESS: to state whether to compress data segment or not to reduce disk space.