Solved

SQL Server - Index

Posted on 2013-11-22
5
598 Views
Last Modified: 2013-11-22
I have a table that gets additional data added to it each night.  ID field is auto-increment.

1.  I want an index on the ID field.  What type of index should I use?
2.  Do I need I to re-index each night?
2.  If so, is there an easy way to re-index the ID field?  (script?)
0
Comment
Question by:patriotpacer
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39668900
1.  Not enough information here to properly answer the question.  Does this table have frequent insert-updates-deletes?  Are there columns that are frequently searched?  Are the columns to be searched mostly large text fields (i.e. full-text search)?  Does the table have historical data that cannot be backed up to another location?  

2a.  No, unless we're talking a ginormous amount of data here, and then maybe there would be a need for re-indexing, re-generating statistics.

2b.  Explain what you mean by 're-indexing'.
0
 

Author Comment

by:patriotpacer
ID: 39668913
>> Does this table have frequent insert-updates-deletes?

Frequent inserts and updates


>>Are there columns that are frequently searched?

Searches will be by ID column



>>2b.  Explain what you mean by 're-indexing'.

Let me ask differently.   When do you need to drop the index and re-create?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39668944
>Frequent inserts and updates
Then the table should not have a lot of indexes, as every action query such as insert-update (if it touches the index columns)-delete will affect the rows AND the indexes, which will take more time.

>Searches will be by ID column
Then an excellent start would be a clustered index on only the ID column.

>When do you need to drop the index and re-create?
When it is determined that query execution on the table is too slow, and re-design or re-modeling the table is not desired.
0
 

Author Closing Comment

by:patriotpacer
ID: 39668960
Thank you!

I really appreciate your help.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39668974
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now