SQL titbit: adding a key to a table


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


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


Regards,
Jen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s