Something you don’t know about DROP TABLE

DROP TABLE has now have new syntax in SQL SERVER 2016. A separate query is not required  to check existence of a table before dropping it.  New syntax will allow you to check existence of table before dropping it.

New Syntax:

DROP TABLE IF EXISTS <TABLE NAME>

You can drop more than one table in a single drop table statement.

--Example 1 
CREATE TABLE TABLE1 (col int)
GO
CREATE TABLE TABLE2 (col int)
GO
DROP TABLE TABLE1, TABLE2

If you are dropping both table which holds primary key and table which hold foreign key in one drop table statement then the referencing table must be listed first and It will throw error if you try to drop primary key holding table first. At the same time it will only drop TABLE2 only.

--Example 2

CREATE TABLE TABLE1 (col int primary key)
GO
CREATE TABLE TABLE2 (col int constraint fk_col foreign key references TABLE1(col) )
GO
DROP TABLE TABLE1, TABLE2

drop table

If the table is large that uses more than 128 extent and you are dropping it then SQL server will drop them in two phases logical phase and physical phase.In first phase the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the second phase that is physical phase, the IAM pages marked for deallocation are physically dropped in batches.

Dropping a table that contains a VARBINARY(MAX) column with the FILESTREAM attribute then any data stored in the file system will not be removed.

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

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