GilaMonster (10/29/2007)
--------------------------------------------------------------------------------
As for CTEs and index/table scans. As I said, CTEs aren't tables. They're temporary views. As such, they them selves cannot be the subject of index or table scans. The tables referenced by the CTE are the ones that are read (either by scan or seek)
Thanks a Lot Gail, I cleared my concepts.
Grant (10/29/2007)
--------------------------------------------------------------------------------
But again, when the time comes to query the data out of a table variable or temp table, there are very distinct differences in behavior that, depending on the amount of data, makes temp tables perform better than table variables.
Thanks Grant,
Today morning I just tried with this SQL script
I got more idea,
1st
====
Begin
Declare @abc Table
(
a int
)
insert into @abc
select a from t1 -- this is going for index scan for t1 Table
select * from @abc -- this is going for table scan from @abc Table Variable.
end
------
2nd
====
with abc
as
( select a from t1 )
select * from abc -- it is only going for Index scan for t1 Table
not for abc , which is not a table means inline view.
Thanks to both of you and others those clear my concepts..
Have a Nice Day...!!!
Cheers!
Sandy.:)
--