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

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

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
--

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
--

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
--

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
--

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.
>