Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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. –  Martin Smith Jan 4 '13 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)
share|improve this answer

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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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