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

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.