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!
JJ
One 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...
> 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
>

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...
> 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], [DA
TE])
> 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
>

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

Create Difference Query

I have a table that has participant scores for each performance and I want
to select all those instances where the score changed by 3.0 or more (up or
down) points in a 2 w period. A participant may be scored twice on any
given day. To try to clarify, the table includes the following basic
information:
Unit Name
Performance Date
Score
What I want to get is:
"Unit A1", 3/12/2005, 57.7
"Unit A1", 3/13/2005, 62.4
"Unit A2", 3/5/2005, 62.2
"Unit A2", 3/5/2005, 63.5
"Unit A2", 3/12/2005, 58.8
etc.
I cannot get my head around this? Should I look at an SP instead of just a
query?
Any help is much appreciated.
WayneBTW, here is what I am trying right now:
Select Distinct y.Unit, y.Class, y.Contest, y.Date, y.[Gross Score] From YTD
y
Inner Join YTD y2 on y.Unit = y2.Unit
Where (ABS(y.[Gross Score]-y2.[Gross Score]) > 3)
AND y.Class = y2.Class
AND DATEDIFF(day, y.[Date], y2.[date]) < 14
Order By y.Class, y.Unit, y.[Date]
But the result set is including cases where the score difference is less
than 3?
Wayne
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:uqCNGC$JFHA.2212@.TK2MSFTNGP12.phx.gbl...
> I have a table that has participant scores for each performance and I want
> to select all those instances where the score changed by 3.0 or more (up
or
> down) points in a 2 w period. A participant may be scored twice on any
> given day. To try to clarify, the table includes the following basic
> information:
> Unit Name
> Performance Date
> Score
> What I want to get is:
> "Unit A1", 3/12/2005, 57.7
> "Unit A1", 3/13/2005, 62.4
> "Unit A2", 3/5/2005, 62.2
> "Unit A2", 3/5/2005, 63.5
> "Unit A2", 3/12/2005, 58.8
> etc.
> I cannot get my head around this? Should I look at an SP instead of just a
> query?
> Any help is much appreciated.
> Wayne
>|||On Sun, 13 Mar 2005 11:24:41 -0700, Wayne Wengert wrote:

>BTW, here is what I am trying right now:
>Select Distinct y.Unit, y.Class, y.Contest, y.Date, y.[Gross Score] From YT
D
>y
>Inner Join YTD y2 on y.Unit = y2.Unit
>Where (ABS(y.[Gross Score]-y2.[Gross Score]) > 3)
>AND y.Class = y2.Class
>AND DATEDIFF(day, y.[Date], y2.[date]) < 14
>Order By y.Class, y.Unit, y.[Date]
>But the result set is including cases where the score difference is less
>than 3?
Hi Wayne,
Am I correct that you didn't get a reply yet? Or did my newsreader miss
it?
Anyway, my first guess would be to add
AND y2.[date] > y.[date]
somewhere in the query.
If that doesn't fix it, then I need to be able to reproduce it. For
that, I need a CREATE TABLE statement (including all constraints and
properties, but excluding irrelevant columns), some chosen rows of
sample data (as INSERT statements - and make sure to include both rows
that should and rows that should not be returned), and the expected
output. Check out www.aspfaq.com/5006 as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the reply Hugo. Yours is the only one I got. I ended up breaking
it into two queries and added the results to a temp table. Kludgy but it got
the job done.
Maybe later I'll come back to see if I can figure out the right way to do
it.
Wayne
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7poe31l1icu89gd1q7a8lsj1lnclum2eb2@.
4ax.com...
> On Sun, 13 Mar 2005 11:24:41 -0700, Wayne Wengert wrote:
>
YTD
> Hi Wayne,
> Am I correct that you didn't get a reply yet? Or did my newsreader miss
> it?
> Anyway, my first guess would be to add
> AND y2.[date] > y.[date]
> somewhere in the query.
> If that doesn't fix it, then I need to be able to reproduce it. For
> that, I need a CREATE TABLE statement (including all constraints and
> properties, but excluding irrelevant columns), some chosen rows of
> sample data (as INSERT statements - and make sure to include both rows
> that should and rows that should not be returned), and the expected
> output. Check out www.aspfaq.com/5006 as well.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)