Re: Create Table Permission for Domain Users Group

From: BP Margolin (bpmargo@attglobal.net)
Date: 07/05/02 

From: "BP Margolin" <bpmargo@attglobal.net>
Date: Thu, 4 Jul 2002 19:28:16 -0400

Rob,

> Are there any upsides to temp tables from a performance point of view
> over permanent tables?

Actually, Yes.

Temp tables are written to the tempdb database. Because the tempdb database
is **always** recreated from scratch when SQL Server is restarted, SQL
Server has to do less I/O to the tempdb transaction log. Frankly, not a huge
difference, but a performance enhancement nevertheless :-)

Perhaps more importantly, if the server crashes, might you face problems in
your code if your "temporary" permanent tables still exist. You might be
handling this situation, by testing that the "temporary" permanent tables do
not exist before creating them, but that's extra processing that should not
be necessary with real temp tables ... and that could actually have a
measurable impact on performance.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Rob Taylor" <rob@webadviser.info> wrote in message
news:er#gqw4ICHA.2228@tkmsftngp08...
> Luis
>
> I didn't use temp Tables (#/@tablename) but created "full tables" and
> then dropped them.
>
> Are there any upsides to temp tables from a performance point of view
> over permanent tables?
>
> Also, is it possible to get around the permissions issue I raised where
> new permanent tables are created - in case I need to use this route in
> the future?
>
> Many thanks
>
> Rob
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: OT - How temp agencies work
    ... I used temp. ... Sometimes a permanent job can come out of it, ... agency I work for. ... The bulk of our business came from positions like ...
    (rec.crafts.textiles.quilting)
  • Re: Way OT Query...
    ... to reign in the use of temp services. ... workforce *instead of* a group of full time workers. ... employee (and also risk the extra expenses of having to terminate someone ... if the choice was permanent position or defer the work uhntil a later time, ...
    (rec.arts.sf.tv.babylon5.moderated)
  • ADO And SQL server 200O
    ... to check the data for completeness before posting it to the permanent ... the user saves I check it for completeness before doing an update to the ... recordin the temp table, if another user has a record in the temp ... Can I adjust my ADO components to not cause a lock on ...
    (borland.public.delphi.database.ado)
  • Re: Avoid Creating Auto Number Field
    ... > Don't use a temp table. ... > validate the entered data and then add a new record to your table using ... A temp table which matches the layout of the permanent table is ... >> having the auto number field designated as an auto number field in the ...
    (microsoft.public.access.formscoding)
  • Re: Create Table Permission for Domain Users Group
    ... Luis ... Are there any upsides to temp tables from a performance point of view ... over permanent tables? ...
    (microsoft.public.sqlserver.security)