difference between with and temp table in my two query
-
what diffrence betwwen in two below table
with cteStudent(ID,Name,Family)
AS
(
select ID,Name,Family from tblStudent
)
select * from cteStudentSELECT ID,Name,Family into #tblTemp FROM tblStudent
select * from #tblTempassume that i remove temp table after run two query. which of this two query is better than another (in performance aspect) thanks for any help t
-
what diffrence betwwen in two below table
with cteStudent(ID,Name,Family)
AS
(
select ID,Name,Family from tblStudent
)
select * from cteStudentSELECT ID,Name,Family into #tblTemp FROM tblStudent
select * from #tblTempassume that i remove temp table after run two query. which of this two query is better than another (in performance aspect) thanks for any help t
mehdi.sabet wrote:
what diffrence betwwen in two below table
The first will only work on Sql2005+, since that's when the Common Table Expression was introduced.
mehdi.sabet wrote:
assume that i remove temp table after run two query.
which of this two query is better than another (in performance aspect)What makes you think there's much difference in terms of speed? :) It's not like there's "duplicate" functionality and programmers having to look for the "most efficient" version of a routine. Your hunting for speed in the wrong place. Now, to answer the question; the second version is preferred, it'd be unnoticeable faster. A good reason would be if you'd need to do multiple mutations on the same set. A good reason to use the CTE would be recursion. Good luck.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
what diffrence betwwen in two below table
with cteStudent(ID,Name,Family)
AS
(
select ID,Name,Family from tblStudent
)
select * from cteStudentSELECT ID,Name,Family into #tblTemp FROM tblStudent
select * from #tblTempassume that i remove temp table after run two query. which of this two query is better than another (in performance aspect) thanks for any help t
On SQLServer you can think of the CTE as an inline view, while the temporary table will be materialized and can be indexed.
mehdi.sabet wrote:
which of this two query is better than another (in performance aspect)
In this specific case probably the CTE, because the select is straight forward and directly streamed to output. While the temp table needs to be materialized, if even just in memory, and selected from memory to the output. If the CTE/temptable would be used in more than one place and/or the select could benefit from indexing the temptable, the result could be quite different. On Oracle it's a bit different though. There a CTE can be materialized.
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
what diffrence betwwen in two below table
with cteStudent(ID,Name,Family)
AS
(
select ID,Name,Family from tblStudent
)
select * from cteStudentSELECT ID,Name,Family into #tblTemp FROM tblStudent
select * from #tblTempassume that i remove temp table after run two query. which of this two query is better than another (in performance aspect) thanks for any help t
As Jörgen Andersson said, cte is faster cause there's no IO on disk, it's just memory. when you declare an #Temporary, you really create the table on your tempdb, that means that you have to write on disk. For a long range of information, you can say that is not that fast as use an cte. If i'm not wrong, declare an variable ( Declare @Table Table (column) ) is faster as use an CTE , and less complicate, cause just create the table on Memory. BUT, you got to remember, that this will only use memory, for a long range of information, this mean that will use a lot of ram.