[sql] Restrict varchar() column to specific values?

Is there a way to specify, for example 4 distinct values for a varchar column in MS SQL Server 2008?

For example, I need a column called Frequency (varchar) that only accepts 'Daily', 'Weekly', 'Monthly', 'Yearly' as possible values

Is this possible to set within the SQL Server Management Studio when creating the table?

You want a check constraint.

CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.

You want something like:

ALTER TABLE dbo.Table ADD CONSTRAINT CK_Table_Frequency
    CHECK (Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly'))

You can also implement check constraints with scalar functions, as described in the link above, which is how I prefer to do it.

When you are editing a table
Right Click -> Check Constraints -> Add -> Type something like Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly') in expression field and a good constraint name in (Name) field.
You are done.

Personally, I'd code it as tinyint and:

• Either: change it to text on the client, check constraint between 1 and 4
• Or: use a lookup table with a foreign key

Reasons:

• It will take on average 8 bytes to store text, 1 byte for tinyint. Over millions of rows, this will make a difference.

• What about collation? Is "Daily" the same as "DAILY"? It takes resources to do this kind of comparison.

• Finally, what if you want to add "Biweekly" or "Hourly"? This requires a schema change when you could just add new rows to a lookup table.