Small table and rows

• Hollyz

  SSCommitted

  Points: 1763

  More actions

  September 16, 2008 at 2:15 pm

  #197865

  There are many articles says that small table you probably don't need any indexes because the exeuction plan will do better doing a full table scan than doing the index search.

  So my question is What is considered small table size? Does anyone know how many rows is considered as a small table? When is the table considered medium size and same for Big table size?

• SanjayAttray

  SSChampion

  Points: 13157

  More actions

  September 16, 2008 at 2:30 pm

  #872704

  HKwai (9/16/2008)

  ---

  There are many articles says that small table you probably don't need any indexes because the exeuction plan will do better doing a full table scan than doing the index search.

  So my question is What is considered small table size? Does anyone know how many rows is considered as a small table? When is the table considered medium size and same for Big table size?

  There is no hard coded rule to define a small or large table.

  1) I had a table1 with 252 columns and 1 composite Pkey and 81 non-clustered index on it and has around 150 thousand rows.

  2. Another table2 has 16 columns and 1 Pkey and 3 non-clustered index on it and has just less than 1 million records.

  For me managing performance and statistics for table1 is more difficult that table2, even though table1 has six times less rows than table2.

  Others can differ with my experience.

  SQL DBA.

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  September 16, 2008 at 2:31 pm

  #872706

  IMO it is IO related so I would say 8 pages (i.e. 64KB )

  However a table should have a primary key (and most tables have)

  Also keep in mind, a unique constraint is also materialized as an index

  I even think sql2005 will not use an index if the data page level is very small.:unsure:

  (Maybe others have more experience with that)

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me