Prev: Manipulating complex joined query in a view
Next: Please help with Select Statement (Zip File)
From: Uri Dimant on 23 Jul 2008 01:53
Omid
> Let's say that I often want to select all OrderDetails for a specific
> OrderNumber, but I don't want anything else from OrderHeader.

Lets take an example where you run a query like that
SELECT od.*

FROMOrderHeader AS o

INNER JOIN OrderDetail AS od

ON o.OrderID = od.OrderID

WHERE od.OrderQty > 20

It is important that OrderDetail table has an index on OrderID as well , so
in that case OrderHeader table will not be touched at all (Index
Scan/Clusterd Index Scan on OrderDetail table)

Also make sure that you created FK WITH CHECK option







"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: steve dassin on 23 Jul 2008 05:58
Can you restate what you mean by a 'dilemma'. Do you think there should be
a way to get details given an OrderNumber value (where OrderNumber is only
in the OrderHeader table) without the need to do a join between the 2
tables? This is not possible in sql but I'm curious if this is your
thinking.

www.beyondsql.blogspot.com

"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: Omid Golban on 23 Jul 2008 08:17
I was searching for a good way of selecting OrderDetail for a specific OrderNumber possibly without a join on OrderHeader. Everyone
has pointed out that I could always start my select from Orderheader and then join to the OrderDetail. Furthermore Eric suggested an
indexed view if I don't want the explicit join.

To further explain why I asked this question:
1. there are more fields and tables involved
2. all PK fields will be indexed (clustered)
3. There are usually several filters provided
3.1 sometimes the user may provide specific OrderNumber(s)
3.2 sometimes the user may provide OrderDate(s) instead, or some other filters
4. These queries are in stored procedures (SPs) that application developers will call

If I know that user always provides OrderNumber(s) then I can always start my select from OrderHeader:
SELECT *
FROM OrderHeader AS o
JOIN OrderDetail AS od
ON o.OrderID = od.OrderID
...
WHERE o.OrderNumber IN (user's list of order numbers)
AND o.Date (within a range)
...

If the user provides OrderNumber(s) and OrderDate(s) then this works well.
But: what if the user only provides PartNumber(s)? PartNumber will be in Items table:
CREATE TABLE Items (
ItemID INT, <- PK, cluster indexed
PartNumber VARCHAR(20), <- unique (alternate key)
...)
In this case I would want my SELECT to start with the Items table.
I would have to do a If-then-else in the SP to see if user has provided OrderNumber(s) and / or PartNumber(s) and do the selects and
joins in different orders.
Well, if I expand this to include filters like SupplierCode(s), ... then I would end up with more if-then-else to determine the
order of selects and joins so that I use the most efficient index first.

I want to minimize the number of SPs that I provide, assuming that over time this will grow larger.
I want to minimize use of views:
1. there is added overhead
2. there is added maintenance issues
3. I have to justify each view to the DBAs

It seems that I have to balance the use of select(s), views, and number of stored procedures.
Being new to SQL Server (January of 2008) I am looking to learn from the vast knowledge that you guys bring to this forum.

Thank you,
Omid

"steve dassin" <steve_nospam_(a)rac4sql.net> wrote in message news:e8FO$qK7IHA.1192(a)TK2MSFTNGP05.phx.gbl...
> Can you restate what you mean by a 'dilemma'. Do you think there should be a way to get details given an OrderNumber value (where
> OrderNumber is only in the OrderHeader table) without the need to do a join between the 2 tables? This is not possible in sql but
> I'm curious if this is your thinking.
>
> www.beyondsql.blogspot.com
>
> "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: Roy Harvey (SQL Server MVP) on 23 Jul 2008 08:39
On Wed, 23 Jul 2008 05:17:02 -0700, "Omid Golban" <ogolban(a)cox.net>
wrote:

>It seems that I have to balance the use of select(s), views, and number of stored procedures.
>Being new to SQL Server (January of 2008) I am looking to learn from the vast knowledge that you guys bring to this forum.

Of course you COULD just copy the natural key, OrderNumber, onto the
OrderDetail table. It would be a bit redundant and cost some space
but it is worth thinking about. Or not.

Also I note that OrderNumber is declared varchar(10). There are two
bytes of overhead for each varchar it takes up 2 to 12 bytes. Likewise
varchar has extra processing overhead, including when using an index
on a varchar. If the actual data is anywhere near the ten character
maximum it should probably be a fixed length CHAR(10).

Roy Harvey
Beacon Falls, CT
From: Alex Kuznetsov on 23 Jul 2008 09:30
On Jul 22, 4:12 pm, "Omid Golban" <ogol...(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

If there are performance concerns, I would start with an index on
(OrderID, OrderNumber). Having OrderNumber in the child table is not a
problem either. DId you consider getting rid of OrderID altogether?
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Manipulating complex joined query in a view
Next: Please help with Select Statement (Zip File)