Page 115 - SQL

This allows the table to retain the structure, along with the metadata about the index.

Critically, this retains the index statistics, so that it is possible to easily evaluate the change. If
warranted, the index can then later be rebuilt, instead of being recreated completely;

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Unique Index that Allows NULLS

CREATE UNIQUE INDEX idx_license_id
ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

This schema allows for a 0..1 relationship - people can have zero or one driving licenses and each
license can only belong to one person

Rebuild index

Over the course of time B-Tree indexes may become fragmented because of
updating/deleting/inserting data. In SQLServer terminology we can have internal (index page
which is half empty ) and external (logical page order doesn't correspond physical order).
Rebuilding index is very similar to dropping and re-creating it.

We can re-build an index with

ALTER INDEX index_name REBUILD;

By default rebuilding index is offline operation which locks the table and prevents DML against it ,
but many RDBMS allow online rebuilding. Also, some DB vendors offer alternatives to index
rebuilding such as REORGANIZE (SQLServer) or COALESCE/SHRINK SPACE(Oracle).

Clustered index

When using clustered index, the rows of the table are sorted by the column to which the clustered
index is applied. Therefore, there can be only one clustered index on the table because you can't
order the table by two different columns.

Generally, it is best to use clustered index when performing reads on big data tables. The
donwside of clustered index is when writing to table and data need to be reorganized (resorted).

An example of creating a clustered index on a table Employees on column Employee_Surname:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);

Non clustered index

Nonclustered indexes are stored separately from the table. Each index in this structure contains a

https://riptutorial.com/ 97