Author Topic: Avoid creating indexes on large string columns.  (Read 2527 times)

kaluahir16

  • Newbie
  • *
  • Posts: 20
    • View Profile
Avoid creating indexes on large string columns.
« on: September 19, 2012, 06:57:41 PM »
Avoid creating indexes on large string columns.
Regards,
Kalu Bhuva

BhushanShah

  • Global Moderator
  • Jr. Member
  • *****
  • Posts: 73
  • BhushanShah21
    • View Profile
Re: Avoid creating indexes on large string columns.
« Reply #1 on: September 20, 2012, 08:12:04 PM »
When you design an index consider the following column guidelines:

  • Keep the length of the index key short for clustered indexes. Additionally, clustered indexes benefit from being created on unique or nonnull columns. For more information, see Clustered Index Design Guidelines.
  • Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. For more information, see Index with Included Columns.
  • Examine data distribution in the column. Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones




Regards,
Bhushan Shah

Ritesh Shah

  • Administrator
  • Jr. Member
  • *****
  • Posts: 57
  • Ritesh Shah
    • View Profile
    • IT Technology Blog
Re: Avoid creating indexes on large string columns.
« Reply #2 on: September 21, 2012, 03:22:23 PM »
Thanks Bhushan.... This detail was due in my list but you did my work. :)
Ritesh Shah
Administrator
Extreme-Advice.com
SQLHub.com