SQL - Create Index

What is SQL index?

质量指数是快速检索数据库数据的有效途径。 图表或观点索引可大大改进查询和申请业绩。 虽然指数有助于加速查询,但用户无法直接看到这些指数。

An SQL index is a special table that provides an efficient way to search larger tables enabpng faster retrieval of required data. When we try to retrieve data from multiple tables using joins, indexes help to improve the performance.

在处理小表格时通常不需要设定一个指数;但是,如果表中包含大量记录,指数将大大提高查询率。 随着数据量的增长,结构指数对于优化任何相关数据库管理系统的业绩至关重要。 页: 1 服务器支持可用于此目的的多种指数。

下表是:

Clustered Indexes

Non-Clustered Indexes

Unique Indexes

Filtered Indexes

Full-text Indexes

How to create SQL Index?

CREATE INDEX 报表用于在数据库中建立一个或一个以上栏目的索引。

Syntax

以下简称:CREATE INDEX> statement in --

CREATE INDEX index_name 
ON table_name (column_name1, column_name2,…column_nameN);

在这方面,

index_name This specifies the name of the index that you want to create.

table_name This specifies the name of the table on which you want to create the index.

(column_name1, column_name2,…column_nameN) are the names of one or more columns on which the index is being created.

Example

首先,让我们努力利用以下询问建立一个名为CUSTOMERS的表格:

SQL> CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

让我们利用以下询问,将一些价值观列入上述表格:

SQL> INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1,  Ramesh ,  32 ,  Ahmedabad , 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (2,  Khilan ,  25 ,  Delhi , 1500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3,  kaushik ,  23 ,  Kota , 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4,  Chaitap ,  25 ,  Mumbai , 6500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (5,  Hardik , 27 ,  Bhopal , 8500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (6,  Komal ,  22 ,  MP , 9000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (7,  Muffy ,  24 ,  Indore , 5500);

Once the table is created, let us create an index for the column named “NAME” in the CUSTOMERS table using the following query −

SQL> CREATE INDEX index_name on CUSTOMERS(NAME);

Output

当我们执行上述询问时,产出如下:

Commands completed successfully.

Verification

The stored procedure named sp_helpindex contains information about all the indexes created on a table or view. You can get the pst of the indexes created on the CUSTOMERS table using the following query.

在这方面, we are calpng the sp_helpindex procedure by passing the name of the desired table as the @objname.

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

在你观察上述询问的结果时,你可以找到“NAME”栏以及索引表中的ID。

+----------------------------------+----------------------+------------------+
| index_name                       | index_description    | index_keys       |
+----------------------------------+----------------------+------------------+
| index_name                       | nonclustered located | NAME             |
|                                  | on PRIMARY           |                  |
| PK__CUSTOMER__3214EC27FBADB0A7   | clustered, unique,   | ID               |
|                                  | primary key located  |                  |
|                                  | on PRIMARY           |                  |
+----------------------------------+----------------------+------------------+

Creating an index on Multiple Fields

我们还可使用国家清单数据表编制多个领域或表列索引。 为此,你刚刚需要将各栏的名称(需要编制索引)传递给询问。

Example

Instead of creating a new table, let us consider the previously created CUSTOMERS table. 在这方面, we are trying to create an index on the columns “NAME” and “AGE” using the following query −

SQL> CREATE INDEX mult_index_data on CUSTOMERS(NAME, AGE);

Output

当我们执行上述询问时,产出如下:

Commands completed successfully.

Verification

现在,让我们采用以下办法,列出在科罗塞罗群岛表格上建立的所有指数:

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

在你观察上述调查结果时,你可以在指数表中找到“NAME”和“AGE”等栏目。

+----------------------------------+----------------------+--------------+
| index_name                       | index_description    | index_keys   |
+----------------------------------+----------------------+--------------+
| mult_index_data                  | nonclustered located | NAME, AGE    |
|                                  | on PRIMARY           |              |
| PK__CUSTOMER__3214EC27FBADB0A7   | clustered, unique,   | ID           |
|                                  | primary key located  |              |
|                                  | on PRIMARY           |              |
+----------------------------------+----------------------+--------------+