3 edited body
source | link

This is pretty broad, but I'll give you as general andan answer as I can.

CTEs...

  • Are unindexable (but can use existing indexes on referenced objects)
  • Cannot have constraints
  • Are essentially disposable VIEWs
  • Persist only until the next query is run
  • Can be recursive
  • Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

  • Are real materialized tables that exist in tempdb
  • Can be indexed
  • Can have constraints
  • Persist for the life of the current CONNECTION
  • Can be referenced by other queries or subprocedures
  • Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is dispoabledisposable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.

This is pretty broad, but I'll give you as general and answer as I can.

CTEs...

  • Are unindexable (but can use existing indexes on referenced objects)
  • Cannot have constraints
  • Are essentially disposable VIEWs
  • Persist only until the next query is run
  • Can be recursive
  • Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

  • Are real materialized tables that exist in tempdb
  • Can be indexed
  • Can have constraints
  • Persist for the life of the current CONNECTION
  • Can be referenced by other queries or subprocedures
  • Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is dispoable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.

This is pretty broad, but I'll give you as general an answer as I can.

CTEs...

  • Are unindexable (but can use existing indexes on referenced objects)
  • Cannot have constraints
  • Are essentially disposable VIEWs
  • Persist only until the next query is run
  • Can be recursive
  • Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

  • Are real materialized tables that exist in tempdb
  • Can be indexed
  • Can have constraints
  • Persist for the life of the current CONNECTION
  • Can be referenced by other queries or subprocedures
  • Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.

2 added 121 characters in body
source | link

This is pretty broad, but I'll give you as general and answer as I can.

CTEs...

  • Are unindexable (but can use existing indexes on referenced objects)
  • Cannot have constraints
  • Are essentially disposable VIEWs
  • Persist only until the next query is run
  • Can be recursive
  • Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

  • Are real materialized tables that exist in tempdb
  • Can be indexed
  • Can have constraints
  • Persist for the life of the current CONNECTION
  • Can be referenced by other queries or subprocedures
  • Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is dispoable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.

This is pretty broad, but I'll give you as general and answer as I can.

CTEs...

  • Are unindexable (but can use existing indexes on referenced objects)
  • Cannot have constraints
  • Are essentially disposable VIEWs
  • Persist only until the next query is run
  • Can be recursive

#Temp Tables...

  • Are real materialized tables that exist in tempdb
  • Can be indexed
  • Can have constraints
  • Persist for the life of the current CONNECTION
  • Can be referenced by other queries or subprocedures

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is dispoable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.

This is pretty broad, but I'll give you as general and answer as I can.

CTEs...

  • Are unindexable (but can use existing indexes on referenced objects)
  • Cannot have constraints
  • Are essentially disposable VIEWs
  • Persist only until the next query is run
  • Can be recursive
  • Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

  • Are real materialized tables that exist in tempdb
  • Can be indexed
  • Can have constraints
  • Persist for the life of the current CONNECTION
  • Can be referenced by other queries or subprocedures
  • Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is dispoable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.

1
source | link

This is pretty broad, but I'll give you as general and answer as I can.

CTEs...

  • Are unindexable (but can use existing indexes on referenced objects)
  • Cannot have constraints
  • Are essentially disposable VIEWs
  • Persist only until the next query is run
  • Can be recursive

#Temp Tables...

  • Are real materialized tables that exist in tempdb
  • Can be indexed
  • Can have constraints
  • Persist for the life of the current CONNECTION
  • Can be referenced by other queries or subprocedures

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is dispoable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.