Geralmente, é mais rápido selecionar em uma tabela temporária do que selecionar em uma tabela real?

escrever no tempdb é mais rápido do que uma tabela real que não está no tempdb

É verdade. Há dois aprimoramentos de E/S no TempDb.

Gravações em uma tabela em um banco de dados de usuário devem ter seus registros de log liberados no disco na confirmação ou, se uma inserção minimamente registrada (como SELECT ... INTO), deve ter as páginas do banco de dados liberadas no disco na confirmação. A maneira como o log mínimo funciona em um banco de dados do usuário é que as páginas do banco de dados são gravadas diretamente no disco. Quando o SELECT ... INTO for concluído, as novas páginas foram todas gravadas nos arquivos físicos.

As gravações de TempDb não precisam ser liberadas no disco na confirmação, pois o TempDb nunca é recuperado após uma falha. Então eles simplesmente não são. Suas alterações geram registros de log, mas o buffer de log é liberado para o disco somente quando está cheio, não para cada confirmação.

E desde o SQL Server 2014 , as inserções minimamente registradas no TempDb nem sempre são gravadas em disco. Se você carregar uma tabela temporária pequena e de curta duração, ela pode nunca ser gravada no disco. O log terá alguns registros sobre as alocações de página e entradas de metadados para a tabela, mas é isso.

EG execute o seguinte lote em tempdb, um banco de dados de recuperação completo e um banco de dados de recuperação simples para ver as diferenças.

drop table if exists foo
go
declare @data bigint
declare @log bigint 
select @log = sum(case when type_desc = 'LOG' then num_of_bytes_written end) 
      ,@data =  sum(case when type_desc = 'ROWS' then num_of_bytes_written end) 
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

select * 
into foo
from sys.objects 

select -@log + sum(case when type_desc = 'LOG' then num_of_bytes_written end)  log_bytes
      ,-@data +  sum(case when type_desc = 'ROWS' then num_of_bytes_written end) data_bytes
      , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

e você verá algo como:

Para recuperação simples:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
24576                16384                SIMPLE

para recuperação total:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
36864                0                    FULL

e para tempdb:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
0                    0                    SIMPLE

Às vezes, para tempdb, você verá o buffer de log liberado:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
61440                0                    SIMPLE

Além de gravar em tempdb, muitas vezes, nem todos os discos / redes de E/S atingidos, conforme estendido na resposta de David Browne , dependendo da sua configuração de E/S, você pode descobrir que, mesmo quando os dados são grandes o suficiente para serem enviados para o disco, ainda é mais rápido do que selecionar em uma tabela "normal":

• O TempDB pode estar em unidades diferentes, portanto, tenha sua própria largura de banda de E/S. Isso é especialmente significativo com unidades giratórias em vez de SSDs. Ler e gravar no mesmo banco de dados (ou outro banco de dados nas mesmas unidades) envolverá mais movimentos de cabeça que adicionam mais latência de E/S e potencialmente limitam sua largura de banda de E/S efetiva. Copiar dados entre bancos de dados em diferentes unidades/matrizes não terá a mesma latência extra.

• O TempDB pode até estar em uma mídia mais rápida que seu armazenamento principal. Talvez em unidades locais onde o armazenamento principal esteja na rede ou SSDs NVMe onde o armazenamento principal esteja em unidades tradicionais.

Essas duas diferenças também podem ser vistas dentro do mesmo banco de dados se você usar vários grupos de arquivos para distribuir partes dos dados entre diferentes unidades/matrizes.

O oposto também pode ser verdadeiro se você tiver vários bancos de dados que estão ativamente em uso. Como o TempDB é um recurso compartilhado, e as unidades/rede que o hospedam, podem estar sob mais carga do que os arquivos de dados para qualquer banco de dados individual.