Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

any way this can avoid of index scan? Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 7:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
GilaMonster (7/17/2013)
ChrisM@Work (7/17/2013)
Really? What type of join is shown for Query 1 and Query 2?


NOT IN and NOT EXISTS do perform identically with identical execution plans if the columns are defined as NOT NULL.
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/


<headdesk> Thanks Gail.
DROP TABLE #A
CREATE TABLE #A (col1 INT NOT NULL)
INSERT INTO #A (col1)
SELECT TOP 10000 col1 = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM syscolumns a, syscolumns b

CREATE UNIQUE CLUSTERED INDEX ucx_col1 ON #A (col1)


DROP TABLE #B
CREATE TABLE #B (col1 INT NOT NULL)
INSERT INTO #B (col1)
SELECT TOP 300 col1 = CAST(col1 AS INT)
FROM #A

INSERT INTO #B (col1) SELECT TOP 10000 col1 = 10000+ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

CREATE UNIQUE CLUSTERED INDEX ucx_col1 ON #B (col1)


-- range scan
SELECT a.col1
FROM #A a
WHERE a.col1 NOT IN (SELECT b.col1 FROM #B b)

-- range scan
SELECT a.col1
FROM #A a
WHERE NOT EXISTS (SELECT 1 FROM #B b WHERE b.col1 = a.col1)





“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1474632
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse