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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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