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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
April 27, 2022 at 5:42 pm

#4023209
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
```

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

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

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

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 1 year, 11 months ago by homebrew01.
This reply was modified 1 year, 11 months ago by homebrew01.

Ed B Hall of Fame Points: 3615 More actions · Answer 2

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.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

Since TRUNCATE is more efficient, try it first.

If it doesn't work, then use DELETE.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

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)

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 5

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

Jonathan AC Roberts SSCoach Points: 18256 More actions · Answer 6

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.