isnt this an insult when the interviewer asks the difference between DELETE and TRUNCATE

if the interview is for senior dev ...not an entry or junior dev

@vivek7 Once the senior dev at my place deleted the whole database when the project was in production( client wanted new updates while in production phase,so instead of adding new db tables he just deleted everything). Thankfully, a backup was taken a couple of days before.

Not sure why anyone would be insulted by that. Sounds like a resonable question to me.

A lot of people don't really know the difference!

Sounds like a question with junior and senior level answers, no?

Someone now has to give us the textbook answer in case we get asked? :-)

#advanced

@vivek7 what is the correct answer?

@spl0 wht ans

@vivek7 the difference between the two?

u r insulting me buddy ...u havnt read d rant proprly

haha

@vivek7 dont be insulted man... just tell us the answer so we know what to say!
:)

@spl0 delete-dml,locks d row,filter what to delete,rollback possible

truncate- ddl,locks d table,no filter deletes fuckn everythng ,no rollback so u r fckd if u mistakenly truncate

spl0 r u ms sql dev ..seen related rants

@vivek7 thanks for the info!

i've used ms sql... but wouldnt say i like it. lock lock lock lock lock lock lock lock

@spl0 if you know sql try learning apache hive used by facebook for data analytics..its a part of hadoop environment.

u ll love learng it

@g-m-f thanks for d info...my ans ws in general for SQL

databases may have their own features

@g-m-f TRUNCATE being DDL also commits whatever went before it! On oracle anyway....

Talking of DDL... cant you rollback DDL on some databases?

@vivek7 SQL != SQL Server

@spl0 sql is a query language
m talkng abt tht ...MS sql is a db

u got me wrong

Can someone please translate @vivek7 for me?

@fyroc what exactly you want to knw

@fyroc vivek is a sanskrit word meaning wisdom or knowledge

n its my name

@fyroc if you're wanting to know what he meant by difference between delete and truncate I can give you the ANSI differences, which hold true for "most" SQL databases. I say most as any 1 provider (MariaDB, MySQL, db2, oracle, postgres, etc) may have so specific implementation of the language.

In any regards, a truncate is ddl, meaning "data definition language". Other ddl commands include "create", "drop", and "alter". Truncate removes ALL data from a db table. There is no filtering capability, there is no rollback there is no recovery. (some databases have "soft deletes" that let you recover deleted data easily, and most databases support transactions which allow multiple commands to all complete as one or do nothing). Delete, allows filtering, is DML (data manipulation language) - other DML statements are "insert" and "update". Delete can be part of a "transaction", and in databases that support soft deletes are easy to recover.

@fyroc additionally, since delete is DML, you can create triggers at the row level, or command level (trigger once per command, or once per row) that kick off before and/or after the delete to do additional logic. One example of this, would be to create a row level trigger on deletes, or inserts, that logs the data changed into an audit table. You couldn't do this with truncate as truncate doesn't have any row level processing. It drops all the data in the table.

@brettmoan tht was great

@brettmoan Oh no, I know the differences between the two. I was just being an add hole because of the terrible grammar.

You'd probably also want to talk about space reclamation and how each behaves with constraints such as foreign keys