j_heck
asked on
MS SQL syntax questiion
I am curious what kind of response I would get on this and if anybody could tell me that one way is better than the other or are they the same. The difference between the two snippets of code is the JOINS in the first code and the WHERE clause in the second code. I am using si.Corporate = 1 on the top code in the JOIN and on the bottom code it is placed in the WHERE clause. Any thoughts on this? One better than the other are they both equal? etc.
***** First Code JOINS *****
Select td.StoreId
,fm.EndDate
,COUNT(1) as CertRecvd
,sum(soldprice) as Sales
from TLogDtl td with (nolock)
Join vFiscalMonth fm with (nolock) on td.TransDate between fm.BgnDate and fm.EndDate
Join PSPCorp.dbo.StoreInfo si with (nolock) on td.StoreId = si.StoreId and si.Corporate = 1
Join TLogHdr th with (nolock) on td.TLogHdrId = th.TLogHdrId and th.custNbr <> 0
where td.TransDate between '1/1/2012' and '12/29/2012'
group by td.StoreId, fm.EndDate
order by td.StoreId, fm.EndDate
***** Second Code WHERE clause *****
Select td.StoreId
,fm.EndDate
,COUNT(1) as CertRecvd
,sum(soldprice) as Sales
from TLogDtl td with (nolock)
Join vFiscalMonth fm with (nolock) on td.TransDate between fm.BgnDate and fm.EndDate
Join PSPCorp.dbo.StoreInfo si with (nolock) on td.StoreId = si.StoreId
Join TLogHdr th with (nolock) on td.TLogHdrId = th.TLogHdrId
where td.TransDate between '1/1/2012' and '12/29/2012'
and si.Corporate = 1
and th.CustNbr <> 0
group by td.StoreId, fm.EndDate
order by td.StoreId, fm.EndDate
Thanks for your thoughts on this.
***** First Code JOINS *****
Select td.StoreId
,fm.EndDate
,COUNT(1) as CertRecvd
,sum(soldprice) as Sales
from TLogDtl td with (nolock)
Join vFiscalMonth fm with (nolock) on td.TransDate between fm.BgnDate and fm.EndDate
Join PSPCorp.dbo.StoreInfo si with (nolock) on td.StoreId = si.StoreId and si.Corporate = 1
Join TLogHdr th with (nolock) on td.TLogHdrId = th.TLogHdrId and th.custNbr <> 0
where td.TransDate between '1/1/2012' and '12/29/2012'
group by td.StoreId, fm.EndDate
order by td.StoreId, fm.EndDate
***** Second Code WHERE clause *****
Select td.StoreId
,fm.EndDate
,COUNT(1) as CertRecvd
,sum(soldprice) as Sales
from TLogDtl td with (nolock)
Join vFiscalMonth fm with (nolock) on td.TransDate between fm.BgnDate and fm.EndDate
Join PSPCorp.dbo.StoreInfo si with (nolock) on td.StoreId = si.StoreId
Join TLogHdr th with (nolock) on td.TLogHdrId = th.TLogHdrId
where td.TransDate between '1/1/2012' and '12/29/2012'
and si.Corporate = 1
and th.CustNbr <> 0
group by td.StoreId, fm.EndDate
order by td.StoreId, fm.EndDate
Thanks for your thoughts on this.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jerry Miller
I agree with ValentinoV. They return the same (or very similar) execution plans depending upon your database setup. But for ease in reading, use the second option.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the information. I have used the three suggestions with good success.