22. Heap table, Covering index, index with included columns in SQL| Performance tuning in SQL Server
This Video contains In-depth coverage of What is a HEAP Table, Covering index and What is an index with included columns. Covers all of the important queries and SQL commands. In this videos series, you'll learn how to read and write complex queries. Handle tricky SQL interview questions. Implement complex SQL queries and answer popular interview questions on SQL. These videos series will help to crack interviews and get jobs in SQL Developer, Database Developer, Data Engineer, Reporting Analyst, Data Analyst, Data Scientist, System Analyst, Power BI Developer, Tableau Developer, BI Developer, etc.
-----------------------------------------------------------------------------------------------------------------
Are you someone who is looking for Real-time training, visit https://pandeyguruji.graphy.com/ and join for free training materials, Link: https://bit.ly/30vrk1W
For any query Contact me on social media:
Facebook : / mpandeyguruji
Instagram: / pandey_guruji
Linkedin: / mukesh-pandey-1a151634
Telegram: https://t.me/pandeyguruji
-------------------------------------------------------------------------------------------------------------------
SQL Tutorial for Beginners in Hindi: https://bit.ly/3CwfnGB
SQL Tutorial for Beginners in English: https://bit.ly/3qVI3GQ
SQL Live Training: https://bit.ly/3kXqOl1
Crack SQL Interview: https://bit.ly/3caxIyb
SQL Interview Questions: http://bit.ly/2CR6wCZ
Advance SQL Interview Questions: https://bit.ly/3HnMrnN
Career Guidance: https://bit.ly/3FffkRf
IT Technology News & Updates: https://bit.ly/3FeNQvc
--------------------------------------------------------------------------------------------------------------------
What is a HEAP
A heap table is a special type of table that does not have a clustered index defined on it.
With a heap structure the table data is not stored in any particular order. Heap tables
can be used when the data coming into the table is random and has no natural order but
non-clustered indexes should always be created on heap tables. If there are no
non-clustered indexes defined when the table is queried all the data would have to be
scanned and sorted in almost all cases leading to very poor performance.
The following example shows how without any indexes on a heap structure the entire
table is scanned and the final dataset also needs to be sorted before being returned.
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderID BETWEEN 43755 AND 43759
ORDER BY SalesOrderID, SalesOrderDetailID;
Covering index
A covering index is an index which is made up of all (or more) of the columns required
to satisfy a query as key columns of the index. When a covering index can be used to
execute a query, fewer IO operations are required since the optimizer no longer has to
perform extra lookups to retrieve the actual table data.
Below is an example of the TSQL you can use to create a covering index on the Product
table.
/ pandeyguruji One-to-one Training:https://pandeyguruji.graphy.com/
What is an index with included columns
An index created with included columns is a non-clustered index that also includes
non-key columns in the leaf nodes of the index, similar to a clustered index. There are a
couple benefits to using included columns. First it gives you the ability to include
column types that are not allowed as index keys in your index. Also, when all the
columns in your query are either an index key or included column, the query no longer
has to do an extra lookup in order to get all the data needed to satisfy the query which
results in fewer disk operations. This is similar to the covering index mentioned earlier.
Using the same example from above the following TSQL will create the same index
except with the ProductNumber column referenced as an included column and not an
index key column.
CREATE NONCLUSTERED INDEX IX_Production_ProductNumber_Name
ON Production.Product (Name ASC) INCLUDE (ProductNumber);
SELECT ProductNumber, Name FROM Production.Product WHERE Name = 'Cable Lock';
#Call_Now_8309569513_Realtime_One_to_One_Training