Solved

SQL Server - Index

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax error with Dates where clause 10 50
xml files 7 29
Amazon RDS Server load 2 16
Setting variables in a stored procedure 5 23
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

830 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