SQL Indexes - SQL Create Index Statement
Demo wikitechydatabase
- Below is a selection from the wikitechytable table used in the examples:
SQL CREATE INDEX Statement
- The CREATE INDEX statement is used to create indexes in tables.
- Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.
CREATE INDEX Syntax
- Creates an index on a table. Duplicate values are allowed:
CREATE INDEX idx_name ON wikitechytable (name);
Output
CREATE UNIQUE INDEX Syntax
- Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX idx_gender ON wikitechytable (gender);
Output
- The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.wikitechytable' and the index name 'idx_gender'. The duplicate key value is (female). The statement has been terminated.
SHOW INDEXES
- You can use the sp_helpindex system stored procedure:
EXEC sp_helpindex 'wikitechytable';
Output
DROP INDEX Statement
- The DROP INDEX statement is used to delete an index in a table.
DROP INDEX idx_name ON wikitechytable;
Output
