Offset, limit, multiply columns comparision in SQL Server query

Offset, Limit
Problem
The query retrieves multiply rows ordered, but you only want to select some rows from specified position.
SQL server only supports "select TOP ...."

Solution
Use ROW_NUMBER function. Consider following example:

select ... from (select ROW_NUMBER() OVER (ORDER BY <column_name>) as rowIndex, ...) as q1

where q1.rowIndex >= <offset_position> and rowIndex <= <offset_posistion> + <limit_count>

Multiply columns comparison
Problem

select *
from table1WHERE  (CM_PLAN_ID,Individual_ID)
IN
(
 Select CM_PLAN_ID, Individual_ID
 From CRM_VCM_CURRENT_LEAD_STATUS
 Where Lead_Key = :_Lead_Key)

but the WHERE..IN clause allows only 1 column. How to compare 2 or more columns with another inner select?

Solution
1. Convert to string format and concatenate them together, then compare. Consider following example:

select *
from table1WHERE  CM_PLAN_ID + '|' + Individual_ID = 
(
 Select CM_PLAN_ID + '|' + Individual_ID
 From CRM_VCM_CURRENT_LEAD_STATUS
 Where Lead_Key = :_Lead_Key)

2. Use JOIN

select * from table1 JOIN
(
 Select CM_PLAN_ID, Individual_ID
 From CRM_VCM_CURRENT_LEAD_STATUS
 Where Lead_Key = :_Lead_Key) as tbl2 
ON tabl1.CM_PLAN_ID and tbl2.Individual_ID = tbl2.Individual_ID