Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server - Index

Posted on 2013-11-22
5
Medium Priority
?
639 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 66

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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

Expert Comment

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 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