DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to optimize a large IN list?

The following Microsoft SQL Server T-SQL script maybe very inefficient if the IN (T-SQL IN operator) list and the table are large:

-- SQL IN statement - sql in operator

SELECT *

FROM   TableX

WHERE  TableXID IN (1523,48325,321643,234182, 426347....)

The MSSQL IN statement can be optimized by storing the list values in a table variable or in a temporary table and using JOIN instead of the IN operation. Regular (permament) table storage can also be applied.

-- T-SQL table variable

DECLARE  @KeySet  TABLE(

                        PrimaryKey INT

                        )

 

INSERT @KeySet VALUES(1523)

 

INSERT @KeySet VALUES(48325)

 

INSERT @KeySet VALUES(321643)

 

INSERT @KeySet VALUES(426347)

 

SELECT *

FROM   TableX x

       INNER JOIN @KeySet k

         ON x.TableXID = k.PrimaryKey

GO

 

-- T-SQL temporary table

CREATE TABLE  #KeySet  (

                        PrimaryKey INT

                        )

 

INSERT #KeySet VALUES(1523)

 

INSERT #KeySet VALUES(48325)

 

INSERT #KeySet VALUES(321643)

 

INSERT #KeySet VALUES(426347)

 

SELECT *

FROM   TableX x

       INNER JOIN #KeySet k

         ON x.TableXID = k.PrimaryKey

GO



Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE