SQL Truncate vs. Delete

By now as you know, in this post I am going to discuss about two SQL commands we use regularly to clear data in our tables.

Usage

1. DELETE FROM TABLENAME
3. TRUNCATE TABLE TABLENAME

Even though they do a similar thing, there are some differences which makes them unique commands.

	DELETE	TRUNCATE
1.	Considered as Data Mining Language (DML) statement.	Considered as Data Definition Language (DDL) statement.
2.	Can be used to delete all or part of data in a table.	Will delete all the data in a table.
3.	Will log the actions.	There will be no logging on record deletion.
4.	Will use more locks.	Will use fewer locks.
5.	Will use more resources.	Will use less resources.
6.	Slow.	Fast.
7.	Will not reset the identity seed.	Will reset the identity seed to 0.
8.	Can be used to delete data in tables having relationships.	Cannot use in tables having relationships.
9.	Can be used in tables involved in log shipping or replication.	Cannot be used in tables involved in log shipping or replication.
10.	Transaction can be rolled back.	No rollback.
11.	Table may keep the empty pages. Can be released by running, SHRINKDATABASE (Database Name).	Data pages related to the table will be de allocated and returned to the system.
12.	Related Triggers are fired.	Triggers are not fired.

 

If you used DELETE to fully remove all the records, you can reset the identity value by running the following command.

1. DBCC CHECKIDENT (TABLENAME, RESEED, 0)

If you do have some rows left in table, simply replace 0 with the last identity column value. For example if you put 5 instead of 0 then the next record inserted into the table will have an identity column value of 6.

If you jus need to check the current identity value just use the following command.

1. DBCC CHECKIDENT (TABLENAME, NORESEED)

Beerenberg Strawberry Farm – Hahndorf South Australia

If you like Strawberries and would like to pick your own, then there is a good place you can do it in Hahndorf South Australia.

It is called Beerenberg Strawberry farm, located on the Mount Barker road. If you are planning to visit them to pick your own strawberries you need to visit them from October to May which is the Strawberry season. Else you can buy their produce from the shop.

Normally they are open everyday except Christmas day from 9:00AM to 04:00PM. Anyway it is always good idea to give them a call before going.

There is enough parking on site and there will be a helper on busy days.

Once you are there you can visit the shop to get permission to enter the patch. When I was there entry was $ 3 per person. They will also provide containers to bring the Strawberries you pick. Remember to pick whatever amount of Strawberries you would like to buy. Normally you can keep them from few days to about 10 days in fridge (obviously you can keep bit more days than this but then they wont be tasting fresh). Once you have enough Strawberries picked you can bring them back to the shop where they will weigh it, so you can pay. While I was there they sold a Kilo of Strawberry for $ 9, but the price always changes from season to season, so it is always best to check the price they are selling before heading to the Strawberry patch.

If you are travelling with young children, I do not recommend a pram since the ride will be bumpy, best is to use a child carrier sling.

Address: Beerenberg Strawberry Farm,
Mount Barker Road Hahndorf,
South Australia, 5245
Phone: +61 8 8388 7272
Fax: +61 8 8388 1108
Email: admin@beerenberg.com.au
Web: http://www.beerenberg.com.au/

Beerenberg Strawberry Farm VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map