SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Index on small tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ahmeds08
Aged Yak Warrior

India
694 Posts

Posted - 01/03/2013 :  02:54:49  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Dear All,
Creating a index on a table which has less then 50 rows,will it be a benefit?

Thanks,
Javeed.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/03/2013 :  03:49:09  Show Profile  Reply with Quote
It depends ...though on most occasions it might not be used as its easier to scan entire table to get values rather than traversing through index tree.
But that being said, if number of columns are large and if index is covering one or selectivity is large, optimiser might choose to use the index

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/03/2013 :  03:59:29  Show Profile  Reply with Quote
talking benefitwise, the effect would negligible,if at all, for an index on such a small table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/03/2013 :  04:53:26  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
A friend had a case where a small table benefited a lot from a clustered index when it was used in a nested loop many times in the query plan - but that was an odd scenario nad was due to the internal strauctures built rather than the index itself.

The only way to tell is to try it and see.
You should probably have a unique contraint on every table.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000