SQL Server Resource Governor – Difference Between Resource Pools and Workload Groups

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.

Workload groups are a concept of the resource governor feature in SQL server. You would define workload groups (such as a prod group and a test group) on a server and then define the amount of resources that can be used by each.

So for instance if you only had one SQL server for test and prod say, you could define the 2 workload groups and limit the CPU/Memory that can be used by each, to limit the impact of the test workload on the prod workload (SQL 2014 can also limit the IOPs used by each group too).

You should read up more on the resource governor here:

Resource Governor