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