Difference between @Table and #Table in a Stored Proc 5

[checkai]

Are there any advantages of using the variable table within a stored procedure? currently I use all #Tables, and i didn't know if that is good or not?

thanks, dlc

 

[SQLSister]

Books online comes down pretty firmly in favor of table variables.

To my mind the fact that you cannot insert the results of a stored proceudre into a table variable is a big problem and regular temp tables are the only way I know of to get around this. In most other uses, I would choose a table varaible over a temp table.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[mrdenny]

I agree. I'd also prefer to stick with Table Variables.

One other advantage that I've noticed with table variables over temp tables is that the temp tables are written to the TempDB database. Table variables are handled in RAM. Thus speeding up access time within the table since you don't have to wait for the disks. And for the forseeable (man I spelt that badly) future RAM will be faster than storage.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[checkai]

Thanks for that! Can you paste an easy sample of creating a table with variables? Thanks.

 

[mrdenny]

Like this.

Declare @TableVar table (Col1 varchar(10), Col2 int)

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[checkai]

What about Updates on the table like this one....

update @tblStaff
set	stdBudRate = isnull((select top 1 isnull(sr.rate1,0)
from	tblStaffChargeRate sr
where	sr.staffid = @tblStaff.staffid
		and sr.date <= @endPeriodDate 
order by date desc),0)

I get this message

Server: Msg 137, Level 15, State 1, Line 159
Must declare the variable '@tblStaff'.

 

[checkai]

anyone?

 

[SQLSister]

So did you declare it first? Would need to see the rest of the code to tell what is going on.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[checkai]

Declare @tblStaff Table (
	staffID int,
	periodBudget money default 0,
	stdBudRate money default 0,
	nextPeriodBudget money default 0,
	daysWorked int)
Insert Into @tblStaff (staffID)
Select 	Distinct staffid
From	#WIP

update @tblStaff
set	stdBudRate = isnull((select top 1 isnull(sr.rate1,0)
from	tblStaffChargeRate sr
where	sr.staffid = @tblStaff.staffid
		and sr.date <= @endPeriodDate 
order by date desc),0)

 

[dbomrrsm]

I think because you are using @tblStaff in the

select top 1 isnull(sr.rate1,0)
from    tblStaffChargeRate sr
where    sr.staffid = @tblStaff.staffid
        and sr.date <= @endPeriodDate 
order by date desc

it is out of context of the rest of the query so it doesnt know what @tblStaff is in that context.

Maybe completely wrong but just my two peneth.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[checkai]

yeah, see this worked when using #tblStaff...

 

[dbomrrsm]

yeah - cos #tblStaff is a table until the connection is terminated so the inside select can see it !

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[checkai]

so would this case be a limitation of using @Tables?

 

[dbomrrsm]

certainly looks like it !

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[pms18066]

I fyou execute all of the statements in one exec it will work fine. The declared table loses scope once the query completes execution.

If this was a stored proc and would execute to completion you would be fine with using the table variable as defined above....

I have used many table vars and they work fine, better then temp tables in most cases, however they act just like any other variable.

Once your dataset beomes too large than indexing may become an issue which is when you need to look for another solution.

 

[donutman]

Update @tblStaff
   Set stdBudRate=isnull(SR.Rate1,0)
   From
      (Select max([Date]) MaxDate, StaffID From tblStaffChargeRate
          Where [Date]<=@EndPeriodDate Group By StaffID) DT1 
   Inner Join tblStaffChargeRate SR 
      on SR.StaffID=DT1.StaffID and SR.[Date]=DT1.MaxDate
   Inner Join @tblStaff on SR.StaffID=@tblStaff.StaffID

See if this works. You'll have to consider what should be done if there are more than one rows with MaxDate, that problem exists with either approach.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[forevertechie]

Also, the major limitation of the table variable is that the max row size can be 8060 bytes... so obviously, if your query exceeds this limit, SQL Server will error out at the time of creation of the table variable (when you try to create the stored procedure).

-forevertechie

 

[mrdenny]

Regular tables and Temp Tables also have a max row length of 8060 bytes.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[checkai]

when using #Tables is it Best practice to drop them when done using them? I'm assuming yes...but, i'm not sure how they get cleaned up...

 

[dbomrrsm]

as far as I am aware you dont need to explicitly drop temp tables:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


All other local temporary tables are dropped automatically at the end of the current session.


Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

I understand that local temp tables can only be seen by the session that created them and global temp tables can be seen by any session until it is dropped by the ending of the session that created it OR the last session to be using it ends.

This means that a session could create it - another starts using it - the creator ends their session - another session can still see the table as it stays in existence because of the second session using it.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]