Advice on indexes

I have a table (detail) with 4 columns and over 30 million rows .
Say the columns are called ColA, ColB, ColC, ColD

ColA and ColB form a foreign key as this table is a 'detail' table for another master table in the database (master). The master table uses these two columns as its primary key.
To speed up joins between the two tables I have created an index over ColA and ColB

I frequently need to join the two tables together and so most of my queries are of the form:

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail
ON master.ColA = detail.ColA and master.ColB = detail.ColB
WHERE master.ColA = @.valA
ORDER BY master.ColB, detail.ColC

This could return up to 60000 rows for a specific value of @.valA.
This is usually a sub-query that feeds directly into another query or a temporary table.

However, I also quite often need to filter the details further by ColC so I have queries like

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail

ON master.ColA = detail.ColA and master.ColB = detail.ColB

WHERE master.ColA = @.valA AND detail.ColC = @.valC

ORDER BY master.ColB

The results set from this could be just a few hundred rows depending on @.valC

I clearly have a requirement to have an index across ColA, and ColB, presumably as the clustered index.

My question is how to speed up queries that involve ColC.
Do I create another index across ColA, ColB and ColC or just ColC by itself?
Will an index just on ColC make use of the the clustered index on the other columns?
Should I make the clustered index cover ColA, ColB and ColC instead ? etc ..

I am using SQL Express and am approaching the 4GB limit and this table and another one similar to it account for 96% of the database size.
The four columns in the detail table are all integer types and so don't take up much space per row. I am worried that a wide index will significantly add to the storage space per row and therefore significantly reduce the amount of data I can store in the database.

Any advice would be appreciated.

You defintely do not ever want to create one index that is a subset of another as you asked, ie. do not create two indexes on

ColA, ColB

ColA, ColB, ColC

The one on ColA, ColB, ColC does everything that the first one does, so it is all you need.

In your case it sounds like a clustered index on ColA, ColB, ColC should be all you need. Leaving out one column won't make much difference to the size. The size will be affected by the fillfactor and the fragmentation level of the index, so you should rebuild the index from time to time (how often depends on the nature of your updates) with a fillfactor of 100%, although a high fillfactor will increase the possibility that some updates will be slower.

|||Thanks for that.

I have done as you have said and created an index across all three columns and I am happy with both the speed of the queries and the size of the database.sql

Advanced Expression?

I'm creating a report that looks through detail records and want it to sum
amounts based on a record type. If FieldType = 1 to sum accounts in a range
otherwise to sum all records with that account. The result is an error
[BC30201] Expression expected. The formula I have so far is:
=iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
db.owner.table with (nolock) where Fields!account between
Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
I've also tried VB's Select/Case statement rather than IIF but it still
returns an error.
If this can't be done with expressions, can it be done someother way?
Thanks,
RickThis seems you might want to try creating a stored procedure and using the sp
as the basis of your report, not trying to calc that in a report cell. You
can't use a select statement from within the iif statement.
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick|||Mike,
Thanks for your reply. Are you referring to creating a stored procedure in
SQL or does RS have the ability to create stored procedures as well, like in
Report properties.custom code?
Since my post I came to the same conclusion you mention that what I'm trying
to do won't work in the reporting cell so I've been looking into creating a
function using custom code but as you mention, the select statement can't be
used within IIF. Can a stored procedure be called from IIF?
As a general custom code question, can C as well as VB code be used?
Thanks again!
Rick
"mike" wrote:
> This seems you might want to try creating a stored procedure and using the sp
> as the basis of your report, not trying to calc that in a report cell. You
> can't use a select statement from within the iif statement.
> "rickp3131" wrote:
> > I'm creating a report that looks through detail records and want it to sum
> > amounts based on a record type. If FieldType = 1 to sum accounts in a range
> > otherwise to sum all records with that account. The result is an error
> > [BC30201] Expression expected. The formula I have so far is:
> >
> > =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> > db.owner.table with (nolock) where Fields!account between
> > Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> >
> > I've also tried VB's Select/Case statement rather than IIF but it still
> > returns an error.
> >
> > If this can't be done with expressions, can it be done someother way?
> >
> > Thanks,
> > Rick|||You can try an SQL user defined function returning a table with summary
details, then use the IIF statement to get more specific, or you can use a
"Where" statement in your dataset
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick