Why # (hash) in the Dapper sample

Question

I just read this sample from the Dapper manual:

connection.Execute(@" set nocount on create table #t(i int) set nocount off insert #t select @aa union all select @b set nocount on drop table #t", new {a=1, b=2 }) .IsEqualTo(2); 

Are #t special syntax for something? Or are they just confusing me? :)

Answer 1

Yes, # means something important in TSQL - a table named foo is permenant, for this db / schema. A table named #foo is a temporary table - it exists only for this connection and is deleted when the connection is closed or reset. A table named ##foo is a global temporary table and exists everywhere, but is intended for temporary use. It is mainly used when bulk data.

Using #t here is that the table exists only in this join, so we can repeat the test trivially.

In addition, a table named @foo is either a table variable or a table-value parameter, and exists only for this / sproc command.