Sql-server – How to create unique index for month and year column

sql-server-2008

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

Best Answer

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)
Related Question