sql: question regarding query optimization

I have the following two tables:

CREATE TABLE Test_Main
    (
      [ID] INT IDENTITY ,
      [TypeID] INT ,
      [BookID] INT
    )

CREATE TABLE Test_Second
    (
      [TypeID] INT ,
      [BookID] INT ,
      [Value] INT,

    )

INSERT  INTO Test_Main(TypeID, BookID)
   SELECT 1, 10
   UNION
   SELECT 2, 31
   UNION
   SELECT 3, 51
   UNION
   SELECT 4, 81

INSERT INTO Test_Second(TypeID, BookID, Value)
   SELECT  1, 0, 30
   UNION
   SELECT  2, 31, 45
   UNION
   SELECT  3, 51, 66
   UNION
   SELECT  4, 0, 22 

and I have the following query :

SELECT  
    ID ,
    Test_Main.TypeID ,
    Test_Main.BookID
FROM
    Test_Main
INNER JOIN 
    Test_Second ON Test_Main.[TypeID] = Test_Second.[TypeID]
WHERE   
    Test_Main.BookID = CASE WHEN (Test_Main.BookID = 2 OR Test_Main.BookID = 3) 
                                THEN Test_Second.BookID
                            ELSE Test_Main.BookID
                       END 

This query is giving the following output:

ID          TypeID      BookID
----------- ----------- -----------
1           1           10
2           2           31
3        开发者_运维技巧   3           51
4           4           81

Now when my table has million rows I suspect my case clause will cause the performance issue. How can I optimize the query to use index seek. What should be the index for the above query?

You need to have the appropriate indices in place:

• The column TypeID should be indexed on both tables (since it's used in the JOIN condition)
• The column Test_Main.BookID needs to be indexed since it's used in the WHERE clause

With those three indices in place, I believe your query should work quite nicely, even with lots of rows in the tables!

(Question: Are you sure you don't mean TypeID instead of BookID in this part?):

... WHEN (Test_Main.TypeID = 2 OR Test_Main.TypeID = 3)

Your query is equivalent to:

SELECT  
    ID ,
    Test_Main.TypeID ,
    Test_Main.BookID
FROM
    Test_Main
INNER JOIN 
    Test_Second ON Test_Main.[TypeID] = Test_Second.[TypeID]
WHERE   
    ( Test_Main.BookID IN (2,3) 
      AND Test_Main.BookID = Test_Second.BookID
    )
  OR Test_Main.BookID NOT IN (2,3)

Besides indexes, you can check the execution plans of this version too (just in case, I'm not sure how well CASE clauses can be optimized).