Adding a new column is something that business intelligence consumers want to do; in fact, I don’t often hear of people wanting to remove a column. I came across a table with no keys on it recently, and was asked about it, so here’s a quick note on the topic. In SQL, it is very easy to do that with a simple statement. So here goes:
ALTER TABLE dbo.DimTime
Add YYYYMMDD INT NOT NULL
DEFAULT 1 WITH VALUES ;
If your table doesn’t have a primary key, and you want to make your new column to serve as one, here is the syntax here. Fill factor etc is up to you.
ALTER TABLE dbo.DimTime WITH NOCHECK
ADD CONSTRAINTPK_DimTime PRIMARY KEYCLUSTERED (YYYYMMDD)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)