Friday, March 23, 2012

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

No comments:

Post a Comment