composite indexes - a subtle question

I have a table like so
CREATE TABLE [dbo].[account] (
[pty_id] [int] NOT NULL ,
[sort_code] [int] NOT NULL ,
[account_no] [int] NOT NULL ,
[account_open_dt] [datetime] NULL ,
[account_close_dt] [datetime] NULL ,
[account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[category_code] [smallint] NULL ,
[prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
) ON [PRIMARY]
as you can see there's a primary key clustered index on account_no and
sort_code
I can link it up to any other table efficiently which also has sort_code and
account_no as part of a single index (primary or otherwise)
ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
etc...
and that will work fine...
Here's my question
if account_no is part or the sort_code/Account_no composite key (and
therefore index)
could I link another table to only the account_no column and still get some
indexing benefit
i.e can you join on a single column of a multiple column index and still get
some help from that composite index or does that composite index only work a
s
a single entity and in fact you need to create an extra nonclustered index
for that column on top of the other index it's participating in to get the
benefit of indexing on that column...
phew
I hope this makes sense
any help would be great appreciated
Regards and thanks in advance,
CharlesACharles,
When joining to the first column of a multi-column index, some
of the same optimizations are available--for example, a merge
join (if the other table also has a supporting index) or nested loop
joins with index ss. Depending on the exact query, the two-column
index may be just as useful as a separate one-column index or a
little less useful (typically because the two-column index takes up
more data pages because of duplications in the first column and the
presence of the second column even when duplicates are few), but it
should still help in most cases. The only real way to be sure is
to create the additional index and measure the performance.
Steve Kass
Drew University
CharlesA wrote:

>I have a table like so
>CREATE TABLE [dbo].[account] (
> [pty_id] [int] NOT NULL ,
> [sort_code] [int] NOT NULL ,
> [account_no] [int] NOT NULL ,
> [account_open_dt] [datetime] NULL ,
> [account_close_dt] [datetime] NULL ,
> [account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [category_code] [smallint] NULL ,
> [prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
> CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
> ) ON [PRIMARY]
>
>
>as you can see there's a primary key clustered index on account_no and
>sort_code
>I can link it up to any other table efficiently which also has sort_code an
d
>account_no as part of a single index (primary or otherwise)
>ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
>etc...
>and that will work fine...
>Here's my question
>if account_no is part or the sort_code/Account_no composite key (and
>therefore index)
>could I link another table to only the account_no column and still get some
>indexing benefit
>i.e can you join on a single column of a multiple column index and still ge
t
>some help from that composite index or does that composite index only work
as
>a single entity and in fact you need to create an extra nonclustered index
>for that column on top of the other index it's participating in to get the
>benefit of indexing on that column...
>phew
>I hope this makes sense
>any help would be great appreciated
>Regards and thanks in advance,
>CharlesA
>|||Charles,
Can you recreate your PK as (account_no, sort_code)? What are the
cardinalities of these 2 columns?|||Charles,
I know you'd like an immediate answer, but it might really help to
watch Kimberly Tripp's (SQL Server MVP) presentation at the following
website. I'm in the process of designing indexes and it was incredibly
helpful to watch the presentation. She keeps it simple but I finally
got it...
http://www.microsoft.com/uk/technet...aspx?videoid=29
Other links:
http://msevents.microsoft.com/CUI/E...e=en-U
S
http://www.sqlskills.com/blogs/kimb...
3-a58ba7b1265e
(The presentation took me a while to download and it is an hour long
but totally worth it.)|||Hi,
Whether you get performance benifit or not depends on the location of the
column in the order of the index key.
You want to join Account_no from tbl1 with Account no in tbl2 with
sort_code/Account_no as an index.
If the left most column in the index is sort_code i.e
Index(sort_code,Account_no) , you don't find any use of the index, it will
still go for an idex scan.
But if your index had been Index(Account_no,sort_code)
then this index is sufficient for joining Account_no alone or Account_no and
sort_code.
Hope I made sense :)|||Thanks everyone for your answers, much appreciated...
I will download that presentation when I'm at home, I've been dying to
understand indexing properly for ages now
as I say, thanks All
Regards,
CharlesA