Tutorial by Examples

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId); This will create an index for the column EmployeeId in the table Cars. This index will improve the speed of queries asking the server to sort or select by values in EmployeeId, such as the following: SELECT * FROM Cars WHERE EmployeeId = 1 ...

Indexes can have several characteristics that can be set either at creation, or by altering existing indexes. CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email); The above SQL statement creates a new clustered index on Employees. Clustered indexes are indexes that dict...

UPDATE Customers SET Email = "[email protected]" WHERE id = 1; This will fail if an unique index is set on the Email column of Customers. However, alternate behavior can be defined for this case: UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUP...

This command will drop index in the table. It works on SAP ASE server. Syntax: DROP INDEX [table name].[index name] Example: DROP INDEX Cars.index_1

If you use an index that is sorted the way you would retrieve it, the SELECT statement would not do additional sorting when in retrieval. CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC); When you execute the query SELECT * FROM scoreboard ORDER BY score DESC; The database system ...

DROP INDEX ix_cars_employee_id ON Cars; We can use command DROP to delete our index. In this example we will DROP the index called ix_cars_employee_id on the table Cars. This deletes the index entirely, and if the index is clustered, will remove any clustering. It cannot be rebuilt without rec...

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

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...

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 ...

Nonclustered indexes are stored separately from the table. Each index in this structure contains a pointer to the row in the table which it represents. This pointers are called a row locators. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered tab...

SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows. Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of orders with order_state_id equal to started (1). If your business make ...