Difference between table lazy spool and table eager spool

 Below are the difference between Table lazy spool and table eager spool

Sno	Aspect	Lazy Spool	Eager spool
1	When row are spooled	Rows are spooled when they are required by the parent operator. Spooling occurs lazily row by row as the query progress	Rows are spooled immediately and eagerly as soon as they are produce by the child operator regardless of where all rows are required or not.
2	Behaviour	Lazy spool is differed processing row on demand. This is efficient when all row are not required.	Eager spool process all the rows up front and store it into the tempdb
3	Common use case	Recursive CTE, Correlated sub query, Complex sub query	Joins that need to repeated access the same data in the nested loop
4	Performance impact	More efficient when not all rows are accessed. If all rows are eventually accessed lazy spool may be slower	It will be faster when query required all row. This is processed at one go. It will use more memory in case of large dataset
5	Resource uses	Consume less resource because it pull the row when it required.	Consume more resource
6	Tempdb	It use tempdb lesser the Eager spool because data rows are stored as incremental load.	It use tempdb higher than the Lazy spool because it process all the records up front and store it in the tempdb