6 added 131 characters in body
source | link

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

You might also want to consider using a table variable. This is used as a temp table is used and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy structure, as it doesn't need to be dropped after execution. Mostly just syntactic sugar. However, if you keep the row-count low, it never materializes to disk. See What's the difference between a temp table and table variable in SQL Server? for more details.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

You might also want to consider using a table variable. This is used as a temp table is used and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy structure, as it doesn't need to be dropped after execution. Mostly just syntactic sugar.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

You might also want to consider using a table variable. This is used as a temp table is used and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy structure, as it doesn't need to be dropped after execution. Mostly just syntactic sugar. However, if you keep the row-count low, it never materializes to disk. See What's the difference between a temp table and table variable in SQL Server? for more details.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

5 added 42 characters in body
source | link

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

You might also want to consider using a table variable. This is used as a temp table is used, is also in-memory only, and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy in-memory structure, as it doesn't need to be dropped after execution. Mostly just syntactic sugar.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

You might also want to consider using a table variable. This is used as a temp table is used, is also in-memory only, and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy in-memory structure.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

You might also want to consider using a table variable. This is used as a temp table is used and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy structure, as it doesn't need to be dropped after execution. Mostly just syntactic sugar.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

4 Comment from Paul White, so I revisited.
source | link

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure, but bear in mind that it will need to be recreated every time it is needed.

You might also want to consider using a table variable. This is used as a temp table is used, but is also in-memory only, butand can be used multiple times without needing to be recreated every timere-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this iscan be a handy in-memory structure.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure, but bear in mind that it will need to be recreated every time it is needed.

You might also want to consider using a table variable. This is used as a temp table is used, but is also in-memory only, but can be used multiple times without needing to be recreated every time. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this is a handy in-memory structure.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small.

EDIT:

Please see Martin's comments below:

The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 '12 at 17:08


Original answer

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

You might also want to consider using a table variable. This is used as a temp table is used, is also in-memory only, and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy in-memory structure.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.


Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

3 added 11 characters in body
source | link
2 added 1385 characters in body
source | link
1
source | link