create index on table

Hi,
What's the difference/performance difference when you
create a index with two columns combined vs create two
indexes with each one of them as showed below?
1.
CREATE INDEX [index1] ON [dbo].[table1]([MARKET], [DATE])
WITH FILLFACTOR = 75 ON [primary]
2.
CREATE INDEX [index1] ON [dbo].[table1]([MARKET]) WITH
FILLFACTOR = 75 ON [primary]
CREATE INDEX [index1] ON [dbo].[table1]([DATE]) WITH
FILLFACTOR = 75 ON [primary]
thanks a lot!
JJOne Index with both columns will provide roughly the same index relief as
two individual columns.
Two separate indexes adds over head as each individual index has to be
maintained by SQL Server.
Greg Jackson
PDX, Oregon|||"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:122f001c4429d$86cb8500$a301280a@.phx.gbl...
> Hi,
> What's the difference/performance difference when you
> create a index with two columns combined vs create two
> indexes with each one of them as showed below?
>
An index can only be accessed by its leading column(s). So an index on two
columns is usefull when accessing the table by the leading column of the
index or both the columns of the index. But an index on two columns cannot
be used when accessing the table by the second columns of the index only.
So in your example
CREATE INDEX [index1] ON [dbo].[table1]([MARKET], [DATE])
WITH FILLFACTOR = 75 ON [primary]
index1 cannot be used to filter
select * from table1 where date = '2002-05-05'
but can be used to filter
select * from table1 where market = 3 and date = '2002-05-05'
and a query of the form
select market, date from table1 where market = 1
is covered by the query and can be processed completely from the index
without hitting the base table.
On the other hand two seperate indexes have more overhead, and don't do
particularly well with queries which specify both columns, as either index
may be used but not both.
select * from table1 where market = 3 and date = '2002-05-05'
David|||Actually that is not quite true on two counts. The idea is correct but the
details are misleading.
> An index can only be accessed by its leading column(s). So an index on
two
SQL Server can actually still use the index when searching for the second
column but not with a SEEK, only a SCAN. If no other index is available it
may be cheaper to scan the compound index than scanning the entire table.
But obviously this is not the recommended way to do this.
> On the other hand two separate indexes have more overhead, and don't do
> particularly well with queries which specify both columns, as either index
> may be used but not both.
This is not totally true either. SQL Server can in fact use two separate
indexes in what is called "Index Intersection" to find common rows between
the two indexes. So if you do have a situation where you need to search on
both columns individually it is often helpful to have two separate indexes.
Sometimes you may find having a compound index and a second one with a
single column useful as well. As always it depends.
--
Andrew J. Kelly
SQL Server MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23UTTNJqQEHA.644@.tk2msftngp13.phx.gbl...
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:122f001c4429d$86cb8500$a301280a@.phx.gbl...
> > Hi,
> >
> > What's the difference/performance difference when you
> > create a index with two columns combined vs create two
> > indexes with each one of them as showed below?
> >
> An index can only be accessed by its leading column(s). So an index on
two
> columns is usefull when accessing the table by the leading column of the
> index or both the columns of the index. But an index on two columns
cannot
> be used when accessing the table by the second columns of the index only.
> So in your example
> CREATE INDEX [index1] ON [dbo].[table1]([MARKET], [DATE])
> WITH FILLFACTOR = 75 ON [primary]
> index1 cannot be used to filter
> select * from table1 where date = '2002-05-05'
> but can be used to filter
> select * from table1 where market = 3 and date = '2002-05-05'
> and a query of the form
> select market, date from table1 where market = 1
> is covered by the query and can be processed completely from the index
> without hitting the base table.
>
> On the other hand two seperate indexes have more overhead, and don't do
> particularly well with queries which specify both columns, as either index
> may be used but not both.
> select * from table1 where market = 3 and date = '2002-05-05'
> David
>|||wow! you are all so knowledgable! thank you so much for
being so helpful!
JJ
>--Original Message--
>Hi,
>What's the difference/performance difference when you
>create a index with two columns combined vs create two
>indexes with each one of them as showed below?
>1.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET],
[DATE])
>WITH FILLFACTOR = 75 ON [primary]
>2.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET]) WITH
>FILLFACTOR = 75 ON [primary]
>CREATE INDEX [index1] ON [dbo].[table1]([DATE]) WITH
>FILLFACTOR = 75 ON [primary]
>
>thanks a lot!
>JJ
>.
>|||wow! you are all so knowledgable! thank you so much for
being so helpful!
JJ
>--Original Message--
>Hi,
>What's the difference/performance difference when you
>create a index with two columns combined vs create two
>indexes with each one of them as showed below?
>1.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET],
[DATE])
>WITH FILLFACTOR = 75 ON [primary]
>2.
>CREATE INDEX [index1] ON [dbo].[table1]([MARKET]) WITH
>FILLFACTOR = 75 ON [primary]
>CREATE INDEX [index1] ON [dbo].[table1]([DATE]) WITH
>FILLFACTOR = 75 ON [primary]
>
>thanks a lot!
>JJ
>.
>