Solved

SQL Server - Index

Posted on 2013-11-22
5
601 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

831 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