SQL Advanced App (CREATE INDEX)

SQL CREATE INDEX Statement

CREATE INDEX statement for creating indexes in tables

　　Enables database applications to find data faster without reading the entire table

You can create indexes in tables to query data more quickly and efficiently

Users cannot see the index, they can only be used to speed up search/query

　　Note: Updating a table that contains an index requires more time than updating a table that does not have an index, because the index itself needs to be updated. Therefore, it is desirable to create an index only on columns (and tables) that are often searched

　　

SQL CREATE INDEX Syntax

Create a simple index on a table that allows duplicate values to be used

CREATE Idnex Index_nameon table_name (column_name)

Note: "column_name" specifies the columns that need to be indexed

SQL CREATE UNIQUE INDEX syntax

Creates a unique index on the table. A unique index means that two rows cannot have the same index value

CREATE UNIQUE INDEX Index_nameon table_name (column_name)

　　

CREATE INDEX Instance

Create a simple index, named "Personindex", in the LastName column of the persons table

CREATE INDEX Personindexon Persons (LastName)

If you want to index a value in a column in descending order, you can add a reserved word after the column name DESC

CREATE INDEX personindexon person (LastName DESC)

If you want to index more than one column, you can list the names of those columns in parentheses, separated by commas

CREATE INDEX personindexon person (LastName, FirstName)

　　

SQL Advanced App (CREATE INDEX)