View Poll Results: which one is smarter to do

Voters
2. You may not vote on this poll
  • delete * from table

    2 100.00%
  • drop table then create table

    0 0%
  • Any other? (i cant see any other option but why not add)

    0 0%
Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    18

    Unanswered: delete or drop table then create table

    which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DELETE is a logged transaction, and so incurs overhead that DROP and RECREATE do not.
    For another option, try TRUNCATE table, which is also unlogged.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2006
    Posts
    18
    well said i did research truncate table and you are of course correct on this. I'll change the code to that

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    delete *


    You crack me up
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you show us what you're doing, what volume, ect

    Where for example are you getting the data from...and if you got into an argument, what did your coworker say?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Personally I would not allow the drop/create table here, as it carries too many permission implications. Take the matter of the logs up with the DBA involved, and see if the transaction logs will hold up. He may need to add more diskspace, more backups, or both.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    delete *
    You crack me up
    That's MS Access syntax...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2006
    Posts
    18
    Heres the code i made

    if 0< (select count(*) from dbo.sysobjects where id = object_id(N'[dbo].[_Bootfiles_Summary_Work_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    TRUNCATE TABLE _Bootfiles_Summary_Work_Table
    end
    else
    begin
    Create Table _Bootfiles_Summary_Work_Table
    (Agency smallint,
    Plate_State varchar(20),
    Plate varchar(8),
    Cite_Count int,
    CiteBalance Money)
    end

    its really simple actually no index no nothing, and in case it gets deleted some code that i dont know this is will recreate the table.

    and o yea no star on the delete my mistake.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •