MS SQL syntax questiion

Posted on 2013-01-30
Medium Priority
Last Modified: 2013-02-19
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
      ,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
      ,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.
Question by:j_heck
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 37

Assisted Solution

ValentinoV earned 668 total points
ID: 38834970
AFAIK, they should result in the same execution plan.  You can verify that you by running the query in SSMS with the "Include Execution Plan" option switched on.

However, I would put the filter in condition in the WHERE clause, not in the JOIN.  It just reads better and that's what they've invented the WHERE clause for.

Also, when specifying dates in queries it's better to use the following format: YYYYMMDD.  The format you've used depends on the server settings and thus could result in an error when running the query on a server with different settings.
LVL 18

Expert Comment

by:Jerry Miller
ID: 38835254
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.
LVL 69

Accepted Solution

Scott Pletcher earned 668 total points
ID: 38835867
I'd put it in both the WHERE and the JOIN: it won't hurt anything, and you give SQL as much info as possible to gen a plan.  Why try to guess which will help the optimizer more?!
LVL 10

Assisted Solution

deviprasadg earned 664 total points
ID: 38838228
Here we can check the performance of each query practically and use the one that has best performance.

How to check?
Paste the two queries in the same query window in SQL Server Management studio
Enable Actual Execution Plan and run the queries.

In the Execution results tab, you can find the one that has the lowest Query Cost %.

Use that query.

If both the queries share the same Query Cost% (50%,50%) then go with the one that has best readability.

Author Closing Comment

ID: 38905131
Thanks for all the information.  I have used the three suggestions with good success.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question