What is indexing and how to create indexes and why?

Indexing makes columns faster to query by creating  pointers to where data is stored within a database.

To create index, use the CREATE INDEX command:

1. syntax create index_name on table-name (column1,column2..,columnN):- create index on one column create index products_category on products(category);

2. create index on multiple columns create index product -category_brand on products(category, brand-id); 

indexes prevent duplicate entries in the column or combination of columns on which it is created. since sql indexs are primarily a performance tool, they are most useful when a database  grows in size. the clustered index is one of the most popular types of indexes supported by sql server.

Type of Indexes: 

1 Clustered index - Only one can be created in the one table 

2. Non-clustered index - Can create 999 indexes in one table.

We can rebuild the index or reorganize the index. 

Based on fragmentation level you can rebuild or reorganized the index.