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.


  • 6
    Have you looked at the CREATE INDEX topic in BOL? That explains the syntax in great depth and I'm not sure there is any benefit of repeating it here. Jan 4, 2013 at 12:13

2 Answers 2


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)

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.