SQL Tutorial - How to drop a column from a table

Learn how to drop columns from a table in this video tutorial and also why you might come across some errors. In this SQL Tutorial I cover: How to drop columns from a table How to drop multiple columns from a table What impact does dropping columns have on Views that include those columns How to drop computed columns and what happens if you try to drop a column that is part of the computation What happens if you try to drop a column that is part of an index What happens if you try to drop a column that is part of a foreign key or primary key Chapters 00:00 Introduction 00:55 Warning about dropping columns 02:30 DROP Column Syntax 03:30 DROP multiple columns 04:40 DROP columns used in VIEWs 08:30 DROP COMPUTED COLUMNs 09:45 DROP Index Columns 11:00 DROP FOREIGN KEY Columns 12:20 DROP PRIMARY KEY Columns To follow along with the video, the below scripts are available: /* create tables */ IF OBJECT_ID(N'dbo.OrderDetails', N'U') IS NOT NULL DROP TABLE dbo.OrderDetails IF OBJECT_ID(N'dbo.Orders', N'U') IS NOT NULL DROP TABLE dbo.Orders GO CREATE TABLE dbo.Orders ( OrderId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Orders_OrderId PRIMARY KEY (OrderId), OrderDate DATE NOT NULL, EmployeeId INT NOT NULL ); INSERT INTO dbo.Orders (OrderDate, EmployeeId) VALUES ('20220101', 1), ('20220102', 2), ('20220103', 3) CREATE TABLE dbo.OrderDetails ( OrderDetailsId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_OrderDetails_OrderDetailsId PRIMARY KEY (OrderDetailsId), OrderId INT NOT NULL CONSTRAINT FK_Orders_OrderDetails_OrderId FOREIGN KEY (OrderId) REFERENCES dbo.Orders (OrderId), ProductCategory VARCHAR(50) NOT NULL, ProductId INT NOT NULL, Quantity INT NOT NULL, Price DECIMAL(6, 2) NOT NULL, TotalExcludingVAT AS Quantity * Price ); INSERT INTO dbo.OrderDetails (OrderId, ProductCategory, ProductId, Quantity, Price) VALUES (1, 'Electronics', 5, 4, 3.99), (1, 'Clothing', 16, 2, 7.99), (2, 'Electronics', 5, 2, 3.99), (2, 'Hardware', 28, 1, 18.99), (2, 'Electronics', 3, 3, 6.99), (2, 'Electronics', 8, 6, 4.99), (3, 'Clothing', 18, 19, 1.99), (3, 'Electronics', 5, 1, 3.99), (3, 'Electronics', 6, 10, 6.99), (3, 'Hardware', 23, 50, 0.99); /* DROP column */ ALTER TABLE dbo.OrderDetails DROP COLUMN ProductCategory; /* create view */ CREATE VIEW dbo.vwOrders AS SELECT Ord.OrderId, Ord.OrderDate, Ord.EmployeeId, OrdDet.OrderDetailsId, OrdDet.ProductCategory, OrdDet.ProductId, OrdDet.Quantity, OrdDet.Price, OrdDet.TotalExcludingVAT FROM dbo.Orders AS Ord INNER JOIN dbo.OrderDetails AS OrdDet ON Ord.OrderId = OrdDet.OrderId; /* create index */ CREATE NONCLUSTERED INDEX NC_IX_OrderDetails_ProductCategory ON dbo.OrderDetails (ProductCategory);