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

[@singuyen5927]

Thank you for the schema binding

[@BeardedDevData]

No problem 👍.

[@johnbreslin4521]

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

[@BeardedDevData]

Hi Josh, appreciate the support, you can find the video here: ua-cam.com/video/GoFsAFDz8JI/v-deo.html

[@hasanmougharbel8030]

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

[@BeardedDevData]

You can actually run both in sql server management studio, however when connecting to a database you write queries using SQL, when connecting to an analysis services model you write queries using MDX or DAX. Both are different data structures even in analysis services, multidimensional and tabular models are very different.

[@houstonvanhoy7767]

@@BeardedDevData Do you have videos on SSAS?

[@houstonvanhoy7767]

@@BeardedDevData Do you have any videos on database design?
Likewise, do you recommend any database design tool?