[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

SQL Server - Index

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
patriotpacer
Asked:
patriotpacer
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
patriotpacerAuthor Commented:
>> 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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
patriotpacerAuthor Commented:
Thank you!

I really appreciate your help.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now