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?

Have you already looked at adding a check constraint on that column which would restrict values? Something like:

CREATE TABLE SomeTable
(
   Id int NOT NULL,
   Frequency varchar(200),
   CONSTRAINT chk_Frequency CHECK (Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly'))
)

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.

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.

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.