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
If you have small table so optimizer will choose scan table to retrieve
the data. An index may improve the performance if your WHERE clause searches
for a few rows and the index is created on selective (at least 95%) column
Just testing,testing....
"Tom Williams" <Tom.Williams@.ncmail.net> wrote in message
news:O4dVMNqRGHA.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.
>
|||if you modify tables without any indexes, you may end up locking up the
whole table.
Also the best practice is to have a primary key for every table.
|||[repost]
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
|||Bah.
small reference tables do not require indexes. Keys are kind of nice,
but the average schmuck dba can ususally figure out how a reference
table works.
for really small tables, it is about 6's to add an index or not.
Personal preference.
My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
pretty close to 20.
|||Doug wrote:
> Bah.
> small reference tables do not require indexes. Keys are kind of nice,
> but the average schmuck dba can ususally figure out how a reference
> table works.
> for really small tables, it is about 6's to add an index or not.
> Personal preference.
> My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
> pretty close to 20.
The job of a key is to help out the DBA? That's a good one!
I really hope you meant your reply ironically. If not, I hope no
database I see will ever be a victim of your rule of thumb.
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