Index questions

Q1: When creating indexes, which is the best way:
1. Create one index for each column which needs to be indexed?
2. Create one index, which contains all the columns, which need to be indexe
d?
Q2: Does indexex affect the datafile (physical file) size a lot?Hi
http://msdn.microsoft.com/library/d...>
_05_2ri0.asp
What you are describing is a composite index.
http://www.sql-server-performance.c...ite_indexes.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Vonasek" wrote:

> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be inde
xed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>|||Creating too many indexes also brings down the performance of the system as
the sorting needs to be done on the data files.
Create indexes only on those columns which you think are involved in a WHERE
or ORDER BY clause.
thanks and regards
Chandra
"David Vonasek" wrote:

> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be inde
xed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>|||Regarding your first question, it depends.
The first column of the index is very important. Because the optimiser
decides to use that index if your WHERE clause is querying the first column
of the index.
Sometime it is useful to have a covered index. That is a composite index
that contains all the columns required by a special query. See "Designing an
Index" in BOL for more info.
Indexes do consume space. Also, having too many indexes on an OLTP
(read/write) database hurts write performance too.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Vonasek" <David Vonasek@.discussions.microsoft.com> wrote in message
news:F09D8517-35D2-4F15-9526-7B161C0230A9@.microsoft.com...
Q1: When creating indexes, which is the best way:
1. Create one index for each column which needs to be indexed?
2. Create one index, which contains all the columns, which need to be
indexed?
Q2: Does indexex affect the datafile (physical file) size a lot?|||Another possibility to consider is whether or not the criteria that you
typically query on is fixed or not. For example if you really only have one
query and that query has criteria on 3 columns, then a covering index will
probably be best. If you support lots of adhoc queries and there may be
criteria on one or more of lets say 8 columns, it may make better sense to
just create 8 individual indexes and the the optimizer use index intersectio
n
in the query plans.
"Narayana Vyas Kondreddi" wrote:

> Regarding your first question, it depends.
> The first column of the index is very important. Because the optimiser
> decides to use that index if your WHERE clause is querying the first colum
n
> of the index.
> Sometime it is useful to have a covered index. That is a composite index
> that contains all the columns required by a special query. See "Designing
an
> Index" in BOL for more info.
> Indexes do consume space. Also, having too many indexes on an OLTP
> (read/write) database hurts write performance too.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "David Vonasek" <David Vonasek@.discussions.microsoft.com> wrote in message
> news:F09D8517-35D2-4F15-9526-7B161C0230A9@.microsoft.com...
> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be
> indexed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>
>