T.Ashish (5/17/2013)
So should I say that it is the old way of INNER JOINing tables, that will not be supported in future versions !!
No, that's incorrect. The older (non ANSI) version of outer joins using =* and *= is deprecated. You should be able "INNER JOIN" in where clause...
Also, when we write "x IN (2,4,6)", optimizer converts it into "x=2 OR x=4 OR x=6", we can see it in execution plan. Does Sql store this optimized query somewhere?
Yes it does. In a query plan cache. You can access it using sys.dm_exec_text_query_plan