Prev: Manipulating complex joined query in a view
Next: Please help with Select Statement (Zip File)
From: Omid Golban on 22 Jul 2008 17:12
Consider a variation of the classical order header and detail example:

CREATE TABLE OrderHeader (
OrderID INT, <- PK
OrderNumber VARCHAR(10), <- uniqe (alternate key)
CustomerID INT, <- FK
...)

CREATE TABLE OrderDetail (
OrderDetailID INT, <- PK
OrderID INT, <- FK
ItemID INT, <- FK
...)

The unique column OrderNumber in OrderHeader is indexed for quick access.
Let's say that I often want to select all OrderDetails for a specific OrderNumber, but I don't want anything else from OrderHeader.
My dilema would be that I would like to create an index for OrderNumber (not OrderID) in OrderDetail but I don't want the column in
OrderDetail.

What would you suggest?

Thank you,
Omid

From: Tom Cooper on 22 Jul 2008 17:26
You can't put an index on OrderNumber on the OrderDetail table unless
OrderNumber is in the OrderDetail table. And, you are right, you probably
don't want to do that. But instead just do

Select <whatever>
From Orders o
Inner Join OrderDetails od On o.OrderID = od.OrderID
Where o.OrderNumber = '<order you want>'

It is true that that will get one order from the OrderHeader and you aren't
otherwise using that row. But the cost of getting that order will be small
as long as you have either a unique constraint or unique index on
Order.OrderNumber.

Just make sure you have an index on OrderDetail.OrderID. If you do a lot of
queries of this type, you might want to consider making the index on
OrderDetail.OrderID the clustered index of that table.

Tom

"Omid Golban" <ogolban(a)cox.net> wrote in message
news:9EE59D74-B5E7-4BA9-B8FF-3E6ACCF2DEAD(a)microsoft.com...
> Consider a variation of the classical order header and detail example:
>
> CREATE TABLE OrderHeader (
> OrderID INT, <- PK
> OrderNumber VARCHAR(10), <- uniqe (alternate key)
> CustomerID INT, <- FK
> ...)
>
> CREATE TABLE OrderDetail (
> OrderDetailID INT, <- PK
> OrderID INT, <- FK
> ItemID INT, <- FK
> ...)
>
> The unique column OrderNumber in OrderHeader is indexed for quick access.
> Let's say that I often want to select all OrderDetails for a specific
> OrderNumber, but I don't want anything else from OrderHeader.
> My dilema would be that I would like to create an index for OrderNumber
> (not OrderID) in OrderDetail but I don't want the column in OrderDetail.
>
> What would you suggest?
>
> Thank you,
> Omid


From: Michael C on 22 Jul 2008 17:29
"Omid Golban" <ogolban(a)cox.net> wrote in message
news:9EE59D74-B5E7-4BA9-B8FF-3E6ACCF2DEAD(a)microsoft.com...
> Consider a variation of the classical order header and detail example:
>
> CREATE TABLE OrderHeader (
> OrderID INT, <- PK
> OrderNumber VARCHAR(10), <- uniqe (alternate key)
> CustomerID INT, <- FK
> ...)
>
> CREATE TABLE OrderDetail (
> OrderDetailID INT, <- PK
> OrderID INT, <- FK
> ItemID INT, <- FK
> ...)
>
> The unique column OrderNumber in OrderHeader is indexed for quick access.
> Let's say that I often want to select all OrderDetails for a specific
> OrderNumber, but I don't want anything else from OrderHeader.
> My dilema would be that I would like to create an index for OrderNumber
> (not OrderID) in OrderDetail but I don't want the column in OrderDetail.
>
> What would you suggest?

Joining to OrderHeader?


>
> Thank you,
> Omid


From: Eric Isaacs on 22 Jul 2008 17:32
I assume you also have an index on your order detail table on
OrderID? If not, add one before you try anything else.

An indexed view would provide what you want, but would require the
view to be schemabound to the underlying tables. Then refer to the
indexed view instead of the table when you want to lookup the details
by OrderNumber.

The other obvious alternative is to link the tables on the OrderNumber
instead of the OrderID.

-Eric Isaacs
From: Roy Harvey (SQL Server MVP) on 22 Jul 2008 17:39
You do not say if the Primary Keys are clustered. Since that is the
default I am going to assume they are. If they are not ignore what
follows.

Every non-clustered index on a table that is clustered carries the
column(s) of the clustering key, even if they are not referenced in
the non-clustered index. (After traversing the non-clustered index it
uses the clustered index key to traverse the clustered index to reach
the actual row.) So if OrderHeader is clustered on the PK of
(OrderID), and the non-clustered index on OrderHeader(OrderNumber)
includes OrderID. Given that, suppose we have a query:

SELECT A.OrderNumber, B.*
FROM OrderHeader as A
JOIN OrderDetail as B
ON A.OrderID = B.OrderID

In that query all references to OrderHeader are "covered" by the
non-clustered index on OrderNumber. Hopefully the optimizer
recognizes that and uses it, and the query above never touches the
non-clustered index.

However, suppose we don't want to take any chances. In that case, we
could create a view, and make it an indexes view.

CREATE VIEW OrderIDbyOrderNumber As
SELECT OrderNumber, OrderID
FROM OrderHeader;

CREATE UNIQUE CLUSTERED INDEX OrderIDbyOrderNumber_Ix
ON OrderIDbyOrderNumber (OrderNumber);

The reason for this approach is that unlike a basic view, an indexed
view is the equivalent of an actual physical table that is maintained
by the system when there are changes to the table(s) that make up the
view. So the virtual table of the view actually is a physical table
internally.

Now we can go back to that first query and change it to use the view:

SELECT A.OrderNumber, B.*
FROM OrderIDbyOrderNumber as A
JOIN OrderDetail as B
ON A.OrderID = B.OrderID

You could simply write the queries you asked about that way, or you
could even make it a view.

Roy Harvey
Beacon Falls, CT

On Tue, 22 Jul 2008 14:12:30 -0700, "Omid Golban" <ogolban(a)cox.net>
wrote:

>Consider a variation of the classical order header and detail example:
>
>CREATE TABLE OrderHeader (
> OrderID INT, <- PK
> OrderNumber VARCHAR(10), <- uniqe (alternate key)
> CustomerID INT, <- FK
> ...)
>
>CREATE TABLE OrderDetail (
> OrderDetailID INT, <- PK
> OrderID INT, <- FK
> ItemID INT, <- FK
> ...)
>
>The unique column OrderNumber in OrderHeader is indexed for quick access.
>Let's say that I often want to select all OrderDetails for a specific OrderNumber, but I don't want anything else from OrderHeader.
>My dilema would be that I would like to create an index for OrderNumber (not OrderID) in OrderDetail but I don't want the column in
>OrderDetail.
>
>What would you suggest?
>
>Thank you,
>Omid
 |  Next  |  Last
Pages: 1 2 3
Prev: Manipulating complex joined query in a view
Next: Please help with Select Statement (Zip File)