Clustered vs Non-Clustered

From: Julie (anonymous_at_discussions.microsoft.com)
Date: 05/14/04 

Date: Fri, 14 May 2004 06:28:04 -0700

A Clustered Index has to be unique otherwise it will
report an error. By default all primary keys are clustered.

So

either leave OrderId as clustered
or
OrderNumber
or
Any combination that includes either OrderNumber or
OrderID.

This will depend upon your joins, so you will need to find
whether OrderID or OrderNumber will be queried the most.

Question, why have an OrderId when OrderNumber is unique?,
couldn't that be set as the primary key ?.

J

>-----Original Message-----
>Let's suppose we have the following tables:
>
>CREATE TABLE Customers (
> CustomerID int IDENTITY PRIMARY KEY,
> CustomerName varchar(255) NOT NULL UNIQUE
>)
>
>CREATE TABLE Orders (
> OrderID int IDENTITY PRIMARY KEY,
> OrderDate smalldatetime NOT NULL,
> OrderNumber varchar(10) NOT NULL UNIQUE,
> CustomerID int NOT NULL REFERENCES Customers (CustomerID)
>)
>
>CREATE TABLE Products (
> ProductID int IDENTITY PRIMARY KEY,
> ProductName varchar(50) NOT NULL UNIQUE
>)
>
>CREATE TABLE OrderDetails (
> OrderDetailID int IDENTITY PRIMARY KEY,
> OrderID int NOT NULL REFERENCES Orders (OrderID),
> ProductID int NOT NULL REFERENCES Products (ProductID),
> Quantity numeric(18,3) NOT NULL,
> UNIQUE (OrderID, ProductID)
>)
>
>Which indexing strategy would better for the Orders table:
>
>a) make OrderID clustered and OrderDate, OrderNumber and
CustomerID
>non-clustered,
>
>or
>
>b) make OrderDate clustered and OrderID, OrderNumber and
CustomerID
>non-clustered,
>
>or
>
>c) other strategies ?
>
>As you may guess, the Orders table will be frequently
used in joins
>with the other tables: sometimes Orders and Customers,
sometimes
>Orders and OrderDetails, sometimes all 4 tables. Many
queries (but not
>all) will be for a date range of one month (i.e. WHERE
OrderDate
>BETWEEN '20040101' AND '20040131').
>
>Please take into consideration:
>1) the efficiency of clustered vs non-clustered indexes
for the types
>of queries that will be used;
>2) the fact that OrderID is an identity column (therefore
it will have
>unique values, the values will always be greater than the
existing
>values and the values cannot be modified)
>3) the fact that OrderDate is not unique (and because of
this will
>have a lower selectivity than OrderID)
>4) any other aspects that you consider to be relevant.
>
>After giving a response to this scenario, please consider
another
>scenario: the same tables using uniqueidentifier primary
keys (instead
>of int identity). The same question applies: which index
should be
>clustered?
>
>Razvan Socol
>.
>

Relevant Pages

  • Re: Doubling the order
    ... Other than the orderId, there is nothing to say that two orders are the ... > 1.PRODUCTID) - primary Key ... >> relational database and it is not the best one. ... the gap in the sequence is not filled in and the sequence ...
    (microsoft.public.sqlserver.programming)
  • Re: Allen Brownes Copying forms/Subforms
    ... Is OrderID the primary key? ... Allen Browne - Microsoft MVP. ... perhaps the duplicate is on another field where you specified a "No ...
    (microsoft.public.access.modulesdaovba)
  • Re: Allen Brownes Copying forms/Subforms
    ... The "OrderID" is the primary key. ... Allen Browne - Microsoft MVP. ... perhaps the duplicate is on another field where you specified a "No ...
    (microsoft.public.access.modulesdaovba)
  • Re: Clustered vs Non-Clustered
    ... > either leave OrderId as clustered ... > OrderNumber ... > couldn't that be set as the primary key ?. ... >> OrderDate smalldatetime NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: For getting Idea
    ... clustered and non-clustered indexes on the primary key. ... select OrderID, minas minProd ... set statistics io off ...
    (microsoft.public.sqlserver.clients)