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.Since all tables should have primary keys - and PK's are enforced with
indexes - then the answer is Yes. Also, this enforces the likely rule that
county names are unique within your state.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Williams" <Tom.Williams@.ncmail.net> wrote in message
news:u9VfKFqRGHA.5924@.TK2MSFTNGP09.phx.gbl...
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.|||To add on to Tom's response, follow database design Best Practices and
create primary keys, unique constraints and foreign key constraints.
Constraints and indexes provide the query optimizer with useful information
and efficient data access paths. You get 'free' indexes for primary key and
unique constraints. Although not a requirement, it's often a good idea to
index foreign key columns too.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Williams" <Tom.Williams@.ncmail.net> wrote in message
news:u9VfKFqRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> 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.
>|||To add even more<g>. An index or constraint will also allow row level
locking where as the only choice is table level without them. So even on
small tables, especially ones that are updated you want proper indexes.
Andrew J. Kelly SQL MVP
"Tom Williams" <Tom.Williams@.ncmail.net> wrote in message
news:u9VfKFqRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> 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.
>