Index In Sql?

An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table. So, the key points to remember are that an index consists of column values from one table, and that those values are stored in a data structure.

Index is a database object, which can be created on one or more columns (16 Max column combination for SQL Server).
Indexes allow the database application to find data fast; without reading the whole table.
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify

Precautions:

Indexes should not be used on small tables.
Tables that have frequent, large batch update or insert operations.
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.

Syntax:

CREATE INDEX index_name ON table_name (column_name);
CREATE UNIQUE INDEX index_name ON table_name (column_name);
CREATE INDEX index_name ON table_name (column_name1, column_name2);
CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);

-- Create a non-clustered index
CREATE INDEX fyi_links_created ON fyi_links (created);
GO
-- display all index on table
EXEC SP_HELP <table name;
DROP INDEX index_name;

MySql Index:
ALTER TABLE table_name ADD INDEX (column1), ADD UNIQUE INDEX (Colum2);

--Drop Index
ALTER TABLE T1 DROP INDEX B, DROP INDEX C;
or
DROP INDEX B ON T1; DROP INDEX C ON T1;

CREATE TABLE (
id NUMBER NOT NULL,
[...]
CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id) )

 

#index  #mysql index  # database