[dba-SQLServer] Joining temp tables in SPROC

Variable tables are generally tables in memory and are meant to be light so
that they can survive in memory.  A major advantage is you can convert comma
delimited lists to variable tables (generally a small-ish table) and have
all the benefits of a normal table but in RAM.  Temp tables if small will
remain in memory, but if the server is busy with other major tasks that
demand memory, your temp table will be written to disk.  In general a
placeholder is always created, but your data may just be in RAM vs on disk,
my recommendation for people using variable tables or temp tables is that if
you're going to be hauling a lot of data (or potentially a lot of data) then
you should architect your solution with temp tables, otherwise for smaller
datasets a variable table is a great performance option.


-Francisco
http://bit.ly/sqlthis   | Tsql and More...
<http://db.tt/JeXURAx>




On Wed, Aug 31, 2011 at 8:36 AM, Mark Breen <marklbreen at gmail.com> wrote:

> Hi David,
>
> what is the difference between Temp table and using a variable tables.  I
> never used such an exotic thing ?
>
> thanks
>
> Mark
>
>
> On 30 August 2011 20:42, newsgrps <newsgrps at dalyn.co.nz> wrote:
>
> > Thanks Francisco.  That worked and makes me more comfortable.
> >
> > David
> >
> >
> > At 31/08/2011, Francisco Tapia wrote:
> >
> >> i'm not sure, but i've always used an alias when I join my temp tables
> >> including variable tables, so my change would read
> >>
> >> FROM @ttmpWeeks AS tWeeks
> >>
> >> then your ON statment can read tWeeks.WeekNo = join clause
> >>
> >> -Francisco
> >> http://bit.ly/sqlthis   | Tsql and More...
> >> <http://db.tt/JeXURAx>
> >>
> >>
> >>
> >>
> >> On Tue, Aug 30, 2011 at 1:41 AM, newsgrps <newsgrps at dalyn.co.nz> wrote:
> >>
> >> > Group,
> >> >
> >> > Can someone please explain why this is
> >> >
> >> > I get an error "Must declare the variable ttmpWeeks" when I try to
> save
> >> the
> >> > following in a stored procedure:
> >> >
> >> > ALTER PROCEDURE sprptProductionSummary
> >> >        (
> >> >                @txtDate1 varchar(20),
> >> >                @txtDate2 varchar(20)
> >> >        )
> >> > AS
> >> >        SET NOCOUNT ON
> >> >
> >> >        SET DATEFIRST 1 -- Monday
> >> >
> >> >        DECLARE @ttmpWeeks table (WeekNo tinyint)
> >> >
> >> >        DECLARE @tmpDate as datetime
> >> >
> >> >        SET @tmpDate = @txtDate1
> >> >
> >> >        INSERT INTO @ttmpWeeks (WeekNo)
> >> >        VALUES (DATEPART(week, @tmpDate))
> >> >
> >> >        SET @tmpDate = DATEADD(week, 1, @tmpDate)
> >> >
> >> >        WHILE @tmpdate < @txtDate2
> >> >                BEGIN
> >> >                        INSERT INTO @ttmpWeeks (WeekNo)
> >> >                        VALUES (DATEPART(week, @tmpDate))
> >> >
> >> >                        SET @tmpDate = DATEADD(week, 1, @tmpDate)
> >> >                END
> >> >
> >> >        SELECT WeekNo, ISNULL(spKnit.KnittedWeight,0) AS KnittedWeight
> >> >        FROM @ttmpWeeks LEFT OUTER JOIN (SELECT DATEPART(week,
> >> > dbo.tblRoll.KnitDate) AS WeekNum, ISNULL(SUM(KnitWeight),0) AS
> >> KnittedWeight
> >> >                                                FROM dbo.tblRoll
> >> >                                                WHERE KnitDate BETWEEN
> >> > @txtDate1 AND @txtDate2
> >> >                                                GROUP BY DATEPART(week,
> >> > dbo.tblRoll.KnitDate)) AS spKnit ON @ttmpWeeks.WeekNo = spKnit.WeekNum
> >> >
> >> > {rest of code continues}
> >> >
> >> > However, if I change the last line to
> >> > GROUP BY DATEPART(week, dbo.tblRoll.KnitDate)) AS spKnit ON WeekNo =
> >> > spKnit.WeekNum
> >> > it saves and works.
> >> >
> >> > Why doesn't it accept the table part of the WeekNo field?
> >> >
> >> >
> >> > Regards
> >> >
> >> > David Emerson
> >> > Dalyn Software Ltd
> >> > Wellington, New Zealand ______________________________**
> >> **_________________
> >>
> >
> > ______________________________**_________________
> > dba-SQLServer mailing list
> > dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com
> >
> > http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
> > http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>