SQL DROP Table, DROP Index, and DROP Database Statements Explained with Examples

The SQL DROP statement is a command that deletes existing database components or the entire databases with all their data permanently. There are other SQL commands with a similar effect (TRUNCATE or DELETE), but the specificity of the DROP command is that it deletes everything at once. For instance, DROP TABLE removes the table data, indexes, triggers, permissions, constraints – the entire schema of the table.

The DROP statement requires one to be extremely careful when using it. Once performed, it can’t be rolled back. The information is lost for good. The only chance to return the data will be to restore a backup. Still, in many cases, we have to apply DROP statements. This article will focus on such cases, the specific DROP variants, and how to use them safely.

CodingSight - SQL DROP Index, DROP Table, and DROP Database Statements Explained with Examples

Preparations

To examine our cases, we’ll need a database with the data. I have created a test database named EltechEmployees with the following query:

USE [master] 
go 
CREATE DATABASE [EltechEmployees] 
go

In that database, I have created a table tblEmployees – the following query serves for that:

USE [EltechEmployees] 
go 
CREATE TABLE [tblEmployees] 
  ( 
     [businessentityid] [INT] NOT NULL, 
     [nationalidnumber] [NVARCHAR](15) NOT NULL, 
     [loginid]          [NVARCHAR](256) NOT NULL, 
     [jobtitle]         [NVARCHAR](50) NOT NULL, 
     [birthdate]        [DATE] NOT NULL, 
     [maritalstatus]    [NCHAR](1) NOT NULL, 
     [gender]           [NCHAR](1) NOT NULL, 
     [hiredate]         [DATE] NOT NULL, 
     [vacationhours]    [SMALLINT] NOT NULL, 
     [sickleavehours]   [SMALLINT] NOT NULL, 
     [modifieddate]     [DATETIME] NOT NULL 
     
  ) 
go 

The next step is creating two non-clustered indexes and one clustered index on the tblEmployees table.

  • Non-clustered indexes: IDX_tblEmployees_loginID and IDX_tblEmployees_nationalidnumber
  • One clustered index: IDX _tblEmployees_gender

To create these indexes, use the following query:

USE [EltechEmployees] 
go 
CREATE INDEX [IDX_tblEmployees_loginID]   ON [tblEmployees](loginid) 
go 
CREATE INDEX [IDX_tblEmployees_nationalidnumber]  ON [tblEmployees](nationalidnumber) 
go 
CREATE Clustered INDEX [IDX_tblEmployees_gender]  ON [tblEmployees](gender) 
go

Let’s view the indexes we created:

SELECT Object_name([Index].object_id)                               [Table Name] 
       , 
       [Index].NAME 
       [Index Name] 
       , 
       Col_name([Index Column].object_id, [Index Column].column_id) 
       [Index Column_Name], 
       [Index Column].index_column_id, 
       CASE 
         WHEN is_primary_key = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Primary Key], 
       CASE 
         WHEN is_unique = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Unique Key] 
FROM   sys.indexes AS [Index] 
       INNER JOIN sys.index_columns AS [Index Column] 
               ON [Index].object_id = [Index Column].object_id 
                  AND [Index].index_id = [Index Column].index_id 
WHERE  [Index].is_hypothetical = 0 
       AND [Index].object_id = Object_id('tblEmployees');

Output

Outpup of creation of two non-clustered indexes and one clustered index on the tblEmployees table.

Now we can proceed to the practical examples.

Drop Index SQL Statement

Our first case is dropping indexes from a table.

Example 1: Drop One or Multiple Indexes from the Table

The syntax to drop multiple indexes is following:

Drop index 
[IndexName] on [SchemaName].[TableName] ,
[IndexName] on [SchemaName].[TableName] 
  • IndexName: Specify the index name you want to drop after the Drop Index statement.
  • [SchemaName].[TableName]: Specify the database name, schema name, and table name. The Drop table statement allows you to use the two-part name of any database object. The values of [SchemaName].[TableName] must be specified after the ON keyword.

Suppose we want to drop IX_loginID_tblEmployees and IDX_nationalidnumber_tblEmployees on the tblEmployees table. To do that, run the below query:

DROP INDEX 
[IDX_tblEmployees_loginID] ON [dbo].[tblEmployees], 
[IDX_tblEmployees_nationalidnumber] ON [dbo].[tblEmployees] 

Once indexes are dropped, run the following query to view them:

SELECT Object_name([Index].object_id)                               [Table Name] 
       , 
       [Index].NAME 
       [Index Name] 
       , 
       Col_name([Index Column].object_id, [Index Column].column_id) 
       [Index Column_Name], 
       [Index Column].index_column_id, 
       CASE 
         WHEN is_primary_key = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Primary Key], 
       CASE 
         WHEN is_unique = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Unique Key] 
FROM   sys.indexes AS [Index] 
       INNER JOIN sys.index_columns AS [Index Column] 
               ON [Index].object_id = [Index Column].object_id 
                  AND [Index].index_id = [Index Column].index_id 
WHERE  [Index].is_hypothetical = 0 
       AND [Index].object_id = Object_id('tblEmployees'); 

Output

Drop one or multiple indexes from the table

Example 2: Drop Index with MAXDOP and ONLINE Option.

We can use MAXDOP and ONLINE options while dropping the index.

Note: We can drop only clustered index using MAXDOP and ONLINE options.

The syntax is as follows:

DROP Index [indexName] on [schemaname].[tablename] with (MAXDOP = @maxdop_val, ONLINE = @online_option)
  • Indexname: Specify the index name you want to drop.
  • [SchemaName].[TableName]:  Specify the three-part name of the table.
  • @MaxDop_val: Specify the value of the MAXDOP parameter.
  • @online_option: The valid values are ON and OFF.

Suppose we want to drop the PK_Employee_BusinessEntityID  index with MAXDOP and ONLINE options.

Drop the index using the value of the MAXDOP option to 5, and the ONLINE option is ON. The query to drop the index is following:

DROP INDEX [IDX_tblEmployees_gender] ON [dbo].[tblEmployees] WITH (maxdop=5, 
online=ON)

All indexes have been dropped from the tblEmployees table.

SQL Drop Table Statement

The DROP TABLE SQL statement drops the table from any database. The syntax of the DROP TABLE is following:

DROP TABLE [DatabaseName].[SchemaName].[TableName]

[DatabaseName].[SchemaName].[TableName]: Specify the table name. You can use the three-part name of the table.

Permissions

The user must have the ALTER permission on the schema where the table has been created and the Control permission on the table, or be a member of the db_ddladmin fixed role.

Note:

  1. If you drop a table referenced by a foreign key, you must drop that referencing table first.
  2. When you drop a table with a column having the FILESTREAM attribute, the data stored in the file system will not be dropped.

Example 1: Drop Physical Table

In the following example, we are dropping the table named tblEmployees from the EltechEmployees database:

Drop table [EltechEmployees].[dbo].[tblEmployees]

Example 2: Drop Temp Table SQL

Here, we are dropping a temporary table. I have created a temp table named #tblEmployee by executing the following code:

CREATE TABLE [#tblemployees] 
  ( 
     [businessentityid] [INT] NOT NULL, 
     [nationalidnumber] [NVARCHAR](15) NOT NULL, 
     [loginid]          [NVARCHAR](256) NOT NULL, 
     [jobtitle]         [NVARCHAR](50) NOT NULL, 
     [birthdate]        [DATE] NOT NULL, 
     [maritalstatus]    [NCHAR](1) NOT NULL, 
     [gender]           [NCHAR](1) NOT NULL, 
     [hiredate]         [DATE] NOT NULL, 
     [vacationhours]    [SMALLINT] NOT NULL, 
     [sickleavehours]   [SMALLINT] NOT NULL, 
     [modifieddate]     [DATETIME] NOT NULL 
  ) 
go

Before dropping the table, we check the existence of the temp table. If the table is found, it will be dropped.

IF Object_id(N'tempdb..#tblEmployees', N'U') IS NOT NULL 
  DROP TABLE #tblemployees; 
go 

Thus, the temporary table #tblEmployees will be dropped.

Drop Database Statement

Drop database statement works to drop the entire database. The syntax is following:

Drop database [DatabaseName]

[DatabaseName]: Specify the name of the database name you want to drop.

Permissions

To drop the database, you need the CONTROL or ALTER ANY DATABASE permission on the database. Or, you must be a member of the db_owner fixed database role.

Notes:

  1. When we drop any database in the ONLINE state, the SQL Server will drop the database files from the disk. However, if you are dropping the database in the OFFLINE state, the SQL Server does not drop the database files. We must delete them manually.
  2. To drop the database published for transactional or published/subscribed to the merge replication, we must destroy the replication and then drop the database.
  3. If users are connected to the database, we cannot drop the database. First, we must change its state to SINGLE_USER.

Example: Drop Database which is Being Used

We want to drop the EltechEmployees database. To do that, run the following query:

USE master 
go 
DROP DATABASE [EltechEmployees] 

If users are connected to the database, you will encounter the following error:

Msg 3702, Level 16, State 4, Line 3
Cannot drop database "EltechEmployees" because it is currently in use.

To rectify the error, we must execute the following query:

USE [master] 
go 
ALTER DATABASE eltechemployees SET single_user WITH ROLLBACK immediate 

The above query sets the database in the SINGLE_USER mode and drops all users connected to the database.

Now, let us run the DROP DATABASE statement:

USE master 
go 
DROP DATABASE [EltechEmployees] 

Output

run the DROP DATABASE statemen

As you can see, the command was executed successfully.

Summary

Thus, we’ve examined the DROP INDEX, DROP TABLE, and DROP DATABASE statements specific variants of the SQL DROP statement. Hope that the practical examples presented in this article helped explain how and when to apply these commands.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.

Leave a Reply

Your email address will not be published. Required fields are marked *