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]

It is still a good practice to drop the temp tables when you are finished with them especially ifyou are running an sp that has more steps. THe sooner you free the resources the better.

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

 

[sunila7]

Try this with the Table Variable...

Declare @endPeriodDate DateTime
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
select * from @tblStaff


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


Sunil

 

[forevertechie]

Although it is a fact as per mr.denny "Regular tables and Temp Tables also have a max row length of 8060 bytes.", my SQL statement (inside a stored procedure) for creating the following table:

T.TitleID int,
T.Title varchar(100),
T.Description varchar(4000),
T.ISBN varchar(20),
T.Authors varchar(100),
T.CategoryID int,
T.PublicationDate smalldatetime,
T.Comments varchar(4000),
T.Status char(1),
T.DateLastUpdate datetime,
T.IDLastUpdate tinyint,
BookCount int,
PageCount int

would FAIL(with "table exceeds max row size of 8060 bytes") if I use the table variable (@tmpTitles convention).

However if I use the temp table (by using #tempTitles convention) if would work just fine!

I'm really surprised at this outcome if all the tables (both regular, variable and temp tables) have a max. row size of 8060 bytes.

- forevertechie

 

[SQLSister]

In regular tables and temp tables you can in fact create tables with a potential record size greater than the maximum. However, if you try to enter a record longer than that, it will fail. In some ways the table variable failing on creation is a better idea.(And of course there are times when it isn't, depends onthe situation.) Table variables are indeed limited compared to temp tables and clearly from the discussion not all the limitations are nicely spelled out.



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

 

[dzman1]

I recommend always setting a Primary Key in table variables, as in:

Declare @tblStaff Table (
staffID int NOT NULL,
periodBudget money default 0,
stdBudRate money default 0,
nextPeriodBudget money default 0,
daysWorked int
PRIMARY KEY CLUSTERED (staffID))

If the linking field won't be unique, it's simple to force uniqueness.

Declare @tblStaff Table (
rowID int IDENTITY(1,1) NOT NULL,
staffID int NOT NULL,
periodBudget money default 0,
stdBudRate money default 0,
nextPeriodBudget money default 0,
daysWorked int
PRIMARY KEY CLUSTERED (staffID, rowID))


In multi-step procedures that (for example) join derived tables, you can get dramatic increases in speed (I've seen 5 minutes shrink to less than a second) by throwing the derived tables into PKeyed table variables before joining them. Derived tables don't "inherit" any indexing information from their source, so PKeying makes up for that.

This practice also tends to simplify and self-document the code which you (or your successor) will appreciate if the code has to be adjusted (or scavenged from) later.

DZMan1

 

[donutman]

DZMan1, that's good to know.
-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]