What does it mean when an MS SQL table name starts with @?

Question

I work in manufacturing and no formal SQL training.

I'm looking at a stored procedure in MS SQL 2017 that has what I thought was a temporary SQL table name. But everything on this site and google says temporary tables start with #.

The one I'm looking at starts with @ sign. It seems temporary since data is not persistent and I can't find the table itself listed under any of the DB (including temp).

My other thought is that the whole table is a variable.

I want to understand the SQL environment better and my web searches may be inaccurate if I'm calling this a temp table and it's not.

`USE [Runtime]
GO
/****** Object: StoredProcedure [dbo].[p_Rpt_LP1ChangeOver_curr] Script Date: 5/17/2022 5:05:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[p_Rpt_LP1ChangeOver_curr] @StartTime datetime2, @EndTime datetime2
as

declare @MainTbl table(
[RowID] int IDENTITY(1, 1),
[Comment] nvarchar(500) null,
[RunDurationSecs] integer null,
[FromFormRunStartDTS] datetime2 null,
[COstartDTS] datetime2 null,
[CasesLeftAtCO] integer null,
[COdoneDTS] datetime2 null)

--Get all the times that CO running tag when from 0 to 1

insert into @MainTbl([COstartDTS])
Select StartDateTime
From History
Where History.TagName IN ('LP1_ChgOvrRunning.MiscDI')
AND wwRetrievalMode = 'Delta'
AND wwQualityRule = 'Extended'
AND wwVersion = 'Latest'
AND Value = 1
AND DateTime > @StartTime
AND DateTime < @EndTime

--Determine how many rows are in table. This is needed later when
--adding the calculated RbldCreated
set @MainRowCt = @@ROWCOUNT`

Answer 1

Hi，@Keesee, Kathy

Welcome to Microsoft T-SQL Q&A Forum!

Obviously this is a table variable , which uses a variable for storing rows of data , similar to the function of a temporary table .

Why does the stored procedure use table variables here ? This is because using table variables in stored procedures reduces recompilation compared to using temporary tables.

Unlike our common #table , the structure of table variables cannot be changed after declaration , and non-clustered indexes cannot be created for table variables . However , starting with SQL Server 2014 , memory-optimized table variables can be used to introduce new in-memory OLTP , allowing non-clustered indexes to be added as part of table variable declarations . I believe you will encounter this part later .

As an aside , if you want to save the result of your own stored procedure to a table variable , you can do this:

create proc mytest  
 @id  int  
 as  
 select   1   as  id, ' abc '   as  name  union   all    
 select   @id   as  id, ' bcd '   as  name  
  
 declare  @table  table (id    int ,name  varchar ( 50 ))   
 insert   into @table   exec  mytest  2   
 select   *  
 from   @table      

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It looks like a local variable of type Table. The details and samples can be found in documentation: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-ver15#c-declaring-a-variable-of-type-table. Such tables are usually stored in memory if there is enough space, and are destroyed automatically.

Answer 3

You can compare two type of temporary tables: temp table vs temp table variable. Temp table starts with one # in front of table name is local temp table and with two ## is global temp table. Table variable starts with @ sign with the declare syntax. They are all temp objects. You can access your table variable and local variable in their own sessions and global temp variable may be accessible out of its own session. They are stored in temp DB but with different internal names.