# Index Creation and Management
> [!Note]
> `= Precise control over index characteristics—type, included columns, filtering predicates, and advanced options—enables tailored data structures that maximize query speed and minimize storage overhead.`
In SQL Server, the **CREATE INDEX** statement provides granular control over how an index is built and maintained. You can declare an index as **UNIQUE**, **CLUSTERED**, or **NONCLUSTERED**, determining both its logical structure and impact on the base table.
> [!tip]
> **Index Types**
> Specify **CLUSTERED** to order the table rows themselves, or **NONCLUSTERED** to maintain a separate structure of key pointers. Use **UNIQUE** when you need to enforce no-duplicate rules.
The **INCLUDE** clause lets you attach non-key columns to the leaf level of your index so that more queries become **covering**, eliminating the need to fetch additional columns from the table.
> [!info]
> **Included Columns**
> Adding columns via `INCLUDE` enriches the index without affecting its sort order, turning seeks into full coverage.
With **filtered indexes**, the **WHERE** clause restricts the index to a subset of rows, reducing index size and improving maintenance costs.
> [!warning]
> **Filtered Index**
> Filters must align closely with frequent query predicates; poorly chosen filters can render the index ineffective.
Finally, **advanced options** under the **WITH** clause—such as **FILLFACTOR**, **SORT_IN_TEMPDB**, and **ONLINE**—allow you to tune performance, control page density, offload sorting overhead, and maintain availability during rebuilds.
> [!success]
> **Advanced Options**
> Use **FILLFACTOR** to balance page fullness versus fragmentation and **ONLINE=ON** for zero-downtime rebuilds.
```sql
CREATE [ UNIQUE ]
[ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH ( <relational_index_option> [ ,...n ] ) ];
```
---
## References
- Korotkevitch, D. (2022). _SQL Server advanced troubleshooting and performance tuning: Best practices and techniques_. O’Reilly Media.
- Nevarez, B. (2022). _SQL Server query tuning and optimization: Optimize Microsoft SQL Server 2022 queries and applications_. Packt Publishing.