Re: How to best use clustered index?

From: AndrewV (nospam_at_yahoo.com)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 15:14:35 -0800

Thanks very much for the advices, really appreciate it Tom, Michael. Have a
great day guys.

"AndrewV" <nospam@yahoo.com> wrote in message
news:uB5OGvk8DHA.2316@TK2MSFTNGP09.phx.gbl...
> I have an Order table that is used heavily. About 90% of queries is
looking
> for 1 particular sales order and is joining with other tables via the
> primary key to get relevant data (select * from Order inner join
OtherTable
> on Order.OrderKey = OtherTable.OrderKey where Order.OrderKey = @OrderKey).
> The remaining 10% is looking for the same set of data, but over a date
range
> (select * from Order inner join OtherTable on Order.OrderKey =
> OtherTable.OrderKey where Order.OrderKey = @OrderKey and Order.CreatedDate
> between x and y).
>
> My thought is to clustered index on the primary key since it is used alot
> for joining with other tables. Or is it better to clustered index on the
> CreateDate field because it is sometimes queried for a date range? Is
> clustered index best used on a column that is used heavily for joining, or
> on a column that will be queried for a range of data?
>
> Thanks very much in advance.
>
>

Relevant Pages

  • Re: materialized view vs. denormalized table
    ... This happens a lot to improve efficiency of the queries at the ... cost of storage space. ... Server stores the clustered index on the view. ... You do not have to repopulate the clustered index as data is modified in the ...
    (microsoft.public.sqlserver.server)
  • Re: Index Performance
    ... What is true for SQL-Server 7.0 and above is especially true for 6.5: ... the clustered index is very useful for ... queries you describe). ... > StartDate datetime NULL, ...
    (microsoft.public.sqlserver.server)
  • Re: Need to tune a table for performance gains
    ... it didnot have any pks and hence no clustered index. ... IX_4 on ColA, ColB and ColC put together. ... Queries performed in this table are very slow. ...
    (comp.databases.ms-sqlserver)
  • Re: SalesDetail table
    ... easily be a problem depending on your indexes and what queries you write. ... queries were INSERTs and SalesID was always increasing. ... Basically my query will be a join between SalesDetails table and the Sales without the Qty criteria: ... Key does not have to be your clustered index, a different set of columns may be a better choice and choosing the correct clustered index is important), then adding a nonclustered index on SalesID would most likely not be helpful. ...
    (microsoft.public.sqlserver.server)
  • Re: SalesDetail table
    ... easily be a problem depending on your indexes and what queries you write. ... queries were INSERTs and SalesID was always increasing. ... Basically my query will be a join between SalesDetails table and the Sales ... Key does not have to be your clustered index, ...
    (microsoft.public.sqlserver.server)