WHERE in based on multiple columns I am trying to achieve something like this in sql server 2012. But this syntax does not exists. What are the alternatives to it and why does sql server does not support such syntactic sugar?

Which means C1 match to D1, C2 match to D2, Cn match to Dn. When all of the columns match it only will filter out the result.

SELECT * FROM tableA
WHERE
   ([C1], [C2],..., [Cn]) IN (SELECT [D1], [D2],..., [Dn] FROM tableB)
Your query is standard SQL. Unfortunately it hasn't been implemented yet in SQL Server. You can express your support for this feature to be implemented: Microsoft Azure Feedback: Add support for ANSI standard row value constructors.

You can use the common method of transforming IN to an EXISTS subquery. Works with just one or more columns:

SELECT * 
FROM tableA AS a
WHERE EXISTS
      ( SELECT 1 
        FROM tableB AS b
        WHERE a.[C1] = b.[D1]
          AND a.[C2] = b.[D2]
          ---
          AND a.[Cn] = b.[Dn]
      ) ;