Best way to remove all data from a table?

SQL Server Administration
1

Evening all,

This might be a really simple one but I've never had to do it before. I need to delete all data from a couple of tables in my test database before I import more data. Ideally I would like to have the primary keys recreated from the beginning again, am I best to use 'Truncate'? or is there a better way to achieve this?

Many thanks

Dave

2

TRUNCATE would be the fastest. It is minimally logged, can use "deferred drop" etc. However, there are some restrictions - if you have foreign keys you will have to drop the foreign keys and recreate them. If the table is used in a persisted view, you cannot use truncate etc.

When you truncate the identity seed on the table gets reset.

3

If you cannot use TRUNCATE (because you have Foreign keys and don't want to drop them etc. etc. so choose to use DELETE instead) you can reset the Identity using DBCC

DBCC CHECKIDENT ('YourTableName', RESEED, 12345)
4

Thanks guys, I'll give it ago in the morning.

@Kristen - What would be classed as foreign keys?

Thanks

Dave

5

You would have to formally define a Foreign Key on the table.

If you have an Orders table (CustomerID, OrderDate, etc), which has the Primary Key column OrderID, and then you have an OrderItems table (ProductCode, Qty, Price, etc.) which has the Primary Key OrderID & ItemNo then you can declare a Foreign Key relationship between the two tables the column(s) that are common to both tables - i.e. Order.OrderID and OrderItems.OrderID

You don't have to formally declare the Foreign Key (in which case you could still use TRUNCATE) but if you do declare it SQL will ensure that you never delete a row from Orders that still has one/many rows in OrderItems.

1 Like
6

Awesome, it worked perfectly thanks guys!! OMG 'Truncate' is fast...

@Kristen
Thanks for the great explanation, yes I use foreign keys (didn't know they were called that) but they are not formally declared so no problems there. Tho I suppose it wouldn't really matter as I was clearing all the associated tables...

I learnt 2 new thanks today LOL :grinning:

Thanks again

Dave

7

By-the-way, TRUNCATE does use the transaction log, so if you hear that it does not, it is incorrect. I cannot describe the process, but just thought I would let you know.

8

Yes, TRUNCATE uses the log but minimally. It does NOT log every row that is deleted. You have to use DELETE for that.

9

It would be better that they were ... there are a number of benefits, but even if it saves you from yourself by preventing an accidental / Program Bug deletion of a Parent Record that has Child Records it prevents orphaned data which is just junk in a database.

If the table has Foreign keys defined then you can't use Truncate - even if child tables are empty, so you'd have to work around that (either use DELETE or Drop/Re-CREATE the Foreign Keys, or use a Schema-swap etc etc)

10

I know it's semantics and I apologize for that but, to be totally correct, TRUNCATE TABLE is actually "FULLY LOGGED" in that it logs all of the pages that it de-allocated. It is not considered to be minimally logged and, yes (just in case someone asks), if used inside a transaction, it can be rolled back. If you look up "Minimally Logged Operations" in Books Online, you won't find TRUNCATE TABLE there.

If you have a database in the Bulk Logged mode, using Truncate won't bother the logfile chain like a minimally logged or bulk logged operation because it's neither and that the real reason to understand that it's not actually minimally logged.

If a logfile backup is taken and the database is in the Bulk Logged mode and no minimally logged operations took place, you can restore to a point in time (PIT) in that log file. If all that is true but a minimally logged operation took place, then you can either restore up to and not including that log file or you can use the whole log file as part of a PIT restore and it can be followed by other log file restores, as well.

11

Sorry Jeff that does not fit my definition of fully logged. TRUNCATE TABLE does not log each row being deleted. That is how I define a fully-logged delete operation.

True, the end result may be the same for point in time recovery, but the difference, though subtle, is substantial in terms of performance.

12

I think the confusion is because Microsoft uses the term "Minimally Logged" to refer to a specific type of operation that is done differently in bulk-logged recovery model when compared to full recovery model.

13

Yes, you are quite correct. Not sure the same confusion exists for "Fully Logged" though.