Results 1 to 12 of 12

Thread: Flush Tables

  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Flush Tables

    Quick question. Is it possible to Flush the tables in SQL Server, I know you can do it in MySQL, but not sure on SQL Server?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pull the lever?

    You need to let us know what flush means, but I believe you want

    TRUNCATE TABLE tablename
    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.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Well I'll give that a go then. But when I used to use MySQL if I deleted something with an auto increment or something unique, then the next time I added a record it would add a record but with +1 increment, so in theory there would be a record missing in the middle, if you get me. Example:

    I have 3 records, and I delete record 3 and then add a new record it would be listed as 4 rather than 3.

    But I could do FLUSH TABLE (tablename) in MySQL to get round that problem, and deleted all the stored information.
    Last edited by KevCB226; 10-12-05 at 11:44.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(10), Col3 datetime DEFAULT(GetDAte()))
    GO
    
    INSERT INTO myTable99(Col2) SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
    GO
    
    SELECT * FROM myTable99
    GO
    
    TRUNCATE TABLE myTable99
    GO
    
    INSERT INTO myTable99(Col2) SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
    GO
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DELETE does not reset an identity counter to 0. TRUNCATE does.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by blindman
    DELETE does not reset an identity counter to 0. TRUNCATE does.
    One more thing. Delete is logged, and truncate is not. If you have a big take, delete can take a while since it has to log every row deleted.

    I lied. Two more things. You can't truncate a table if it has a column with a foreign key relationship defined.

    Bill

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by cascred
    One more thing. Delete is logged, and truncate is not.

    ...one more thing...everything is logged....TRUNCATE is just minimally logged....at the page level...
    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.

  8. #8
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by Brett Kaiser
    ...one more thing...everything is logged....TRUNCATE is just minimally logged....at the page level...
    True. But a truncate operation can't be rolled back. I assume the only reason the data pages are logged is in case you need to restore a transaction log... or am I missing another reason?

    Bill

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where'dya dig up that fantasy?

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
    GO
    
    INSERT INTO myTable99(Col2) SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
    GO
    
    SELECT * FROM myTable99
    GO
    
    BEGIN TRAN
    
    TRUNCATE TABLE myTable99
    
    ROLLBACK TRAN
    GO
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He got it from SQL Server 7.0 and 6.5, when truncates were completely unlogged and therefore verbotten in any sort of production process. You had to backup the database after any truncates to ensure recoverability.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    mea culpa....6.5...shudder....
    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.

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Couldnt find the link to the thread .. huh ... Brett

    http://www.dbforums.com/t981228.html
    Get yourself a copy of the The Holy Book

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

Posting Permissions

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