Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involve
d,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)

> TIA,
> Norbert

Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)
> TIA,
> Norbert

Question regarding use of a join to the same table

I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
identifier. A BU/Asset combination can have up to 4 rows, with different
BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
situational).
Due to bad data entry, some of the Assets were not given an AMT row. I have
to identify those BU/Asset combinations.
I know I need to do a join back to the BOOK table, but I can't seem to get
the right statement. Here is my SQL:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
right outer join PS_BOOK B
on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
A.ASSET_ID = B.ASSET_ID
where B.BOOK <> 'AMT'
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
This gives me a double lisitng of rows (of course) and I can visually scan
for those missing 'AMT', but that's way too clunky. Where am I shooting
myself in the foot? What is the correct statement to use to get just a list
of the BU/Asset combinations that are missing 'AMT'?
TIA,
jej1216I tried a simpler stetment:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
where EXISTS (select 'x'
from PS_BOOK B
where B.BOOK <> 'AMT')
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
But I still get both the BU/Asset combination that has 'AMT' as well as the
combination that does not have 'AMT'.
- jej1216
"Joe" wrote:
> I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
> identifier. A BU/Asset combination can have up to 4 rows, with different
> BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
> situational).
> Due to bad data entry, some of the Assets were not given an AMT row. I have
> to identify those BU/Asset combinations.
> I know I need to do a join back to the BOOK table, but I can't seem to get
> the right statement. Here is my SQL:
> select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> from PS_BOOK A
> right outer join PS_BOOK B
> on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
> A.ASSET_ID = B.ASSET_ID
> where B.BOOK <> 'AMT'
> order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> This gives me a double lisitng of rows (of course) and I can visually scan
> for those missing 'AMT', but that's way too clunky. Where am I shooting
> myself in the foot? What is the correct statement to use to get just a list
> of the BU/Asset combinations that are missing 'AMT'?
> TIA,
> jej1216
>|||On Tue, 16 Oct 2007 08:42:02 -0700, Joe wrote:
>I tried a simpler stetment:
>select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>from PS_BOOK A
>where EXISTS (select 'x'
> from PS_BOOK B
> where B.BOOK <> 'AMT')
>order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>But I still get both the BU/Asset combination that has 'AMT' as well as the
>combination that does not have 'AMT'.
Hi Joe,
I think you want this one:
SELECT a.BusinessUnit, a.AssetID, a.Book
FROM PS_Book AS a
WHERE NOT EXISTS
(SELECT *
FROM PS_Book AS b
WHERE b.BusienssUnit = a.BusinessUnit
AND b.AssetID = a.AssetID
AND b.Book <> 'AMT');
If that is not it, then see www.aspfaq.com/5006 to find the information
you need to post in order to make it possible for us to help you.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis