DROP and Truncate

It’s only a matter of time before you need to clean up after yourself in Microsoft SQL. You’ll have to get rid of an index, or a table, or maybe even a whole database. Indexes, tables, and databases can easily be removed using the DROP statement.

The DROP INDEX Statement

If you get into trying to make your queries more effecient, you’ll definitely need to learn how to get rid of an index, so you can try new indexes. To do this, use the DROP INDEX command.

DROP INDEX indexName ON tableName

The DROP TABLE Statement

If you make a regular habit of creating tableName_tmp tables, then you’ll need to know how to clean them up too! Use the DROP TABLE command.

DROP TABLE tableName

The DROP DATABASE Statement

The DROP DATABASE statement is used to delete a database. Be careful, you’ll want to be in the master database before running this command. Also, please make sure you have a backup… I don’t want a bunch of nasty emails from your DBA because you dropped a database you shouldn’t have. Honestly, you should only do this if you are a DBA…What am I saying is your DBA should have locked you out of this ability…If you were able to do it, and you shouldn’t have… Refer your DBA to my rules.

DROP DATABASE databaseName

The TRUNCATE TABLE Statement

What if we only want to delete the data inside the table, and not the table itself?  Then use the TRUNCATE TABLE command.  It basically resets your table to the exact state it was in when it was created!

TRUNCATE TABLE tableName

If you have any questions, send them in!  I’ll do my best to answer them as quickly as possible!

Tags: ,

2 Responses to “DROP and Truncate”

  1. Your Reader 20070401 at 00:00 #

    Good work! Thank you!
    I always wanted to write in my blog something like that. Can I take part of your post to my site?
    Of course, I will add backlink?

    Regards, Reader

Trackbacks/Pingbacks

  1. Studying for the 70-457 | Shannon Lowder - 20130212

    [...] objective may include but is not limited to: create tables without using the built-in tools; ALTER; DROP; ALTER COLUMN; [...]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.