Database index instance

References:

[1]. database indexing

1. Create a table and insert data

Create a test database in SQL Server, create a database table, and insert data. The SQL code is as follows:

USE TestIF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES       WHERE TABLE_NAME = 'emp_pay')   DROP TABLE emp_payGOUSE TestIF EXISTS (SELECT name FROM sys.indexes       WHERE name = 'employeeID_ind')   DROP INDEX emp_pay.employeeID_indGOUSE TestGOCREATE TABLE emp_pay( employeeID int NOT NULL, base_pay money NOT NULL, commission decimal(2, 2) NOT NULL)INSERT emp_pay   VALUES (1, 500, .10)INSERT emp_pay    VALUES (2, 1000, .05)INSERT emp_pay    VALUES (6, 800, .07)INSERT emp_pay   VALUES (5, 1500, .03)INSERT emp_pay   VALUES (9, 750, .06)

After executing the preceding SQL code, we will find that an emp_pay table exists in the test database. The content of the database table is shown in:

2. No index search

We can see that the data stored in the database is arranged in the same order as the data inserted. Next we will query the fields of employeeid = 5 and execute the following SQL code:

USE TestSELECT * FROM emp_pay where employeeID=5

In SQL Server Management studio, click "show estimated query plan" to display the query plan diagram shown in:

The table scan content is:

3. Create an index

Next we will add a unique clustered index for the above table. The Code is as follows:

SET NOCOUNT OFFCREATE UNIQUE CLUSTERED INDEX employeeID_ind   ON emp_pay (employeeID)GO

After executing the code for creating an index, we can query the data content of emp_pay again, as shown in:

We can see that the data content has been sorted by employee ID.

We continue to execute the previous query about employee ID = 5 and click "show estimated execution plan", as shown in:

The content of the clustered index search is:

Summary:

When we create an index for a field in the database table and use this field in the WHERE clause in the query statement, the query efficiency will be improved, in the above experiment, the query efficiency of the data volume relationship is not significantly improved.

Supplement

The index we added above is a unique clustered index. Therefore, an error is reported when the inserted data is duplicated in the employee ID field. If we repeat the data fields before creating an index, we cannot create a unique index.

Sorting after index creation (PS)

Execute the following SQL statement

update emp_pay set employeeID=7 where employeeID=1;

Then execute the full table query again. The query results are as follows:

As long as we update the employee ID, the final update results will be sorted in ascending order according to the value of the employee ID. This is because we have created an index on the employee ID.

Delete An index (PS)

You can use the SQL Server Management studio tool to delete an index, or use an SQL statement to delete an index. Assume that you want to delete the index "employeeid_ind" created earlier, the SQL statement is shown in the following code:

DROP INDEX employeeID_ind ON emp_pay;