How to create unique index for month and year column?

Question

Does anyone have any idea how do i create unique index for both month and year column?

As in: JAN 2012 should be an unique index and also to aid in searching and retreiving of results by month and year.

Thanks

Answer 1

Assuming you have a column of type DATE or DATETIME2 (or DATETIME), you can define two computed, persisted columns for year and month and put an unique index on those:

ALTER TABLE dbo.YourTable
ADD DateMonth AS MONTH(YourDateColumn) PERSISTED

ALTER TABLE dbo.YourTable
ADD DateYear AS YEAR(YourDateColumn) PERSISTED

CREATE UNIQUE INDEX UIX_YourTable_MonthYear ON dbo.YourTable(DateMonth, DateYear)

Answer 2

You probably shouldn't do it quite the way you're thinking. Values like 'Jan 2013', 'Feb 2013' are often involved in sorting, but those values won't sort correctly without extra CASE...WHEN logic in every SQL statement that uses them.

Personally, I find values like '2013-01' and '2013-02' avoid sorting problems, are consistent across multiple languages and cultures, and are readily accepted by users. In historical reports you're unlikely to need only all the Januaries, which would require parsing those values (not a good thing). The most I've ever needed was comparison to the same month a year ago, for which you don't need to do any parsing.

You can store all the valid year/month combinations for the next 100 years in a table of only 1200 rows, and you can validate new and existing data with simple foreign key references.

Finally, a unique index on that single column could be as simple as

create unique index on table-name (column-name);

For two columns,

create unique index on table-name (first-column-name, second-column-name);