Linq to Sql - Sql Generation

Question

Recently a colleague of mine stated that the SQL generated from LINQ vary depending on the table statistics.

I know that the SQL generated gets optimized depending on the SQL Server versions (Sql 2000 vs SQl 2008) leveraging new operators like ROW_NUMBER if supported.

Could anyone confirm this or how to validate this?

Answer 1

Actually, Linq to SQL will call a stored proc in which you can use ROW_NUMBER but only for certain operations like Skip and Take.

Here's a post from the master:

http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

and

http://weblogs.asp.net/scottgu/archive/2006/01/01/434314.aspx

Answer 2

"Recently a colleague of mine stated that the SQL generated from LINQ vary depending on the table statistics." - That's not true. It would require round trips to the DB to create the SQL.

Query plans are constructed by SQL Server using statistics, after the query is submitted to SQL Server.

SQL emitted by LINQ is based on rules.

Answer 3

I doubt that. The query plan generated by SQL Server would vary, but I really doubt the SQL syntax is.