Proper Index for a compound column?

I have tables Sales, Trucks, Merchandise, Marine and Real Estate.
Sales is parent and is a 1:1 for Trucks, Marine, Property. It's 1:M for
Merchandise.
Sales has InvID + AssetType as columns.
All the others have InvID + AssetType also. they would be Assettype 1,2,3,4
for the respective invnetory types
For fast joins on sales reports should I combine the 2 int columns as an
index, or are they combined and because they are int, they are added
together?
TIA
__StephenSQL Server only keeps statistics on the first column of the index.
It can (and does) use multiple indexes in the same WHERE clause.
It is recommended that you have an index for each column
that is commonly used in your WHERE clause.
--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:uQmpnJQbFHA.348@.TK2MSFTNGP14.phx.gbl...
>I have tables Sales, Trucks, Merchandise, Marine and Real Estate.
> Sales is parent and is a 1:1 for Trucks, Marine, Property. It's 1:M for
> Merchandise.
> Sales has InvID + AssetType as columns.
> All the others have InvID + AssetType also. they would be Assettype
> 1,2,3,4
> for the respective invnetory types
> For fast joins on sales reports should I combine the 2 int columns as an
> index, or are they combined and because they are int, they are added
> together?
> TIA
> __Stephen
>
>

Proper Index for a compound column?

I have tables Sales, Trucks, Merchandise, Marine and Real Estate.
Sales is parent and is a 1:1 for Trucks, Marine, Property. It's 1:M for
Merchandise.
Sales has InvID + AssetType as columns.
All the others have InvID + AssetType also. they would be Assettype 1,2,3,4
for the respective invnetory types
For fast joins on sales reports should I combine the 2 int columns as an
index, or are they combined and because they are int, they are added
together?
TIA
__StephenSQL Server only keeps statistics on the first column of the index.
It can (and does) use multiple indexes in the same WHERE clause.
It is recommended that you have an index for each column
that is commonly used in your WHERE clause.
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:uQmpnJQbFHA.348@.TK2MSFTNGP14.phx.gbl...
>I have tables Sales, Trucks, Merchandise, Marine and Real Estate.
> Sales is parent and is a 1:1 for Trucks, Marine, Property. It's 1:M for
> Merchandise.
> Sales has InvID + AssetType as columns.
> All the others have InvID + AssetType also. they would be Assettype
> 1,2,3,4
> for the respective invnetory types
> For fast joins on sales reports should I combine the 2 int columns as an
> index, or are they combined and because they are int, they are added
> together?
> TIA
> __Stephen
>
>

Proper Index for a compound column?

I have tables Sales, Trucks, Merchandise, Marine and Real Estate.
Sales is parent and is a 1:1 for Trucks, Marine, Property. It's 1:M for
Merchandise.
Sales has InvID + AssetType as columns.
All the others have InvID + AssetType also. they would be Assettype 1,2,3,4
for the respective invnetory types
For fast joins on sales reports should I combine the 2 int columns as an
index, or are they combined and because they are int, they are added
together?
TIA
__Stephen
SQL Server only keeps statistics on the first column of the index.
It can (and does) use multiple indexes in the same WHERE clause.
It is recommended that you have an index for each column
that is commonly used in your WHERE clause.
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:uQmpnJQbFHA.348@.TK2MSFTNGP14.phx.gbl...
>I have tables Sales, Trucks, Merchandise, Marine and Real Estate.
> Sales is parent and is a 1:1 for Trucks, Marine, Property. It's 1:M for
> Merchandise.
> Sales has InvID + AssetType as columns.
> All the others have InvID + AssetType also. they would be Assettype
> 1,2,3,4
> for the respective invnetory types
> For fast joins on sales reports should I combine the 2 int columns as an
> index, or are they combined and because they are int, they are added
> together?
> TIA
> __Stephen
>
>
sql

Proglem creating index

I am having a problem creating an index. When I check the table for dupes I don't find any. When I try to create the key I get an error that there are duplicated values. I think that this may be because the key is too long? I don't see any other reason. The data looks OK.

Each client should only have one record for a date (the 1st of the month) and a paysource.

create unique index UniqueArId on AR(CLIENTID, [DATE], PaysourceUUID)

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '400007'.
The statement has been terminated.

Int datetime uniqueid
400007 2005-04-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-05-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-06-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760

402161 2004-01-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64
402161 2004-02-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64
402161 2004-03-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64

The rows below seem to be the duplicates:

400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760|||Sorry, thats a typo in the email, it is not that way in the database.|||

Can you post a repro script that shows the problem? What is the output of the query below?

select CLIENTID, [DATE], PaysourceUUID, count(*) as dupes
from AR
group by CLIENTID, [DATE], PaysourceUUID
having count(*) > 1
order by dupes desc;