Performance Issues

• maechismo_8514

  SSCoach

  Points: 17510

  More actions

  March 8, 2009 at 5:55 pm

  #955749

  By default SQL server creates a clustered index on a primary key and a non clustered index on a unique key. As a rule of thumb yes, every table should have one clustered index. For a small table it really does not really affect performance, but yes for a large table it gives drastic performance.

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  March 8, 2009 at 6:03 pm

  #955751

  radek (3/8/2009)

  ---

  Hi I have other question about indexes:-)

  I read something about indexes, that indexes is good create in "big table"

  But when I create table in MS SQL by GUI and define keys it create automatically cluster indexes on keys..

  Why?

  So I have many small tables about 100 rows with cluster indexes...

  Im not sure if is better redefine it and leave small table like heap,without cluster indexes...

  Which way is better, or faster?

  Thank a lot Radek

  '

  Heh... as others have already said, because that's the default and, if you don't want that, you have to include something in your code to override the default.

  My recomendation, at this point, is for you to hit Books Online. I recommend you start here... (paste the following into the search bar in Books Online)...

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0c0c74cd-5545-4508-bf26-1cea5aca1f0b.htm

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)