Covering Index

Covering Index
The query we’ve chosen to optimize gives us the opportunity to explore a special kind of index, the covering index. A covering index is one that contains all the columns referenced in the query for a single table. We will demonstrate just how useful this can be.

Here is our example query:

SET STATISTICS IO ON;
GO
USE AdventureWorks;
SELECT ss.Name, COUNT(*) as OrderCount, SUM(sd.LineTotal) as TotalPurchases
FROM Sales.SalesOrderDetail sd
JOIN Sales.SalesOrderHeader sh on sd.SalesOrderID = sh.SalesOrderID
JOIN Sales.Customer sc on sh.CustomerID = sc.CustomerID
JOIN Production.Product p on sd.ProductID = p.ProductID
LEFT JOIN Sales.Store ss on sc.CustomerID = ss.CustomerID
WHERE ss.Name is not null
-- and sd.SpecialOfferID = 13
GROUP BY ss.Name
ORDER BY SUM(sd.LineTotal) desc
This query joins on several tables but almost all of the cost of the query is in the full scan on the largest table, SalesOrderDetail, at a cost of 1175 page reads. the query returns 633 rows. (We find this information in the Messages tab of the query window in Management Studio after setting STATISTICS IO ON.) When we examine the query, there seems to be no way to reduce the cost of the table scan through indexing. In this query, SalesOrderDetail is joined on the SalesHeaderId and ProductID columns, but those columns are already indexed. The only other place that an index might improve things is on a column in the WHERE clause, but there are no search arguments in the where clause that apply to this table. The only search argument is on the Sales.Store table. There is no obvious column that we can index to improve the performance on SalesOrderDetail.
But notice that there are only three columns from SalesOrderDetail referenced in this query. They are SalesOrderID, ProductID and LineTotal. If we create an index that includes all those columns it is said to “cover the query”. That means that the query engine can get all the data it needs from the index without touching the table at all. This can lead to performance improvements that range from moderate to absolutely amazing.
CREATE NONCLUSTERED INDEX nc_cover on Sales.SalesOrderDetail(productid) INCLUDE (linetotal) Notice that we use the INCLUDE clause to add the LineTotal column to this index. INCLUDE was a new feature of the CREATE INDEX statement in SQL 2005. It allows us to include columns that are not keys without incurring the substantial cost of adding more key values to the index. Columns that have no value for filtering, joining or ordering results can be added to the index at lower cost than simply naming them in the traditional CREATE INDEX syntax.
You might also notice that we have not mentioned SalesOrderID in this CREATE INDEX statement. How can this be a covering index if we leave out a column that is used in the query? The answer is that we have not left it out.
In a table having a clustered index, all nonclustered indexes include the clustering key. SalesOrderID is in the clustering key, therefore it is automatically added to any nonclustered index we create. There is no need to explicitly name it. When we create this index, the cost of accessing SalesOrderDetail drops from 1175 data pages to 486 pages. This is not an earthshaking improvement, but it does illustrate the principle we are discussing.

10 Things To Treat Acidity

10 things to treat acidity and its recurrence at bay:

Bananas: Are high in potassium which is an alkalizing mineral that has a high pH value. The higher the pH value, the lower the acidity, which is why the banana is a formidable antidote to acidity. They also have a component that makes the stomach lining produce more mucous. This mucous protects the inner lining of the stomach, reducing the damage caused due to acidity. It is high in fibre content, which helps speed along digestion thus reducing the further recurrence of acidity. To make this remedy even more effective try and eat overripe bananas during an attack of acidity. They have higher amounts of potassium making it even more effective. Read about the health benefits of bananas.

Tulsi: Has compounds that make it an effective digestive agent. It stimulates the stomach to produce more mucous and has potent anti-ulcer properties. It also reduces the effect of peptic acids in the stomach thereby preventing excessive acidity. They also reduce gas formation. Eat five to six basil leaves after your meal to get instant relief. Read about the health benefits of tulsi.

Cold milk: Milk has a high amount of calcium that helps it prevent acid build up and absorbs the excess acid produced thereby reducing the symptoms and the fact that it is cold provides instant relief from the burning sensation one feels during acid reflux. It is essential that one has this cold and without any additives like sugar. Mix it with a spoon of ghee to make the remedy even more effective.

Saunf: Called fennel in English and popularly used as a mouth freshener, saunf has many hidden benefits. Among them are aiding digestion reliving constipation. Rich in volatile oils containing flavanoids, plamitic acid and many others, it is known to have very potent anti-ulcer properties. Apart from that it acts as a coolant for the lining of the stomach helping it to heal faster. In fact, this is why saunf is served after meals at restaurants. In case you’ve a sudden bout of acidity, boil a few seeds in water and leave overnight, drink this water when you feel uneasy.

Jeera: It has properties that stimulate the production of saliva which helps in better digestion, improves metabolism and relieves gas and other gastric troubles. In Ayurvedic traditions, it’s believed to have a calming effect on irritated stomach nerves and helps in healing ulcers formed due to acid secretion. One can either chew on it to relieve acidity or a better method is to boil it in water and drink the water when it has cooled down.

Clove: It is a natural carmitive and helps improve peristalisis (the movement of food down the stomach) and also increase the production of saliva. It has a pungent taste when bitten into, and the taste buds react to this taste and secrete excess saliva, which in turn aids digestion. In case you’re suffering from acidity, bite a clove once so that the juice is released and then keep it in the mouth. The slowly escaping juice will immediately lower acid reflux and give you some relief.

Elaichi: In Ayurvedic traditions, cardamom is believed to be one food that balances all three doshas – kapha, pitta and vata. It is known to stimulate digestion and relieve people of stomach spasms. It soothes the mucous lining of the stomach helping it ward off the effects of excess acid produced in the stomach. Due to its sweet taste and slight cooling effect it also relieves the burning sensation associated with acidity. To relieve acidity crust two pods of cardamom (with or without the skin), boil the powder in water and drink the cooled juice for instant relief.

Mint leaves or pudina: These leaves have been used for ages as a mouth freshener and also for garnishing for various foods. This humble leaf has amazing properties that make it just the medicine to treat acidity. It also lowers the acid content of the stomach and improves digestion. Plus its cool effect helps to reduce the pain and burning associated with acid reflux. If you feel an attack coming on chop some mint leaves, add them to a pot of boiling water and drink this water after it cools.

Ginger: A regular ingredient in most Indian households, ginger improves absorption and assimilation of essential nutrients and aids in digestion. It also helps break down the proteins in your food. Ginger protects your stomach against ulcers by promoting mucus secretion thereby reducing the effect of the acid on the stomach. You can chew a piece of ginger for relief. If you find that too pungent, you can try boiling it in water and drinking the decoction. Another remedy is that you can crush ginger, mix it with a small piece of jaggery and keeping sucking on it allowing the juice to slowly reach the stomach. Read about the health benefits of ginger.

Amla: Is a kapha and pitta pacifier apart from having high amounts of vitamin C that helps in healing the injured stomach lining and oesophagus. Have one teaspoon of amla powder twice a day to help keep acidity way. Read about the health benefits of amla. So the next time you feel like you might suffer from a bout of acidity, ditch that bottle of antacid, and try some effective home remedies instead, after all they’ve been around for centuries Take Care Thanks for Reading Mayank Nagar