Indexers in SQL Server

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view.
An index contains keys built from one or more columns in the table or view
These keys are stored in a structure (B-tree)
What is difference between Clustered indexes and Nonclustered indexes.

Clustered indexes:

Clustered indexes sort and store the data rows in the table or view based on their key values

There can be only one clustered index per table

Clustered indexes are implemented in the following ways:

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created

You can specify a unique clustered index if a clustered index on the table does not already exist.

CREATE CLUSTERED INDEX IX_CompanyTable_ProductIDCol
    ON dbo.ComapnyTable (ProductIDCol);

Non Clustered indexes

Nonclustered indexes are in separate structure from the data rows. A nonclustered index contains the key values  each key value entry has a pointer and that pointer points to the data row of the table.This pointer is know as row loactor.

There can be more than one Nonclustered index per table

By default, a nonclustered index is created if clustered is not specified

CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID
    ON Purchasing.ProductVendor (BusinessEntityID);

Ex:Columns that contain a large number of distinct values, such as a combination of last name and first name


How Indexes Are increase performance of Query?

SELECT Title, ExpireDate FROM Company.Products WHERE ProductID = 250;

When this query is executed, the query optimizer evaluates each available method for retrieving the data and selects the most efficient method.The method may be a table scan, or may be scanning one or more indexes if they exist.

When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations

When the query optimizer uses an index, it searches the index key columns, finds matching rows needed by the query and extracts the matching rows from that location.

The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan



Thanks for visiting this blog. How is the content?. Your comment is great gift to my work. Cheers.

No comments:

Post a Comment