Index with SORT IN TEMPDB

Index with SORT IN TEMPDB is a SQL statement in the Indexes category. SQL Server: uses tempdb for sorting during index creation. Reduces fragmentation in the target database. The syntax is CREATE INDEX idx_name ON table (col) WITH (SORT_IN_TEMPDB = ON);. It returns index creation option. A typical example: CREATE INDEX idx_employees_name ON employees (last_name) WITH (SORT_IN_TEMPDB = ON); -- Sorting happens in tempdb (separate disk I/O) -- Index in the target database is less fragmented -- Overall index creation time may increase -- But resulting index has better page density -- When to use: -- - Large indexes (rebuilds) -- - tempdb on fast storage (SSD) -- - Target database space is limited -- When not to use:… A close relative is CREATE INDEX, which creates an index on one or more columns to speed up query performance. A close relative is DROP INDEX, which removes an existing index from a table.