CODEWITHSUNDEEP
t-sql
TSQL_Noob
TSQL_Noob

Reputation: 187

T-SQL temp data comparison # vs WITH

I have been working with basic SQL for a couple years already. However, only recently I encounter the "WITH" command. From my research, it looks like "WITH" is a method to create a temporary table. But in my experience, you can also use the hash "#" to create a temp table.

I tried to search "WITH" and "#", but since these are weird search words, I am not getting much result.

Could someone chime me in on what is the difference such as pros and cons for each?

Upvotes: 5

Views: 1033

Answers (2)

Rodolfo
Rodolfo

Reputation: 4183

WITH is a table created on-the-fly (in memory), so it's potentially much faster than a temporary (#) table that is like any other table, but it's automatically dropped when you close the connection. The con is that it's only alive for one statement

Upvotes: 0

user596075
user596075

Reputation:

You use WITH to create a Common Table Expression (CTE). It is not technically a temp table.

A temp table is like any other table, but it is stored in tempdb and dropped when the connection is closed.

Unlike a temp table, though, a CTE doesn't actually have a physical store to it. It's more like a "view".

Here is a great article on exactly what CTEs are. And unlike temp tables, that persist for the connection, a CTE is:

defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement

MSDN Reference on Using Common Table Expressions

Upvotes: 4

Related Questions