Which command will using to delete all rows from table truncate or delete ?

  • I work on sql server 2017

    i need to delete all rows from table student_course

    but i don't know use

    delete from student_course

    or

    truncate table student_course

    table student_course

    studentid pk

    courseid pk

    table student table

    studentid pk

    table courses

    courseid pk

    ---------------------------------------------

    student_course table have relation with courses table and student table

    so which command will be used to delete all rows from table student_course

    truncate or delete ?

    What I have tried:

    delete from student_course

    or

    truncate table student_course
  • Both will work, but they are different.

    https://www.sqlshack.com/difference-between-sql-truncate-and-sql-delete-statements-in-sql-server/

    You've been here 10 years. Easy to search for the answer, if not already known for some reason.

     

    • This reply was modified 2 months ago by  homebrew01.
    • This reply was modified 2 months ago by  homebrew01.
  • The answer is probably truncate, but both will work.

    You can not truncate a table that is referenced by a foreign key constraint, but it seems likely the student_course has relationships that reference the student and course tables, not the other way around. You would not be able to truncate the student or course table, even if the student_course table is empty.

     

  • Since TRUNCATE is more efficient, try it first.

    If it doesn't work, then use DELETE.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • There are actually several massive differences between Truncate and Delete.  You can read about most of them simply by looking up the commands and reading about them.

    One not so well documented difference is that if you have a table that has data in it and it has a Clustered Index on it and you delete all rows, the table will not allow Minimal Logging even when you're setup for it.  If you have the same table and you Truncate it, then you can use Minimal Logging with the Clustered Index provided, of course, that the other criteria for Minimal Logging are met.

    There's another "Oolie" to be had and I've documented it at the following URL.  It has proven extremely valuable to me in the world of ETL where you must first insert data into a table to evaluate it and then update the data with what amount to "Expansive" updates.  It isn't documented in any of the MS documentation (in fact, the MS documentation says that it can never happen) that I've ever seen and most people don't even know of it.  It will work on a Truncated table with a Clustered Index but will not work after a full table DELETE.

    https://www.sqlservercentral.com/articles/some-t-sql-inserts-do-follow-the-fill-factor-sql-oolie

    --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

  • Also keep in mind you cannot truncate a table that is parent table for other tables. (FK relationships)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Johan Bijnens wrote:

    Also keep in mind you cannot truncate a table that is parent table for other tables. (FK relationships)

    Though you can disable the foreign keys, then truncate, then enable the foreign keys again.

Viewing 7 posts - 1 through 6 (of 6 total)

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