Results 1 to 6 of 6

Thread: Overview of SQL Truncate command

  1. #1
    Join Date
    May 2009
    Posts
    1,084

    Overview of SQL Truncate command

    Hi,
    Many times it happens when we need to remove all the data in a particular table. This happens mostly when a table is not needed. The way of doing is using the Drop Table. But what if the scenario is little different where I want to get rid of the data not the table. For this purpose I had found that Truncate Table command can be used for. What you think in this. How to use the same.

  2. #2
    Join Date
    May 2008
    Posts
    2,389

    Re: Overview of SQL Truncate command

    Truncate is a empty a table or set of tables. The synopsis used in this is TRUNCATE [TABLE] name [, ...] [CASCADE | RESTRICT]. The command TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but as it travels through the table, it is faster. In addition, she immediately recovers disk space, thus avoiding vacuum operation. This command is especially useful for large tables.

  3. #3
    Join Date
    Feb 2008
    Posts
    1,852

    Re: Overview of SQL Truncate command

    Here is how the settings of this works. First comes the name. It is a name of an empty table (can be qualified by the schema). Then Cascade. Empty all the tables that have foreign key references a table and called on every table added to the group because of Cascade. And then Restrict. Refuse dump if a table has foreign key references a table must not be emptied. This option is enabled by default.

  4. #4
    Join Date
    Jan 2008
    Posts
    1,521

    Re: Overview of SQL Truncate command

    You have to note down one thing. That only the owner of a table can be empty (TRUNCATE). TRUNCATE can not be used on a table referenced by other tables through foreign keys, unless the tables are also included in the command. Otherwise, the audit trail would require full table, which is not the purpose of the TRUNCATE command. The CASCADE option is used to automatically include all dependent tables - be careful when you use this option because you might lose data that you would want to keep.

  5. #5
    Join Date
    Apr 2008
    Posts
    1,948

    Re: Overview of SQL Truncate command

    These 2 operations are used to remove data from a table, but they have very different characteristics that make the choice of their use is not easy. The plus point of TRUNCATE TABLE is that it is very fast because there is no loading data into the cache, and logging in reduced, can be integrated in a transaction, release of space used by table and Release of space used by table.

  6. #6
    Join Date
    May 2008
    Posts
    2,012

    Re: Overview of SQL Truncate command

    There are some least points of the same command. You must keep an eye on the same also. It requires substantial rights .i.e. right to change the schema of the table ALTER TABLE. It can run on a table with foreign key same deactivated. It can also run on a table with foreign key same deactivated and cannot be filtered, WHERE clause not possible.

Similar Threads

  1. Replies: 2
    Last Post: 21-03-2012, 03:23 AM
  2. Difference between Delete and Truncate
    By Aileen_L in forum Software Development
    Replies: 3
    Last Post: 29-01-2010, 09:05 AM
  3. Replies: 3
    Last Post: 03-09-2009, 05:41 PM
  4. What is the difference between delete table & truncate table?
    By Swati in forum Software Development
    Replies: 4
    Last Post: 13-02-2009, 05:24 PM
  5. Replies: 1
    Last Post: 18-05-2007, 01:24 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,678,520.48553 seconds with 17 queries