Thursday, 3 May 2007

Performance tuning using Include columns in SQL Server 2005

SQL Server 2005 extends the functionality of non clustered indexes by adding non key columns to the leaf level of the non clustered index using the INCLUDE option in the CREATE INDEX statement. These INCLUDE index option is a slight variation of covering index for improved performance. By including non key columns, you can create non clustered indexes that cover more queries. The benefits of using the INCLUDE option (also called non key non clustered index) in the INDEX are

Advantages

  • All data types are supported, except text, ntext, and image. So more data type options than a covering index.
  • The maximum number of columns that can be included is 1024, where as only 16 in covering indexes.
  • Included Columns are not considered by the Database Engine when calculating the number of index key columns or index key size.The actual index is narrower so the key can be more efficient and can offer better performance where as in covering index all of the columns are part of the key.

The include columns indexes are also having the same disadvantages of the covering indexes such as

  • More space is required to store indexes with non key columns. Non key column data is stored at both the leaf level of the index and in the table itself.
  • Larger indexes mean fewer rows can fit on a page, potentially increasing disk I/O.
  • Index maintenance is increased for data modifications, potentially hurting performance if non key columns are large and the database experiences a high level of data modifications.

Syntax for INCLUDE column

CREATE INDEX IX_INDEX1
ON dbo.Employee (KEYCOLUMN1)
INCLUDE (NONKEYCOLUMN1, NONKEYCOLUMN2, AND NONKEYCOLUMN3);

However care should be taken before converting the covering indexes to non key / include column indexes. The execution plan of both the INDEX options should be compared before deciding the best INDEX option.

8 comments:

Anonymous said...

Its such as you learn my thoughts! You seem to know a lot approximately this, like you wrote the e book in it or something.
I believe that you simply can do with a few percent to pressure the message
house a bit, however instead of that, that is
fantastic blog. A fantastic read. I will certainly be back.
Here is my page ... click the next website page

Anonymous said...

I don't even know how I ended up here, but I thought this post was great. I do not know who you are but definitely you are going to a famous blogger if you are not already ;) Cheers!
Feel free to visit my blog post :: Man Nude Porn Video

Anonymous said...

Why users still make use of to read news papers when in this technological world
everything is presented on net?
Look into my blog post free porn

Anonymous said...

A person necessarily help to make seriously articles I
might state. That is the very first time I frequented your website page and up
to now? I surprised with the analysis you made to make this actual publish extraordinary.
Fantastic task!
Feel free to surf my web page :: Young And Tasty

Anonymous said...

I've been surfing online more than 2 hours today, yet I never found any interesting article like yours. It's pretty worth enough for me.

Personally, if all website owners and bloggers
made good content as you did, the internet will be a lot more useful than ever
before.
Also see my web page :: contractor in Orlando

Anonymous said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Anonymous said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Anonymous said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03