Re: Clustered vs Non-Clustered
From: Andres Taylor (andres_at_rotselleri.com)
Date: 05/14/04
- Next message: Renae: "Using a comma delimited array in a where clause"
- Previous message: Julie: "xp_cmdShell"
- In reply to: Julie: "Clustered vs Non-Clustered"
- Next in thread: Aaron Bertrand - MVP: "Re: Clustered vs Non-Clustered"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 14 May 2004 15:37:42 +0200
A clustered index might well be non-unique. Try it yourself. If it is not
unique, SQL Server will pad it with a 4-bytes uniquefier, to tell rows
apart.
That's the [?] column you see with DBCC PAGE printopts 3 on an non-unique
clustered index page.
Cheers,
Andrés
"Julie" <anonymous@discussions.microsoft.com> wrote in message
news:d1b201c439b7$4954a920$a601280a@phx.gbl...
> 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
> >.
> >
- Next message: Renae: "Using a comma delimited array in a where clause"
- Previous message: Julie: "xp_cmdShell"
- In reply to: Julie: "Clustered vs Non-Clustered"
- Next in thread: Aaron Bertrand - MVP: "Re: Clustered vs Non-Clustered"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
