INDEXING in SQLSERVER

  

What is INDEXING in SQL? 📢
 
✔️Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
✔️An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.
✔️An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements.

🔎When should Indexes be created?
- A column contains a wide range of values, and table has millions of rows.
- A column does not contain a large number of null values.
- One or more columns are frequently used together in a where clause or a join condition.

🔎When should indexes be avoided?
- The table is small.
- The columns are not often used as a condition in the query.
- The column is updated frequently.

📍Syntax to create an Index:

CREATE INDEX INDEX_SALES ON [Sample - Superstore_Orders]

(Order_ID)

 

📍Syntax to remove an Index:
DROP INDEX index;

📍Syntax to alter an Index:
ALTER INDEX INDEX_SALES ON  [Sample - Superstore_Orders]

REBUILD