PageRank
Information Technology, IT catalyzes your business. IT facilitates your life!       
USD $1
Begin main content

Best practices for creating indexes [www.codeproject.com]

There are a several tips you should keep in mind when implementing indexing.

  • Keep indexes lean. Try to build indexes on one or few columns at most. Wide indexes take longer to scan than narrow indexes.
  • Create the clustered index on every table. However, choose the column(s) for the clustered index judiciously. Try to create the clustered index on the column which is used most frequently for retrieving data.
  • Try to create the clustered index on the column with high selectivity; that is the column that does not have many duplicate values.
  • Try to create the clustered index on the column that will never be updated or will be updated infrequently. Every time the clustered index key is updated, SQL Server must maintain not just the clustered index but also the non-clustered indexes since non-clustered indexes contain a pointer to the clustered index. This is yet another reason why you shouldn't create the clustered index on multiple columns.
  • By default, SQL Server creates the clustered index on the PRIMARY KEY column(s) unless the clustered index is created prior to creating the primary key. It is often beneficial to have the clustered index on the primary key, but sometimes you're better off saving the clustered index for other column(s). Feel free to override the default behavior if your testing shows that clustered index on a non-key column will help your queries perform better.
  • SQL Server has to perform some maintenance when index keys are updated, so each additional index could impose a slight performance penalty on the performance of INSERT, UPDATE and DELETE statements. Nevertheless the cost of index maintenance could be far less than the performance improvement you'll reap for your SELECT statements. Therefore you can be fairly liberal with the number of non-clustered indexes.
  • Be sure to eliminate duplicate indexes, that is, multiple indexes created on the same set of columns. Such indexes provide no additional benefit but use up extra disk space and might hinder performance of INSERT, UPDATE and DELETE statements.
  • Check the default fill factor level configured at the server level. If you don't specify the FILLFACTOR option with CREATE INDEX statement, every new index will be created using the default fill factor. This may or may not be what you intend.
  • Non-clustered indexes can be created in different file groups which can reside on separate disk drives to improve the data access i.o. I/O operations.

07:31 PM, 19 Mar 2014 by Iuri Sampaio Permalink | Comments (0)

In every age, science and technology have played an important role in advancing human civilization. From architecture to engineering, communication to transportation, humans have invented and developed extraordinary wonders. The myriad of methods we have used to communicate with each other throughout history have changed our society. Today's tools of communication have the power to spread information across the planet in a matter of seconds. Millions of human beings are now interconnected through advancements in communication technology. In this book, we'll explore seven wonders of communication, capturing the journey from the throaty grunts of our ancestors that formed our first language; to hieroglyphs and the development of written communication; to telephones and cell phones, the World Wide Web, and robot communication. Along the way, we'll see how visual technologies enhanced our communication, how the speed of communication keeps accelerating, and what challenges the future holds. We'll also learn the stories of how and why these communication advancements became important to the world.

02:52 PM, 16 Mar 2014 by Iuri Sampaio Permalink | Comments (0)

XML

Archive

March 2014
S M T W T F S
           
10  11  12  13  14  15 
16  17  18  19  20  21  22 
23  24  25  26  27  28  29 
30  31           
November 2016
August 2016
June 2016
April 2016
March 2016
February 2016
January 2016
December 2015
October 2015
September 2015
August 2015
May 2015
March 2015
December 2014
June 2014
March 2014
September 2013
April 2013
March 2013
February 2013
January 2013
December 2012
November 2012

Notifications

Icon of envelopeRequest notifications

Syndication Feed

XML

Recent Comments

  1. Unregistered Visitor: xmpcOKZArBMdRs
  2. Unregistered Visitor: HAivjBFtqUIRHPrK
  3. Unregistered Visitor: YNvYYeIQtN
  4. Unregistered Visitor: VEDxsclRsOJcTaW
  5. Unregistered Visitor: NIwEAqESBvDBTWUG
  6. Unregistered Visitor: RJWDbORpiBgr
  7. Unregistered Visitor: dBsvVwbjLxCGKx
  8. Unregistered Visitor: MSKUawaCswTtzViGJyP
  9. Unregistered Visitor: CGMHBUBvIsAngqGFI
  10. Unregistered Visitor: aotHMhlirvfQzNutGG