Index Question

When I have tables with small rows and few rows, are indexes needed?
ex: the U.S. federal government numbers every county in the USA. In my
state there are 100 counties. Create Table Counties (
CountyCode smallint,
CountyName VarChar(30),
Region smallint)
All data for all 100 counties will easily be read by SQL in one physical
read. So, is there any reason to create an index? Most applications
written in my office have lookup tables with a small number of small
rows. Same question.
Thanks in advance
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
Tom Williams wrote:
> When I have tables with small rows and few rows, are indexes needed?
> ex: the U.S. federal government numbers every county in the USA. In my
> state there are 100 counties. Create Table Counties (
> CountyCode smallint,
> CountyName VarChar(30),
> Region smallint)
> All data for all 100 counties will easily be read by SQL in one physical
> read. So, is there any reason to create an index? Most applications
> written in my office have lookup tables with a small number of small
> rows. Same question.
> Thanks in advance
> Tom
> --
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
Every table should have at least one key. Keys exist to maintain the
integrity of your data not for performance. In SQL Server a key
constraint, whether PRIMARY KEY or UNIQUE, always has an index. So the
answer is yes, but not all indexes are required for performance
reasons.
BTW, under SQL Server's default ANSI configuration your sample table
can't have a key because all the columns are nullable. If the CREATE
TABLE statement you posted is accurate then I suggest you fix that.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx