Difference between Work Tables and TempTables

Question

Post reply

Difference between Work Tables and TempTables

karthik M

One Orange Chip

Points: 29139
More actions
November 5, 2007 at 6:48 am

#62188

Hi Experts,

Explain me the difference between Worktable and temptable ? I need dame good technical answers for this question.

Inputs are Welcome !

karthik

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Carl Federl

One Orange Chip

Points: 25384

November 5, 2007 at 2:58 pm

#748433

A Worktable is a temporary table used internally by SQL Server, typically for sorting or for cursor results.

A TempTable is a temporary table defined by a user.

SQL = Scarcely Qualifies as a Language

David McFarland SSChampion Points: 11815 More actions · Answer 2

Keep in mind that while Carl's answers are correct, both terms are used in ambiguous manners quite frequently in the SQL world, especially "work table".

I've seen people refer to tables that are created and dropped inside of a stored proc, for example, as work tables. What they're doing is using them as quasi-temp-tables (but by commenting out the drop statement they live outside the scope of the session for debugging, for instance), and I've even seen them to referred to as temp tables, as well.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

I agree with David although Carl is spot on technically... lot's of folks think of a "work table" as a sort of permanent temp table where no damage will be done if the contents of the table are truncated. In our standards at work, we even require that such tables be prefixed with "wt" to identify their expendible nature.

Why use such a thing? Sometimes you need a scratchpad like a Temp table, but on extremely large batch runs, such a table would quickly overwhelm TempDB if a temp table were used. Solution... make the batch job so only one instance can execute at a time (we use a token log for that) and make a "work table".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Also, to help you with the "dame good technical answers" for the "dame" good interview, test, or homework question ;), you really should turn to Books Online. A simple lookup of "worktable" will give you a "dame" good technical answer 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

karthik M One Orange Chip Points: 29139 More actions · Answer 5

I agreed all your experts answer. But in memory level where worktable will be stored ?

For example if i create # table then it will be stored in TempDatabase.

But if use groupby,orderby etc., then it will create worktable. I agreed it is an internal process which is handled by sqlserver. My question is where exactly worktables will be stored ?

karthik

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Using David and Jeff's definition - a work table is a table used for temporary purposes. You could create it anywhere: in the local database, in tempDB, some other database. Temp tables are in tempdb under that definition. (I also tend to use this verbiage, albeit technically wrong).

Using the "correct" technical definition - work tables are created in tempDB, temp tables, anywhere.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

karthikeyan (11/6/2007)

I agreed all your experts answer. But in memory level where worktable will be stored ?

For example if i create # table then it will be stored in TempDatabase.

But if use groupby,orderby etc., then it will create worktable. I agreed it is an internal process which is handled by sqlserver. My question is where exactly worktables will be stored ?

Work Tables that are spawned by the system are as if they were TempTables... that is, if they fit in memory, they will live in memory as Temp Tables do (under the guise of TempDB)... if they don't fit in memory, they will spool off into the TempDB disk space.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions