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
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.
-- Create a non-clustered index
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 INDEX fyi_links_created ON fyi_links (created);
-- display all index on table
EXEC SP_HELP <table name;
DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX (column1), ADD UNIQUE INDEX (Colum2);
ALTER TABLE T1 DROP INDEX B, DROP INDEX C;
DROP INDEX B ON T1; DROP INDEX C ON T1;
CREATE TABLE (
id NUMBER NOT NULL,
CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id) )