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 */

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);

[johnbreslin]

Really good demonstration - will have to check your video on Schema Binding

[hasanmougharbel]

Hey there, here we meet again
I was playing around sql managment studio and perhaps i have a single enquiry.
SQL server scripts and analysis services scripts... In what way does those two scripts differ?
Thanks